referencing

[ben_sorensen]

Hello I have been banging my head on the table for days now.

So I have this equation in my excel worksheet:

=INDEX(FY2014_Table,MATCH($D11,FY_2014_[[#All],[StrNbr]],0),MATCH($G$6,FY_2014_[#Headers],0))

okay so what I need is for anything that is FY_2014_ to be able to be changed to FY_2015_ or FY_2016_ or FY_2013_.........

So I have a drop down list on my page that have all of those names there exactly, but everytime I take out the FY_XXXX_ and replace it with the cell reference it throws back an error, I know that I am trying to cut of part of the reference and rplace it with a reference but I have tried a million things and can't get it to work.

Any Help would be appreciated.

Thanks

 

[RickXL]

Hi,

Look up the INDIRECT function. That allows you to convert variables into constant expressions.

For instance, try replacing Fy2014_Table in your equation with:
INDIRECT(A1 & "Table")
Then if cell A1 contained: FY2015_ your formula would point to FY2015_Table.

Change the other references appropriately.

 

[ben_sorensen]

I slapped myself in the forehead, I didn't even think about using the indirect function. Thanks that did the trick!

 

[RickXL]

No problem - I am pleased you have got it sorted.