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 the 72 changes based on the value of the last row of data in the sheet in a different workbook - I use a max() function to get that number. I named the cell with the result, AMs. This max() function is in a different workbook on Sheet1) The two sheets in the workbook where I am doing the vlookup() from are named "Current" and "AM Only". The formula is on the "Current" sheet.
Here is the formula: =IF(H5="Unknown","TopRx",VLOOKUP(M5,'[Account Managers Office 06.07.23_Final.xlsx]Sheet1'!$C$2:INDIRECT("$H$" & AMs),6,TRUE))
In the calling workbook, H5="Local" so it moves to the VLOOKUP() function. M5 is the name of a sales rep found within the range in the other workbook on "Sheet1"
Independently, $C$2:INDIRECT("$H$" & AMs) returns $H$72" in the formula bar as desired but using it in the vlookup() formula, I get #VALUE!
In the "Help with this Error" popup it says "A value used in the formula is of the wrong data type"
Any help would be greatly appreciated.
Thank you for your consideration.
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.
