Sheet Reference in vlookup
 

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?

Sheet Reference in vlookup
 

=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.

--
Regards,
Tom Ogilvy

"Sean" wrote in message
...
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?

Sheet Reference in vlookup
 

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 "" ???).

Sean wrote:

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?

--

Dave Peterson