wrightjames
New Member
- Joined
- Mar 25, 2020
- Messages
- 4
- Office Version
- 2007
- Platform
- Windows
Hello Mr Excel
I'm trying to create a dynamic formula that picks up the last used cell in a list on another sheet. I've managed to create a formula, (see below) that gets me the row and column of the position of this last non blank cell on another sheet.
ADDRESS(MAX((Lookup!N2:N1000<>"")*(ROW(Lookup!N2:N1000))),COLUMN(Lookup!N2:N1000)) = $N$203
I am now trying to substitute this $N$203 reference into the formula below so that it dynamically looks up this range, but I keep getting reference errors. Not sure if the INDIRECT formula is used or not. Is there a way of joining this referenced cell into the range below?
LOOKUP(1,0/COUNTIF(K5,"*"&Lookup!$N$2:$N$203&"*"),Lookup!$N$2:$N$203)
Hope I've been able to clarify my question. Please can someone give me assistance.
Thanks so much
James
I'm trying to create a dynamic formula that picks up the last used cell in a list on another sheet. I've managed to create a formula, (see below) that gets me the row and column of the position of this last non blank cell on another sheet.
ADDRESS(MAX((Lookup!N2:N1000<>"")*(ROW(Lookup!N2:N1000))),COLUMN(Lookup!N2:N1000)) = $N$203
I am now trying to substitute this $N$203 reference into the formula below so that it dynamically looks up this range, but I keep getting reference errors. Not sure if the INDIRECT formula is used or not. Is there a way of joining this referenced cell into the range below?
LOOKUP(1,0/COUNTIF(K5,"*"&Lookup!$N$2:$N$203&"*"),Lookup!$N$2:$N$203)
Hope I've been able to clarify my question. Please can someone give me assistance.
Thanks so much
James