Dynamic reference to a table in a formula

[scecchin]

Hi

I am trying to determine how I can use the name of a table in a formula where I determine the name of a table depending on a lookup value.

This is what I mean: I have two tables in a worksheet; table t_One and table t_Two. I want the total for a column in the table but I want to determine which table to reference, t_One or t_Two, based on the value in another cell. I have been as yet unable to write the formula, for example, << =SUM(t_One[[#Totals],[Amt]]) >> where I determined the value t_One from a lookup of a value in another cell.

Is this possible?

I have uploaded a small example file.

Thanks for your help
Sergio

 

[NARAYANK991]

Hi Sergio ,

Try this in K7 , and copy down :

=SUM(INDIRECT(J7&"[[#Totals],[Amt]]"))

If you want to do away with the lookup table altogether , you can use this in K7 and copy down :

=SUM(INDIRECT("t_" & I7 & "[[#Totals],[Amt]]"))

Narayan

 

[scecchin]

Thank you Narayan

I thought that INDIRECT() was what I needed to use but I had the syntax wrong. Actually what I did with your help is the following:

=SUM(INDIRECT(VLOOKUP(J8,t_TableNames,2,FALSE)&"[[#Totals],[Amt]]"))

This will allow me to select a unique value and then have related data returned.

Thanks again.

Sergio