Structured reference that refers to table name,a value in header row, and a value from 1st column

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!
 

Forum statistics

Threads
1,081,615
Messages
5,360,037
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top