Help replacing indirect formula

[snocaps]

Hello Experts!

After reading through several blog posts suggesting that volatile functions should be used sparingly, I am trying to rebuild a workbook that uses indirect in a rather wanton fashion (250 times per sheet X 10 sheets). There is a sheet for each month of the year (named "January" etc, not using the month number), that has each property listed as columns. The indirect spreadsheets come into play when each property has its summary using the months as columns. The months are in row 5, the descriptions are in column A. I cannot use a table because I need blank lines for ease of reading. The formula that needs to be rewritten is =IFERROR(INDIRECT("'"&D$5&"'!$B"&MATCH($A8,INDIRECT("'"&D$5&"'!$A$1:$A$50"),0)&""),"")
I think I can use INDEX but can't quite get the right syntax worked out. Can you help? Thanks in advance!

Jenny

 

[Asheesh]

Hi Jenny - A sample file with the required result will help members to help you..

 

[snocaps]

Thank you Asheesh. I hope the file is helpful.

 

[snocaps]

I have managed to create named ranges for each month spreadsheet, and have rewritten the formula to read =IFERROR(INDEX(September,MATCH($A8,September!A:A,0),MATCH($A$3,September!$5:$5,0)),""). The problem is that the month is hard-coded because it references the defined name. Is there no way besides using INDIRECT to force the value from a cell to be the named range?