Changing the VLOOKUP location for changes in Week

Danny318

New Member
Joined
Jun 19, 2007
Messages
40
My formula is this: =VLOOKUP($A46,'[2020 Scorecard Realization OT.xlsx]42'!$L$29:$Q$60,6,FALSE)
It is getting the value for WEEK 42 from the 2020 Scorecard Realization File
Each week, I have to change the Week Number after the bracket in every formula to capture the next week VLOOKUP($A46,'[2020 Scorecard Realization OT.xlsx]43'!$L$29:$Q$60,6,FALSE)
I've tried to link it to my column headers showing the week number but it keeps giving me a formula error
I currently have to do an Find/Replace to change it each time. I'd rather have it done in the formula. Would rather not use macros.
Any help would be appreciated. Thank you!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,234
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
check if you can adopt this for your use

Book1
ABC
1123
2Test4Test3Test1
340130310
Sheet1
Cell Formulas
RangeFormula
A3:C3A3=VLOOKUP(A2,INDIRECT("'"&A1&"'!A:B"),2,0)
Cells with Data Validation
CellAllowCriteria
A2:C2List='1'!$A$1:$A$5


Book1
AB
1Test110
2Test220
3Test330
4Test440
5Test550
1


Book1
AB
1Test1110
2Test2120
3Test3130
4Test4140
5Test5150
2


Book1
AB
1Test1310
2Test2320
3Test3330
4Test4340
5Test5350
3
 

Watch MrExcel Video

Forum statistics

Threads
1,119,021
Messages
5,575,614
Members
412,679
Latest member
TSpan
Top