Using Reference Tables in calculations

[BritBiker2]

I have a set of test sheets for the job I do that I wish to modify. I have another set that I can not use but it has a reference table that I do want to use. When I have tried to incorperate it into my workbook I have found the following problems:



1) The original reference table scrolls but when it is copied it does not.
2) Sometimes it forms a link with the original work book which I do not want.
3)Though I have copied the original formulas to refer to the reference table it does not seem to work in the new one.

Can anyone help?

[macropod]

Without seeing a workbook containing the originals, and another with the problems, it's difficult to be sure what the issues are.

1. Probably because copying & pasting loses the window splitting that applied to the original.
2. Usually happens when the source has references to another worksheet. You can fix this using Find/Replace to delete the unwanted workbook references.
3. You need to make sure the worksheet the reference table is on has the same name as in the original workbook, or change the sheet references in the formulae. You also need to make sure the reference table is in the same rows & columns as before.

There may be other issues, but without seeing the workbooks, it's hard to say.

[BritBiker2]

Thanks for your reply.

Is there any way I can send you the work books directly?

[macropod]

You can attach files to a post (delete anything sensitive) via the paperclip symbol on the 'Go Advanced' tab.

[BritBiker2]

Hi

have now added the test sheets concerned. In test sheets with ref table i am interested in the water terminal sheet and the reference table. I want to move or copy the reference table to another set of sheets ie the working copy without losing the scroll facility. I then want to add all the stuff in red (first copy) to the relevant sheet in the working copy. Up untill now every time i try to do this it will not come up with the correct result.

Many thanks for any help.

[macropod]

Hi BritBiker,

After copying the refernce table to the new workbook, select cell A4 then choose View>Freeze Panes>Freeze Panes. That's all there is to it. You may also want to change the zoom ratio and sheet name to match the original.

As for the external links in the "Water master excel working copies" workbook, your "Water Terminal Balance Sheet" worksheet has links to a file named "Recom Water master excel copies modified.xls". Do a Find for that and, in the cell's formula, copy everything up to and including the ], paste that into the 'Find' box and paste ' (a tick mark/single quote) into the Replace box. Do a 'Replace All' and the links should be gone.