Sheet Reference in vlookup

[Guest]

I coded functionality into a master workbook to browse & attach a worksheet
from another closed workbook so I could use its data in the master
spreadsheet. I use vlookup in that master workbook to pull the data from the
spreadsheet I just imported.

How can I set the Table Array reference in vlookup to recognize an imported
worksheet named 'Summary' instead of getting the error #ref! ? (I built the
spreadsheet with a summary page attached so that I could be sure my vlookups
were set right but got the #ref! error when I deleted that spreadsheet and
imported another one?

 

[Tom Ogilvy]

=Vlookup(A1,Indirect("Summary!A1:Z26"),2,False)

You will get an error while the sheet is missing, but as soon as you bring
in another named summary, it should work again.

 

[Dave Peterson]

I'd just keep an empty sheet named Summary and then just copy the data from the
other sheet and paste it into this Summary sheet.

And to add to Tom's response, you can avoid the error with something like:

=IF(ISERROR(CELL("address",INDIRECT("Summary!A1"))),"missing",
VLOOKUP(A1,INDIRECT("Summary!A:Z"),2,FALSE))

(all one cell)

Change "missing" to whatever you like to see (maybe "" ???).