waltdakind
New Member
- Joined
- May 11, 2010
- Messages
- 30
So, I have multiple tables that each represent a fiscal year. (TableFYE_2010, TableFYE_2011, TableFYE_2012, etc.,) and the tables are all set up with the first column header for GL code the Column name is GLCode, and the remaining column headers are department codes (various three digit numbers). I am trying to set up a formula that permits me to dynamically look up values in the various tables.
I have been successful in looking up the two values(GLCode and Dept Code) in a table with the following formula, but I want to be able to change the table name in another cell (in this case, "K27")
This works:
=HLOOKUP(L27, TableFYE_2010[#All],MATCH(M27,TableFYE_2010[[#All],[GLCode]],0),0)
This addition of a reference to a table name in a cell returns an error:
=HLOOKUP(L27, K27[#All],MATCH(M27,K27[[#All],[GLCode]],0),0)
I have been looking into using INDIRECT to reference the table name, and trying the following syntax
=HLOOKUP(L27,INDIRECT(K27&"[#All]&"),MATCH(M27,INDIRECT(K27&"[[#All]&,[GLCode]]&",0),0))
But it returns an error too (although a different one -- invalid cell reference #REF error). Also, I am hoping to find a non-volatile way of doing this.
Ideas?
Thanks!
I have been successful in looking up the two values(GLCode and Dept Code) in a table with the following formula, but I want to be able to change the table name in another cell (in this case, "K27")
This works:
=HLOOKUP(L27, TableFYE_2010[#All],MATCH(M27,TableFYE_2010[[#All],[GLCode]],0),0)
This addition of a reference to a table name in a cell returns an error:
=HLOOKUP(L27, K27[#All],MATCH(M27,K27[[#All],[GLCode]],0),0)
I have been looking into using INDIRECT to reference the table name, and trying the following syntax
=HLOOKUP(L27,INDIRECT(K27&"[#All]&"),MATCH(M27,INDIRECT(K27&"[[#All]&,[GLCode]]&",0),0))
But it returns an error too (although a different one -- invalid cell reference #REF error). Also, I am hoping to find a non-volatile way of doing this.
Ideas?
Thanks!