Forum Discussion

J. G.'s avatar
J. G.
Copper Contributor
Jan 25, 2018
Solved

EXCEL - Use dynamically created string as refference to table objects

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...

  • If 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)

     

2 Replies

  • If 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)

     

    • J. G.'s avatar
      J. G.
      Copper Contributor
      Thank you very much Ingeborg. I had tried indirect before, but it wouldn't work. I now discovered i really had (unsupprisingly) a syntactic error in the formula. So, thanks for pointing me into the right direction again. :) Jan

Resources