Forum Discussion
Be able to reference table name listed in a cell as input for a function (copy of text in function)
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!
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].
4 Replies
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].
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)
Hi Peter, I have Office 2021, so no newfangled stuff <grin>
HansVogelaar Thank you, this was exactly what I was looking for!
