taking parts of equation to reference other sheets

[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

 

[Rijnsent]

Hi Ben,

I guess this is what would help, taking a simple example from a workbook here:

=MATCH("NOCODE";TblNewWBS[wbs];0) -> works like a charm, normal formula
=MATCH("NOCODE";"TblNewWBS[wbs]";0) -> does not work, reference to table fails, as it is just text
=MATCH("NOCODE";INDIRECT("TblNewWBS[wbs]");0) -> works, Excel interprets the referred range perfectly

So I guess your formula should be, assuming your year is in cell A1:

=INDEX(FY2014_Table,MATCH($D11,INDIRECT("FY_"&A1&"_[[#All],[StrNbr]]",0),MATCH($G$6,INDIRECT("FY_"&A1&"_[#Headers]"),0))

Cheers,

Koen