Creating copy of sheet into new file results in #REF errors

[Sungibungi]

I am trying to copy of a worksheet into a new file but receive #REF errors on
the new workbook when I do. I believe the cause of the problem is the
following formula:

='21day wksht'!$F$3+(IF(ISERROR(VLOOKUP(B3,INDIRECT("'"&B372&"
Update'!$A$7:$L$43"),12)),VLOOKUP(OFFSET($B$369,-396,MATCH(1,$B$369:$V$369,0)-3,1,1),INDIRECT("'"&OFFSET($B$369,3,MATCH(1,$B$369:$V$369,0)-3,1,1)&"
Update'!$A$7:$L$43"),12),VLOOKUP(B3,INDIRECT("'"&B372&"
Update'!$A$7:$L$43"),12)))

Because of this formula, everything else based off of this cell becomes a
problem. Why does this happen? It's simply referencing everything back to the
old worksheet is it not? Plus, I designed the macro so do the following:

1. Create a copy of the sheet into a new file
2. In the new sheet (if things don't come up with these #REF errors), copy
paste special values.

Let me know if this a common mistake. Thanks so much for all your help in
advance.

 

[FrankWood]

It’s difficult to tell for sure what is happening in the formula without
seeing the entire worksheet you are using, however…

When you are using the Indirect() function to figure out what cells to use
in the Vlookup() function, the cells referenced must have a valid cell
address that could be used in the Vlookup.

Such as in one spot, you are doing a Vlookup of cell B3 in a range
determined by:
INDIRECT("'"&B372&" Update'!$A$7:$L$43")

So it follows that B372 would need to have a valid path/filename that can be
found from the current file. My hunch is that these are not returning valid
path/filename:Sheetname!cell references.

If that cell reference does not return the value correctly it would result
in a Reference error as you describe.

It might be helpful to use the Formula Auditing, Evaluate Formula feature to
slowly step through the execution of your formula and perhaps see where it is
going awry.

Hope that helps.

Frank Wood