Worksheet #REF! Issue

Question

I have a template worksheet that will serve as a summary of other worksheets as part of a monthly report. The template references worksheets that don't currently exist, in the template so I understand why I see the #REF! error initially. However, when I copy and paste the appropriately named worksheets into my template, they do exist. I try to refresh the template with CTRL-ALT-F9, but it still shows #REF!. The only way i can get it to update is to go into each cell on my template and hit ENTER. I really don't want to have to do that for each cell individually. Is there a better way to get my entire sheet to update after i past in the referenced worksheets?

Answer 1

You can set up your formulas in your template workbook to reference the missing sheets in a blank "helper" workbook with all of the sheets included--similar to how you would use a helper column. After adding the sheets to your template workbook, simply Edit Links to change the source workbook to your template workbook and all of the formulas will update to the new sheets in the that workbook.

Answer 2

Indeed, once you see #REF! the link is broken and Excel doesn't remember. You could use the INDIRECT function. Let's say you intend to link a cell in a summary sheet to C10 in a sheet called "mydata". Enter that sheet name somewhere in your template. For example in A1 on a sheet called "SheetNames".

Then you can enter this formula on your summary sheet:

=INDIRECT(SheetNames!A1&"!C10")

When "mydata" doesn't exist you get the REF error, but as soon as you create a sheet called "mydata" the formula will calculate.

However, if this is done for a great number of formula's in your workbook it may adversely impact performance. But you can give it a try.