Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dynamic range for Table_array in a VLOOKUP.
A vlookup is simple in it's capabilities, but I want to get clever.
I'm looking up a value from another workbook. Simple enough in itself, but heres the thing. I'd like to write a dynamic vlookup that checks the value of another cell to find the table array for it to look up from. Basically I have a table of data, the column headers are dates. I have a report that is run daily that I need to look up from. Rather than implicitly referencing each sheet I'd like to tell the vlookup to check the date in the column header and use that for the sheet. The range inside of the sheets is always the same. Any help would be appreciated. TIA. |
#2
|
|||
|
|||
try something like
=vlookup(lookup value,offset(indirect(A10),0,0,height,width), col) where A10 would contain the sheetname and cell reference for the upper left cell in the lookuprange ie 'Sheet 10'!A24 "DaveO" wrote: A vlookup is simple in it's capabilities, but I want to get clever. I'm looking up a value from another workbook. Simple enough in itself, but heres the thing. I'd like to write a dynamic vlookup that checks the value of another cell to find the table array for it to look up from. Basically I have a table of data, the column headers are dates. I have a report that is run daily that I need to look up from. Rather than implicitly referencing each sheet I'd like to tell the vlookup to check the date in the column header and use that for the sheet. The range inside of the sheets is always the same. Any help would be appreciated. TIA. |
#3
|
|||
|
|||
It's in another Workbook entirely. It's getting the other workbook name being
dynamic that I'm having the problems with.# TIA. "bj" wrote: try something like =vlookup(lookup value,offset(indirect(A10),0,0,height,width), col) where A10 would contain the sheetname and cell reference for the upper left cell in the lookuprange ie 'Sheet 10'!A24 "DaveO" wrote: A vlookup is simple in it's capabilities, but I want to get clever. I'm looking up a value from another workbook. Simple enough in itself, but heres the thing. I'd like to write a dynamic vlookup that checks the value of another cell to find the table array for it to look up from. Basically I have a table of data, the column headers are dates. I have a report that is run daily that I need to look up from. Rather than implicitly referencing each sheet I'd like to tell the vlookup to check the date in the column header and use that for the sheet. The range inside of the sheets is always the same. Any help would be appreciated. TIA. |
#4
|
|||
|
|||
Try something like this:
=VLOOKUP(A2,INDIRECT("'"&A11&"'!$A$2:$M$7"),7,0) Where you enter the sheet name in A11. Remember, that a sheet name is *not* a date, so the value in A11 *must* be a text value (2-1-2005). -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "DaveO" wrote in message ... It's in another Workbook entirely. It's getting the other workbook name being dynamic that I'm having the problems with.# TIA. "bj" wrote: try something like =vlookup(lookup value,offset(indirect(A10),0,0,height,width), col) where A10 would contain the sheetname and cell reference for the upper left cell in the lookuprange ie 'Sheet 10'!A24 "DaveO" wrote: A vlookup is simple in it's capabilities, but I want to get clever. I'm looking up a value from another workbook. Simple enough in itself, but heres the thing. I'd like to write a dynamic vlookup that checks the value of another cell to find the table array for it to look up from. Basically I have a table of data, the column headers are dates. I have a report that is run daily that I need to look up from. Rather than implicitly referencing each sheet I'd like to tell the vlookup to check the date in the column header and use that for the sheet. The range inside of the sheets is always the same. Any help would be appreciated. TIA. |
#5
|
|||
|
|||
the simplest way to get the format for the other workbook is to enter "=" and
go to the other workbook and click on the cell you want to use as the start of your lookup table. hit enter and go back to the cell and delete the "=" . this should not be in the format which would be recognised by the indirect() function. "DaveO" wrote: It's in another Workbook entirely. It's getting the other workbook name being dynamic that I'm having the problems with.# TIA. "bj" wrote: try something like =vlookup(lookup value,offset(indirect(A10),0,0,height,width), col) where A10 would contain the sheetname and cell reference for the upper left cell in the lookuprange ie 'Sheet 10'!A24 "DaveO" wrote: A vlookup is simple in it's capabilities, but I want to get clever. I'm looking up a value from another workbook. Simple enough in itself, but heres the thing. I'd like to write a dynamic vlookup that checks the value of another cell to find the table array for it to look up from. Basically I have a table of data, the column headers are dates. I have a report that is run daily that I need to look up from. Rather than implicitly referencing each sheet I'd like to tell the vlookup to check the date in the column header and use that for the sheet. The range inside of the sheets is always the same. Any help would be appreciated. TIA. |
#6
|
|||
|
|||
Thanks for the help.
I've done what you said, but the INDIRECT function is coming back as 'Volatile' whatever that means and does not work. Attached is what I'm trying to do in the formula for reference to see if it helps.... =IF(ISERROR(VLOOKUP($A$1,INDIRECT("'" & D83 &"]tblFinal Productivity Report'!$1:$65536",TRUE),2,FALSE)),0,VLOOKUP($A$1, INDIRECT("'" & D83 &"]tblFinal Productivity Report'!$1:$65536",TRUE),2,FALSE)) Cell D83 contains this ... {PATH}\[031005.xls Where {PATH} is the netowrk path of the folder that contains the sheet 031005.xls Anymore help would be gratefully received. TIA. "RagDyeR" wrote: Try something like this: =VLOOKUP(A2,INDIRECT("'"&A11&"'!$A$2:$M$7"),7,0) Where you enter the sheet name in A11. Remember, that a sheet name is *not* a date, so the value in A11 *must* be a text value (2-1-2005). -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "DaveO" wrote in message ... It's in another Workbook entirely. It's getting the other workbook name being dynamic that I'm having the problems with.# TIA. "bj" wrote: try something like =vlookup(lookup value,offset(indirect(A10),0,0,height,width), col) where A10 would contain the sheetname and cell reference for the upper left cell in the lookuprange ie 'Sheet 10'!A24 "DaveO" wrote: A vlookup is simple in it's capabilities, but I want to get clever. I'm looking up a value from another workbook. Simple enough in itself, but heres the thing. I'd like to write a dynamic vlookup that checks the value of another cell to find the table array for it to look up from. Basically I have a table of data, the column headers are dates. I have a report that is run daily that I need to look up from. Rather than implicitly referencing each sheet I'd like to tell the vlookup to check the date in the column header and use that for the sheet. The range inside of the sheets is always the same. Any help would be appreciated. TIA. |
#7
|
|||
|
|||
When using Indirect(), the WBs must be open!
Also, I don't quite follow the use of the square bracket ( ] ). Will this be used when all WBs are open? -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "DaveO" wrote in message ... Thanks for the help. I've done what you said, but the INDIRECT function is coming back as 'Volatile' whatever that means and does not work. Attached is what I'm trying to do in the formula for reference to see if it helps.... =IF(ISERROR(VLOOKUP($A$1,INDIRECT("'" & D83 &"]tblFinal Productivity Report'!$1:$65536",TRUE),2,FALSE)),0,VLOOKUP($A$1, INDIRECT("'" & D83 &"]tblFinal Productivity Report'!$1:$65536",TRUE),2,FALSE)) Cell D83 contains this ... {PATH}\[031005.xls Where {PATH} is the netowrk path of the folder that contains the sheet 031005.xls Anymore help would be gratefully received. TIA. "RagDyeR" wrote: Try something like this: =VLOOKUP(A2,INDIRECT("'"&A11&"'!$A$2:$M$7"),7,0) Where you enter the sheet name in A11. Remember, that a sheet name is *not* a date, so the value in A11 *must* be a text value (2-1-2005). -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "DaveO" wrote in message ... It's in another Workbook entirely. It's getting the other workbook name being dynamic that I'm having the problems with.# TIA. "bj" wrote: try something like =vlookup(lookup value,offset(indirect(A10),0,0,height,width), col) where A10 would contain the sheetname and cell reference for the upper left cell in the lookuprange ie 'Sheet 10'!A24 "DaveO" wrote: A vlookup is simple in it's capabilities, but I want to get clever. I'm looking up a value from another workbook. Simple enough in itself, but heres the thing. I'd like to write a dynamic vlookup that checks the value of another cell to find the table array for it to look up from. Basically I have a table of data, the column headers are dates. I have a report that is run daily that I need to look up from. Rather than implicitly referencing each sheet I'd like to tell the vlookup to check the date in the column header and use that for the sheet. The range inside of the sheets is always the same. Any help would be appreciated. TIA. |
#8
|
|||
|
|||
No, the workbooks won't be open and the ']' is needed to denote the actual
workbook name that you're getting to. At least a non-dynamix formula has square brackets in it. Is there any other way to dynamically define the Table_Array? TIA. "RagDyeR" wrote: When using Indirect(), the WBs must be open! Also, I don't quite follow the use of the square bracket ( ] ). Will this be used when all WBs are open? -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "DaveO" wrote in message ... Thanks for the help. I've done what you said, but the INDIRECT function is coming back as 'Volatile' whatever that means and does not work. Attached is what I'm trying to do in the formula for reference to see if it helps.... =IF(ISERROR(VLOOKUP($A$1,INDIRECT("'" & D83 &"]tblFinal Productivity Report'!$1:$65536",TRUE),2,FALSE)),0,VLOOKUP($A$1, INDIRECT("'" & D83 &"]tblFinal Productivity Report'!$1:$65536",TRUE),2,FALSE)) Cell D83 contains this ... {PATH}\[031005.xls Where {PATH} is the netowrk path of the folder that contains the sheet 031005.xls Anymore help would be gratefully received. TIA. "RagDyeR" wrote: Try something like this: =VLOOKUP(A2,INDIRECT("'"&A11&"'!$A$2:$M$7"),7,0) Where you enter the sheet name in A11. Remember, that a sheet name is *not* a date, so the value in A11 *must* be a text value (2-1-2005). -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "DaveO" wrote in message ... It's in another Workbook entirely. It's getting the other workbook name being dynamic that I'm having the problems with.# TIA. "bj" wrote: try something like =vlookup(lookup value,offset(indirect(A10),0,0,height,width), col) where A10 would contain the sheetname and cell reference for the upper left cell in the lookuprange ie 'Sheet 10'!A24 "DaveO" wrote: A vlookup is simple in it's capabilities, but I want to get clever. I'm looking up a value from another workbook. Simple enough in itself, but heres the thing. I'd like to write a dynamic vlookup that checks the value of another cell to find the table array for it to look up from. Basically I have a table of data, the column headers are dates. I have a report that is run daily that I need to look up from. Rather than implicitly referencing each sheet I'd like to tell the vlookup to check the date in the column header and use that for the sheet. The range inside of the sheets is always the same. Any help would be appreciated. TIA. |
#9
|
|||
|
|||
See if this link can help:
http://xcell05.free.fr/english/moref...direct.ext.htm -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "DaveO" wrote in message ... No, the workbooks won't be open and the ']' is needed to denote the actual workbook name that you're getting to. At least a non-dynamix formula has square brackets in it. Is there any other way to dynamically define the Table_Array? TIA. "RagDyeR" wrote: When using Indirect(), the WBs must be open! Also, I don't quite follow the use of the square bracket ( ] ). Will this be used when all WBs are open? -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "DaveO" wrote in message ... Thanks for the help. I've done what you said, but the INDIRECT function is coming back as 'Volatile' whatever that means and does not work. Attached is what I'm trying to do in the formula for reference to see if it helps.... =IF(ISERROR(VLOOKUP($A$1,INDIRECT("'" & D83 &"]tblFinal Productivity Report'!$1:$65536",TRUE),2,FALSE)),0,VLOOKUP($A$1, INDIRECT("'" & D83 &"]tblFinal Productivity Report'!$1:$65536",TRUE),2,FALSE)) Cell D83 contains this ... {PATH}\[031005.xls Where {PATH} is the netowrk path of the folder that contains the sheet 031005.xls Anymore help would be gratefully received. TIA. "RagDyeR" wrote: Try something like this: =VLOOKUP(A2,INDIRECT("'"&A11&"'!$A$2:$M$7"),7,0) Where you enter the sheet name in A11. Remember, that a sheet name is *not* a date, so the value in A11 *must* be a text value (2-1-2005). -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "DaveO" wrote in message ... It's in another Workbook entirely. It's getting the other workbook name being dynamic that I'm having the problems with.# TIA. "bj" wrote: try something like =vlookup(lookup value,offset(indirect(A10),0,0,height,width), col) where A10 would contain the sheetname and cell reference for the upper left cell in the lookuprange ie 'Sheet 10'!A24 "DaveO" wrote: A vlookup is simple in it's capabilities, but I want to get clever. I'm looking up a value from another workbook. Simple enough in itself, but heres the thing. I'd like to write a dynamic vlookup that checks the value of another cell to find the table array for it to look up from. Basically I have a table of data, the column headers are dates. I have a report that is run daily that I need to look up from. Rather than implicitly referencing each sheet I'd like to tell the vlookup to check the date in the column header and use that for the sheet. The range inside of the sheets is always the same. Any help would be appreciated. TIA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic named range across multiple sheets | Excel Discussion (Misc queries) | |||
Dynamic Range Chart ??? | Charts and Charting in Excel | |||
how to change range for dynamic chart in excel 2000 with button? | Charts and Charting in Excel | |||
dynamic range for excel chart | Excel Discussion (Misc queries) | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |