Indirect Formula

[lizemi]

Hi
I have a workbook with sheets named Week 1 - Week 52 (52 Sheets)
On a different workbook I need to vlookup from my weekly sheets to create a summary
I am trying the indirect formula to tell excel what week nr sheet to use so that I can use one formula for all 52 weeks on my summary, but it is giving me an error.

Basically I whant to tell excel that the sheet nr is b3,c3,,d3,e3 etc

 

[etaf]

you are looking up another workbook ?
also indirect uses the & to add
so maybe
vlookup($a4,indirect("filename"&B3&"range"),2,false)
but you need to make sure you include the ' and the !

in a simple example using a sheet
=VLOOKUP(A4,INDIRECT(B3&"!$A$1:$B$10"),2,FALSE)

summary with Indirect on

Book11
	A	B	C
1
2
3		sheet2	sheet3
4	a	sheet2a-1	sheet3a-1
5	b	sheet2a-2	sheet3a-2
6	c	sheet2a-3	sheet3a-3
Sheet1

Cell Formulas
Range	Formula
B4:C6	B4	=VLOOKUP($A4,INDIRECT(B$3&"!$A$1:$B$10"),2,FALSE)

sheet2

Book11
	A	B
1
2	a	sheet2a-1
3	b	sheet2a-2
4	c	sheet2a-3
Sheet2

Sheet3

Book11
	A	B
1
2
3	a	sheet3a-1
4	b	sheet3a-2
5	c	sheet3a-3
Sheet3

 

[bebo021999]

Basicly:
=VLOOKUP($A4,'Week 40'!$A:$B,2,0)

Same result, put the range inside INDIRECT:
=VLOOKUP($A4,INDIRECT("'Week 40'!$A:$B"),2,0)

with B3="Week 40", split the INDIRECT into 3 parts, wrap anything with "":
INDIRECT("'" & B$3 & "'!$A:$B")

final formula:

=VLOOKUP($A4,INDIRECT("'" & B$3 & "'!$A:$B"),2,0)

 

[Alex Blakenburg]

You can't use indirect on a closed workbook it will give a #REF! error.
Some options are:
• Use VBA to change the formula to the correct sheet name
• Pull in the data for all worksheets into the current workbook using Power Query and use the imported data in your Vlookup

 

[lizemi]

Hi Sorry I still don't seem to get it to work.

If I use
=VLOOKUP($A2,INDIRECT("'C:\Users\Lize\Desktop\[Test Week.xlsx]"&B1&"'!$A:$B"),2,FALSE)
It gives me a #REF error

=VLOOKUP($A2,'C:\Users\Lize\Desktop\[Test Week.xlsx]Week 40'!$A:$B,2,FALSE) is the formula that works but without the indirect obviously

 

[etaf]

is the other workbook "test week.xlsx " - OPEN

as mentioned needs to be open

B1 - you have B3 in the image not B1