Forum Discussion
Using INDIRECT to dynamically produce the second part of a table range
When using INDIRECT to produce the second part of a table range. (The last row changes and I want my range to reflect the start of the range as $C$2 and the end of the range be variable. $H$72 (where...
oh where to start...
In answer to your question, INDIRECT will return a CELL REFERENCE so that part of the formula you have:
'[Account Managers Office 06.07.23_Final.xlsx]Sheet1'!$C$2:INDIRECT("$H$" & AMs)
is from that Account Mangers... worksheet TO this worksheet cell $H$72
I believe to make this work you need:
INDIRECT("'[Account Managers Office 06.07.23_Final.xlsx]Sheet1'!$C$2:$H$" & AMs)
That said I always try to avoid INDIRECT and believe you can avoid it here.
For example in Excel 365 you could use:
DROP(TAKE('[Account Managers Office 06.07.23_Final.xlsx]Sheet1'!$C:$H),AMs),1)In older excel you could:
'[Account Managers Office 06.07.23_Final.xlsx]Sheet1'!$C$2:INDEX('[Account Managers Office 06.07.23_Final.xlsx]Sheet1'!$H:$H), AMs)
Thank you for your help. I will try your suggested method.