Referencing a column in a table based on a variable

[simonj64]

Hi,

Apologies for raising this, but really struggling to get this working.

I have a table, one column in the table will reference one of 15 other columns in the table based on a selection made by a user - I cannot get this to work with INDIRECT and most of the comments I have read indicate as it is a volatile function, do not use it anyhow.

So I have 15 period columns in a table (myTable) as follows Prd1, Prd2........Prd15
I have a named range elsewhere in the workbook, where a user selects a value between 1-15, called nPeriod.
Back to the table, I have a column which needs to reference one of the 15 period columns - what formula should be entered into this column to reference one of the 15 based on the user entry.

Thanks for any assistance.

Simon

 

[pgc01]

Hi Simon

Not clear if you want to reference just the data or to include the headers. Anyway, you can use Index() and a structured reference for the table.

For ex., for the data part, use:

INDEX(MyTable,0,nPeriod)

 

[simonj64]

Thanks PGC, this has worked a treat.

 

[pgc01]

You're welcome. Thanks for the feedback.