Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update vlookup formula sheet reference for multiple sheets | Excel Worksheet Functions | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
vlookup colums with reference to a formula from another sheet | Excel Worksheet Functions | |||
Using a cell reference of a sheet in Vlookup | Excel Worksheet Functions |