Excel Worksheet reference in formula

0
votes

I'm currently working on a workbook which has a 52 worksheets, 1 for week of the year. I'm trying to make a worksheet that i can use as a quick-check for each of the worksheets. I'm wondering if there's a way of having just 1 worksheet pull data in from each of the other sheets ? or do i have to create one for every week individually ?

For example,

instead of individual sheets with a load of '=VLOOKUP(B3,'WEEK 12'!$A$4:$E$40,3,FALSE)' is there any i can have the formula use whichever worksheet name i have in say cell c1 ? like '=VLOOKUP(B3,'Cell C1'!$A$4:$E$40,3,FALSE)'

Sorry for being a bit long winded, and hope you understand what i mean.

2
excelexcel-formula
Google indirect() Excel. To hard to write a complete answer on my phone – LJ01
Thanks, will have a go at using it in my vlookup formulas – Brian Davies
What am i doing wrong with the formula... =VLOOKUP('Lookup'!B3, INDIRECT("'" & $C$1 &"')!$A$4:$E$40,3,FALSE) – Brian Davies
It depends on what you have in c1, but I think you need: =VLOOKUP(B3,INDIRECT("'"&C1&"'!A4:E20"),3,FALSE) where your contents of c1 has something like this: WEEK 12 – FocusWiz
Assuming that "WEEK 12" is in C1, here is what is wrong with your formula: 1. There is no close quote after the second open quote. 2. It looks like you want to enclose only the $C$1 inside the parentheses. If you do that the indirect cell reference will be incomplete, only referencing the sheet name. So, after the 2nd & (and before the comma) you want the following (including the double and single quotes): "'!$A$4:$E$40") As mentioned elsewhere, you don't need the $ signs since these references are pure text fields and will not change nor automatically be updated if the cell is moved. – FocusWiz

2 Answers

2
votes

As you are now using a text representation of columns A and E, there is absolutely no need for the $ markers.

=vlookup(b3, indirect(text(c1, "'@'!\A\:\E")), 3, false)

Alternately, with just 12 in C1.

=vlookup(b3, indirect(text(c1, "'w\e\ek 0'!\A\:\E")), 3, false)
1
votes

For example, where C1 might contain WEEK 12,

=VLOOKUP(B3,INDIRECT("'"&C1&"'!$A$4:$E$40"),3,FALSE)