InDirect Formula HELP

[ItalianPlatinum]

Hello - having some trouble putting an indirect formula together to pull my formulas. Case scenario is my A2 could change so I need it to reference off a cell. But my B2:C2 i want to set them as fixed in the path. I keep getting the below error, is there something I am doing wrong?

Book2
	A	B	C
1	Workbook Path	Fixed File Name	Cell Range
2	C:\Users\Marco\Desktop\	VBA TESTING.xlsm	J:L
3
4	#NAME?
5
6	Desired:	[VBA TESTING.xlsm]Sheet1'!$J:$L
Sheet1

Cell Formulas
Range	Formula
A4	A4	=INDIRECT("'["&A2&VBA Testing.xlsm&"]"&"Sheet1"&"'!"&"J:L")

 

[jasonb75]

Try

=INDIRECT("'["&A2&B2&"]Sheet1'!"&C2)

It should return a #VALUE! error because you're trying to return 2 entire columns to a single cell. If you want to check that INDIRECT is returning the correct range, the easiest way is to test it without the INDIRECT() part, for example

="'["&A2&B2&"]Sheet1'!"&C2

 

[ItalianPlatinum]

Try

=INDIRECT("'["&A2&B2&"]Sheet1'!"&C2)

It should return a #VALUE! error because you're trying to return 2 entire columns to a single cell. If you want to check that INDIRECT is returning the correct range, the easiest way is to test it without the INDIRECT() part, for example

="'["&A2&B2&"]Sheet1'!"&C2

This is my issue to clarify trying to do a Vlookup, encompassing the ask above. getting REF#, also am i missing brackets? See below:

Book2
	A	B	C
1	Workbook Path	Fixed File Name	Cell Range
2	C:\Users\Marco\Desktop\	VBA TESTING.xlsm	J:K
3
4	INDIRECT FORMULA	#REF!	Vlookup result
5			4
6	TEST	'[C:\Users\Marco\Desktop\VBA TESTING.xlsm]Sheet1'!J:K
Sheet1

Cell Formulas
Range	Formula
B4	B4	=INDIRECT("'["&A2&B2&"]Sheet1'!"&C2)
C5	C5	=VLOOKUP(E2,'C:\Users\Marco\Desktop\[VBA TESTING.xlsm]Sheet1'!$J:$K,2,FALSE)
B6	B6	="'["&A2&B2&"]Sheet1'!"&C2

 

[jasonb75]

oops, the bracket wasn't missing, I just had it in the wrong place, it should be =VLOOKUP(E2,INDIRECT("'"&A2&"["&B2&"]Sheet1'!"&C2),2,FALSE)

Although, =VLOOKUP(E2,INDIRECT("'["&B2&"]Sheet1'!"&C2),2,FALSE) should be adequate.

When using INDIRECT, the workbook that the data is being pulled from must be open.

 

[ItalianPlatinum]

oops, the bracket wasn't missing, I just had it in the wrong place, it should be =VLOOKUP(E2,INDIRECT("'"&A2&"["&B2&"]Sheet1'!"&C2),2,FALSE)

Although, =VLOOKUP(E2,INDIRECT("'["&B2&"]Sheet1'!"&C2),2,FALSE) should be adequate.

When using INDIRECT, the workbook that the data is being pulled from must be open.

oh darn. that could be a problem. hmm. the above does work but as you said yes it does have to be open