Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with dynamic table_array
I'm trying to use vlookup with multiple worksheets defined by a dynamic
table_array value. I can store the appropriate value in a cell with the correct format, but the vlookup function returns a REF error. Example: =VLOOKUP(DataCollection!B8,'Beds101-200'!A1:I68,7) works fine if I type the specific table_array name in. If I build the table_array name in cell o1, like this =TRIM("'"&TRIM(N1)&"'!B1:I68"), which displays like this: 'Beds101-200'!A1:I68, I can't get the function to work. Here is the final cell format =vlookup(DataCollection!B8,$o$1,7) I've also tried =vlookup(DataCollection!B8,"$o$1",7) Is there a way to get this to work? -- Jim T |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with dynamic table_array
Try it like this:
N1 = Beds101-200 (or whatever sheet name: Beds201-300) =VLOOKUP(DataCollection!B8,INDIRECT("'"&N1&"'!A1:I 68"),7) Biff "Jim Toohey" wrote in message ... I'm trying to use vlookup with multiple worksheets defined by a dynamic table_array value. I can store the appropriate value in a cell with the correct format, but the vlookup function returns a REF error. Example: =VLOOKUP(DataCollection!B8,'Beds101-200'!A1:I68,7) works fine if I type the specific table_array name in. If I build the table_array name in cell o1, like this =TRIM("'"&TRIM(N1)&"'!B1:I68"), which displays like this: 'Beds101-200'!A1:I68, I can't get the function to work. Here is the final cell format =vlookup(DataCollection!B8,$o$1,7) I've also tried =vlookup(DataCollection!B8,"$o$1",7) Is there a way to get this to work? -- Jim T |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with dynamic table_array
Biff:
Worked like a charm. Thanks. -- Jim T "Biff" wrote: Try it like this: N1 = Beds101-200 (or whatever sheet name: Beds201-300) =VLOOKUP(DataCollection!B8,INDIRECT("'"&N1&"'!A1:I 68"),7) Biff "Jim Toohey" wrote in message ... I'm trying to use vlookup with multiple worksheets defined by a dynamic table_array value. I can store the appropriate value in a cell with the correct format, but the vlookup function returns a REF error. Example: =VLOOKUP(DataCollection!B8,'Beds101-200'!A1:I68,7) works fine if I type the specific table_array name in. If I build the table_array name in cell o1, like this =TRIM("'"&TRIM(N1)&"'!B1:I68"), which displays like this: 'Beds101-200'!A1:I68, I can't get the function to work. Here is the final cell format =vlookup(DataCollection!B8,$o$1,7) I've also tried =vlookup(DataCollection!B8,"$o$1",7) Is there a way to get this to work? -- Jim T |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with dynamic table_array
You're welcome. Thanks for the feedback!
Biff "Jim Toohey" wrote in message ... Biff: Worked like a charm. Thanks. -- Jim T "Biff" wrote: Try it like this: N1 = Beds101-200 (or whatever sheet name: Beds201-300) =VLOOKUP(DataCollection!B8,INDIRECT("'"&N1&"'!A1:I 68"),7) Biff "Jim Toohey" wrote in message ... I'm trying to use vlookup with multiple worksheets defined by a dynamic table_array value. I can store the appropriate value in a cell with the correct format, but the vlookup function returns a REF error. Example: =VLOOKUP(DataCollection!B8,'Beds101-200'!A1:I68,7) works fine if I type the specific table_array name in. If I build the table_array name in cell o1, like this =TRIM("'"&TRIM(N1)&"'!B1:I68"), which displays like this: 'Beds101-200'!A1:I68, I can't get the function to work. Here is the final cell format =vlookup(DataCollection!B8,$o$1,7) I've also tried =vlookup(DataCollection!B8,"$o$1",7) Is there a way to get this to work? -- Jim T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic vlookup? | Excel Worksheet Functions | |||
VLOOKUP in a dynamic setting | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic range for Table_array in a VLOOKUP. | Excel Worksheet Functions | |||
Dynamic Range for Function (Vlookup etc) | Excel Worksheet Functions |