I have a spreadsheet with 2 sheets (Dashboard,Sheet2)
I would like to find a way to use a VLOOKUP formula to get the Sheet name & range from a specific cell, then perform the lookup function.
On the Dashboard sheet, it looks like...
| A | B | |
|---|---|---|
| 1 | Sheet Name & Range: | Sheet2!A1:B10 |
| 2 | =VLOOKUP("Key", B1, 2, FALSE) |
I have tried...
=VLOOKUP("Key", B1, 2, FALSE)
=VLOOKUP("Key", B1:B1, 2, FALSE)
=VLOOKUP("Key", '"&B1&"', 2, FALSE)
=VLOOKUP("Key", FORMULATEXT(B1), 2, FALSE)
The reason why I am looking for a solution this because Sheet2 is updated daily by Alteryx. It does so by deleting all the cells, then adding the new data. Every time it does this, it causes a REF# error and breaks the formula.
Any help or links to learn how to do this would be greatly apricated. Thanks :)
CodePudding user response：
You need INDIRECT() function.
=VLOOKUP("Key", INDIRECT(B1), 2, FALSE)