Using INDIRECT function in a TABLE

[Heathy65]

I have an example table in worksheet (just as an example to demonstrate my query).

The table is called Table1 and has 2 columns Col1 & Col2, Col1 containing different integer values.

In a cell outside of the table (E2) I have this text: Table1[@[Col1]]

Then in Col2 of the table I have this formula: =INDIRECT($E$2,0)

This then causes the values in Col1 to be shown in Col2 as expected.

However if I wanted to perform some mathematical operation on the Col1 (e.g. "/2") so that Col2 then shows "half-values" I am having an issue.

I assumed I would just need to edit the text in E2 to be Table1[@[Col1]]/2 but that gives an error, so I guess I'm doing something stupid. My desire is to have my formula in a single cell (E2) rather than expanding the basic INDIRECT function which is in all the cells in the table column (Col2).

Any help/guidance appreciated.

 

[Fluff]

You will need to change the Indirect function to

=INDIRECT($E$2,0)/2

 

[Heathy65]

Thanks for the prompt reply. So there's no way to go what I want within E2?

 

[Fluff]

No, the value in E2 must evaluate to a range.

 

[RoryA]

You could use EVALUATE instead of INDIRECT, as long as it's OK to save the workbook in macro-enabled format?