convert formula to use dynamic sheet reference

[mtk1985]

Hi All,

Looking for help to convert this formula to use a dynamic sheet reference in cell B1. I tried it myself but always broke the logical statement in the process.

Here is the working formula (without the dynamic sheet reference)

=INDEX('[agent_raw.xlsx]2.14'!$B:$B,MATCH(MAX(IF('[agent_raw.xlsx]2.14'!$A:$A="MTVSSL",'[agent_raw.xlsx]2.14'!$Z:$Z)),IF('[agent_raw.xlsx]2.14'!$A:$A="MTVSSL",'[agent_raw.xlsx]2.14'!$Z:$Z),0))

• I have a Combobox with a list of Months. If for example January is selected, cell B1 will contain the dynamic reference 1.14
• 1.14 stands for January 2014
• My sheets are labelled as 1.14 etc
• I need to include a dynamic reference in the formula to change only the sheet name (The workbook names is fixed)

Thanks for your support

 

[VoG]

Try INDIRECT http://www.contextures.com/xlFunctions05.html

 

[mtk1985]

Try INDIRECT http://www.contextures.com/xlFunctions05.html

Thank you, managed to work it out few mins ago

=INDEX(INDIRECT("'"&B1&"'!B:B"),MATCH(MAX(IF(INDIRECT("'"&B1&"'!A:A")="MTVSSL",INDIRECT("'"&B1&"'!AJ:AJ"))),IF(INDIRECT("'"&B1&"'!A:A")="MTVSSL",INDIRECT("'"&B1&"'!AJ:AJ")),0))

I am undecided to leave the workbook name in the referencing cell or to have it fixed in the formula reference... looking at how to fix it now but probably will revert back.

 

[mtk1985]

All sorted

With Fixed workbook reference:
=INDEX(INDIRECT("'"&"[agent_raw.xlsx]"&B1&"'!B:B"),MATCH(MAX(IF(INDIRECT("'"&"[agent_raw.xlsx]"&B1&"'!A:A")="MTVSSL",INDIRECT("'"&"[agent_raw.xlsx]"&B1&"'!AJ:AJ"))),IF(INDIRECT("'"&"[agent_raw.xlsx]"&B1&"'!A:A")="MTVSSL",INDIRECT("'"&"[agent_raw.xlsx]"&B1&"'!AJ:AJ")),0))

Without Fixed workbook reference:
=INDEX(INDIRECT("'"&B1&"'!B:B"),MATCH(MAX(IF(INDIRECT("'"&B1&"'!A:A")="MTVSSL",INDIRECT("'"&B1&"'!AJ:AJ"))),IF(INDIRECT("'"&B1&"'!A:A")="MTVSSL",INDIRECT("'"&B1&"'!AJ:AJ")),0))