Be able to reference table name listed in a cell as input for a function (copy of text in function)

[Maar7]

Hi all! 

I wonder if it is possible to write down the name of a table column in a cell (for instance Table3[Time passed] in cell B3) and use that as an input in a function. This should look like this: 

=ADDRESS(ROW(=B3),COLUMN(=B3)) 

instead of 

=ADDRESS(ROW(Table3[Time passed]),COLUMN(Table3[Time passed])) 

I use the same table name a lot in several functions, some much longer than this one. I also want to vary the input of these function for different columns more easily (so Table3[Time passed] and Table3[Temperature]), and changing the name of the table as input in the functions is very cumbersome and prone to mistakes. Is there a way that I can take the value of the cell as cell in a function? This function it gives me the address of B3, instead of the address of Table3[Time passed]

I hope to hear from you! 

[#2 · Accepted solution]

@Maar7 

You could use the INDIRECT function for this:

=ADDRESS(ROW(INDIRECT(B3)),COLUMN(INDIRECT(B3)))

Warning: in Excel in Microsoft 365 and Office 2021, this returns a dynamic array with the addresses of all cells in Table3[Time Passed].

[#3]

@Hans Vogelaar Thank you, this was exactly what I was looking for! 

[#4]

@Hans Vogelaar 

Hans, do you have a version of Excel 365 with VSTACK?

If so, the attached file, though probably overcomplicated, is interesting that it returns a Table, or a specific field, specified by name, but without using the volatile function INDIRECT.

=Tableλ(tableName,fieldName)

[#5]

@Peter Bartholomew 

Hi Peter, I have Office 2021, so no newfangled stuff <grin>