Spreadsheet help - Same formula, referring to different sheet with same name...

[rbrian]

I'm trying to create a spreadsheet that will reorder a lot of data, from a different source sheet every day. I have created templates in Numbers 2009 and Excel 2011, but both suffer from the same unhelpful helpfulness. Either that, or I'm going about it in completely the wrong way.

The original worksheets are in the same stupid, cluttered format, with the same name "Jumpering Schedule". I have created some worksheets which mostly use =OFFSET to cherry pick the relevant data from "Jumpering Schedule" and place it in "Prejumpering" and "Changeovers".

"Jumpering Schedule" is the only sheet in a new workbook, downloaded from the intranet every day. I copy the sheet, and paste it into the workbook template with "Prejumpering" and "Changeovers" sheets.

Now every formula in "Prejumpering" and "Changeovers" which refers to "Jumpering Schedule" has a !REF error - I assume because the original version of "Jumpering Schedule" is no longer there. I can correct each formula in turn, but it's very time consuming.

How can I make it refer to whatever sheet has that name, rather than the specific sheet it really wants to use?

I'm trying to do it without using macros, because I've had problems before transferring them between my Mac and my work Vista laptop, and I don't know enough about them to understand the error message.

 

[exegete77]

Howdy. Any time you reference a workbook that is closed, you will run into that problem. In order to go another direction, it would require much more information about what you want to accomplish. It might be good to visit one of the premier Excel help sites:

Mr. Excel

VBA Express

 

[rbrian]

Thanks for the links, I'll wade through them when I have time. In the meantime, you asked for more information- it really isn't complicated.

Essentially, the source sheet is 12 columns wide and 8 rows long - out of those 96 cells, I need 7.

So, in 'Prejumpering' A1 I use "=OFFSET 'Jumpering Schedule' C3,0,0"
In 'Prejumpering B1 I use "=OFFSET 'Jumpering Schedule' E5,0,0"

etc etc. After I've stripped the relevant data from the original bloated sheet, I can start working on it. That part isn't going too badly, and isn't relevant to my query.

The trouble is 'Jumpering Schedule' changes every day. I can't be the only one in this situation, is there no standard way to deal with it? Or do I really have to learn macros?

----------

After some tinkering, I discovered that copying the contents of the new version of 'Jumpering Schedule' and pasting the contents - not the worksheet - my formulas will now work on the new data. Yay!

 

[rbrian]

And now for another frustrating problem: I need to reference the physical positions of terminations on a large metal frame. The terminations are numbered nn.nnnn - e.g. 34.0567. I need to translate this to a frame reference, which is vertical - level - termination - e.g. 17-4-56, which allows you to find it easily without having to count.

To translate the terminations to physical locations, I have made a mapping table - I start at 00.0000 in cell A1, and A2 contains "=SUM(A1,0.0001)", copied down through the table. This increments each row by 0.0001, and column B contains the physical locations. I use =VLOOKUP to find each termination and return the physical location. It should be simple, but often =VLOOKUP can't find the termination - instead of containing 34.0567, which is what I'm looking for, the cell actually contains 34.05699998. How does that happen?! Can Excel really not count to 35?

 

[jlc1978]

To translate the terminations to physical locations, I have made a mapping table - I start at 00.0000 in cell A1, and A2 contains "=SUM(A1,0.0001)", copied down through the table.

What formula are you using to get the termination number - the one you have won't yield a result of 34.0nnn.

Glad you found out that pasting contents won't lose the references. I do the same thing with a very large data set. Excel, when you replace a sheet with a sheet of the same name or remove a named sheet looses all references and doesn't reconstruct them automatically when the referenced sheet is replaced.

You could link to an external file to avoid having to cut and paste.

 

[rbrian]

It ought to yield 34.nnnn if the sheet was 340,000 rows long! And since I'm adding a clear 0.0001, in a column formatted for 4 decimal places, where is it getting the extra 5 decimal places?!

As it is, I'm starting with whatever number it starts with in real life, and adding 0.0001 at a time, a maximum of 960 times. Only 960 iterations and it starts making mistakes? Something strange going on there.

I've found a way round it, making column C starting at 0.0000, then 0.0001, then 0.0002, then dragging autofill all the way down to 960. A1 contains the start number, for example 34.0456. Then the formula in A2 is "=SUM(A$1)+(C2)", autofilled all the way down.

This gets the correct result now, iterating 0.0001 each row, but for some reason =VLOOKUP still can't find the number, throwing up a #NA! error. Better than #REF!, but still not helpful.