Get Table from Cell

[bassplr19]

I have a table that is looking up data from a different table. One of the input variables is the header, I have 29 columns that all have the same formula (across 250 different tables). I'd like to write a lambda function, so I can drag the formula across the whole table. Is there a way I can tell that cell A13 is in table LookupA3 and cell A24 is in LookupA4, for example? Even better, I'd like it to tell me what it's header is!
I'd like to NOT manually enter the head as D$2, then drag...again 250 tables

 

[Rhodie72]

Ever selected the cells you want to update and then pressed Ctrl+Enter? It fills the formula across the entire selection of cells even if they are not adjacent.

 

[bassplr19]

That did not work, nor answer my question...

 

[Rhodie72]

Sorry, you have to select the cells and enter the formula then press ctrl+enter to update them all. Beyond that I am not sure what you are trying to achieve as there is no sample data to understand it.

 

[bassplr19]

Ok, that might help a little bit. But my question is, if I'm in Cell B7 and B7 is in column xyz of Table LookupA1, how can I use cell B7 to use itself to tell me it's header data, i.e., xyz? LookupA1[[#Headers],[xyz]]

Otherwise, using the CTRL-Enter trick, I still need to type in my lambda function datalookup(B$7 (header info),$A8 (first column),$A$15 (outside table range variable)) and these cells change for EACH table.

 

[Rhodie72]

Ok, that might help a little bit. But my question is, if I'm in Cell B7 and B7 is in column xyz of Table LookupA1, how can I use cell B7 to use itself to tell me it's header data, i.e., xyz? LookupA1[[#Headers],[xyz]]

I think that's best achieved with VBA, I'm not great wwith formulas as they are very limited in capability. With VBA you can specify that it returns the value of specific cells relative to the active cell very easily. It may be worth your while trying to learn a little VBA in the process so you get a feel for its power and usefiulness in not only Excel mu all MS Office applications.