Jan 25 2018
12:23 PM
- last edited on
Jul 25 2018
10:53 AM
by
TechCommunityAP
Jan 25 2018
12:23 PM
- last edited on
Jul 25 2018
10:53 AM
by
TechCommunityAP
Hello there,
i was trying to dynamically create links in a column of an "addresstable" in a workbook. All the necessary information is stored in separate dedicated tables in the document.
First, i dynamically create the tablenames as strings in the "tablename_string"-column of the addresstable. And then in the next column (Link) I try to use this string in an index function in order to to retrieve the necessary information from the according tables (Table1, Table2,...).
Table_name | tablename_string | Link
-------------------------------------------------------------------------------------------
Tabelle1 | =([@Table_name]&"[addressfield]") | Link1
Tabelle2 | =([@Table_name]&"[addressfield]") | Link2
Tabelle3 | =([@Table_name]&"[addressfield]") | Link3
Unfortunately i already fail at this step. The formula [@Table_name]&"[addressfield]" does create the correct tablenames, but when i try to use the strings in the following index formula as the required array component, excel will evaluate the reference as a string rather then a table object.
Now, as i'm no excel expert i'm already lost as to the question if this is merely a syntactical issue or if the problem is more profound.
I'm using Excel 2016
And I'm also no native speaker, so don't hesitate to ask whatever necessary :)
Many thanks in advance for your help...
Jan 25 2018 01:05 PM
SolutionIf the table string builds correctly, then you need to use the Indirect() function to turn that string into a reference. Like this
=INDEX(INDIRECT([@[tablename_string]]),2)
Jan 25 2018 08:38 PM
Jan 25 2018 01:05 PM
SolutionIf the table string builds correctly, then you need to use the Indirect() function to turn that string into a reference. Like this
=INDEX(INDIRECT([@[tablename_string]]),2)