jski21
Board Regular
- Joined
- Jan 2, 2019
- Messages
- 133
- Office Version
- 2016
- Platform
- Windows
Good Day Excelites,
Trying to get this formula to work across 17 tabs: =VLOOKUP("Yr 47",INDIRECT("'"&P$1&"'!"&"S1:T9"),18,0).
Excerpt of the range in each spreadsheet is here, along with the dynamic cell (Q1) that is picking up the numbered spreadsheet name:
Trying to get Cell Q1 to pull in Cell T9. Current result is #N/A. BTW, "Yr 47" will change from year-to-year as time goes on. I don't use INDIRECT that often, but am finding more uses for it as I explore ways to improve reporting. Any assistance would be most appreciated.
Thanks all and enjoy your day.
jski
Trying to get this formula to work across 17 tabs: =VLOOKUP("Yr 47",INDIRECT("'"&P$1&"'!"&"S1:T9"),18,0).
Excerpt of the range in each spreadsheet is here, along with the dynamic cell (Q1) that is picking up the numbered spreadsheet name:
NDA WARD REPORT - 2021-22 - Yrs 40-47.xlsm | |||||||
---|---|---|---|---|---|---|---|
P | Q | R | S | T | |||
1 | 12 | #N/A | Yr | 12 | |||
2 | Yr 40 | 0.00 | |||||
3 | Yr 41 | 0.00 | |||||
4 | Yr 42 | 0.00 | |||||
5 | Yr 43 | 0.00 | |||||
6 | Yr 44 | 0.00 | |||||
7 | Yr 45 | 56,876.11 | |||||
8 | Yr 46 | 435,294.00 | |||||
9 | Yr 47 | 435,294.00 | |||||
Charts |
Cell Formulas | ||
---|---|---|
Range | Formula | |
P1 | P1 | =RIGHT(O1,2) |
Q1 | Q1 | =VLOOKUP("Yr 47",INDIRECT("'"&P$1&"'!"&"S1:T9"),18,0) |
Trying to get Cell Q1 to pull in Cell T9. Current result is #N/A. BTW, "Yr 47" will change from year-to-year as time goes on. I don't use INDIRECT that often, but am finding more uses for it as I explore ways to improve reporting. Any assistance would be most appreciated.
Thanks all and enjoy your day.
jski