I feel like this should be an easy question but I can't figure it out.
I am using an INDEX formula to get a value from another spreadsheet. Sometimes the value is found on Row "17", other times on row "31" or "52', etc.
an example of the formula is:
INDEX('[FY 2019 Hour Allocations.xlsx]Q2 - Alloc'!$I$17:$BK$17,,MATCH($A8,'[FY 2019 VLA Hour Allocations.xlsx]Q2 - Alloc'!$I$4:$BK$4,0))
I use this formula, or a variant about 80 time on each of 40 sheets. I would like to lookup the "17" in the formula, from a single cell at the top of the sheet (or perhaps another sheet). The best I could come up with is pretty inelegant: CONCATENATE("'[FY 2019 VLA Hour Allocations.xlsx]",INDIRECT("k7"),"'!","$I$",INDIRECT("M1"),":$bk$",INDIRECT("M1")), where "k7" has the name of referenced sheet, and "M1" has the row number.
Is there a way to replace the "17" with a cell reference?
Thanks
I am using an INDEX formula to get a value from another spreadsheet. Sometimes the value is found on Row "17", other times on row "31" or "52', etc.
an example of the formula is:
INDEX('[FY 2019 Hour Allocations.xlsx]Q2 - Alloc'!$I$17:$BK$17,,MATCH($A8,'[FY 2019 VLA Hour Allocations.xlsx]Q2 - Alloc'!$I$4:$BK$4,0))
I use this formula, or a variant about 80 time on each of 40 sheets. I would like to lookup the "17" in the formula, from a single cell at the top of the sheet (or perhaps another sheet). The best I could come up with is pretty inelegant: CONCATENATE("'[FY 2019 VLA Hour Allocations.xlsx]",INDIRECT("k7"),"'!","$I$",INDIRECT("M1"),":$bk$",INDIRECT("M1")), where "k7" has the name of referenced sheet, and "M1" has the row number.
Is there a way to replace the "17" with a cell reference?
Thanks