Excel table - using a formula in a structured reference

**jankee** · 05-22-2013, 06:24 AM

Hi,

Let's say that I have an "official" Excel table with the number of employees per department.

Then I have many other sheets, one sheet per department actually, each containing complex formulas.

I would like to use structured references in these formulas, like this:

Employees[[#All],[DeptSales]]

What I would like to do is to replace DeptSales in my formula by the cell A1 because there is the name of the department.

I tried Employees[[#All],[indirect(A1)]] but it doesn't work.

The reason why I want to do this is that there are many departments so I would like only to change the text in the cell A1 and not need to adapt all the formulas.

Any ideas? Please?

**XOR LX** · 05-22-2013, 07:52 AM

Hi,

Not too sure without seeing your data, but perhaps you can adapt this example to meet your needs?

Assuming my data table is called Table1 and cell A1 contains the text "DeptSales" then these two formulae will give the same result:

=SUM(Table1[DeptSales])

=SUM(INDIRECT("Table1["&A1&"]"))

Hope that helps

Regards