Indirect Syntax - Outside the workbook

[itsrich]

I am pullling data from multiple workbooks with multiple tabs. The file folder, file name, and tab all change, but the data is in the same place on all the spread sheets.

If I was on one tab I would use XLOOKUP(B5,'S:\2025 Purchasing\2.SPREADSHEETS\[Cycle Count 1 (Floor) 2025.xlsm]360 Products '!$D:$D,'S:\2025 Purchasing\2.SPREADSHEETS\[Cycle Count 1 (Floor) 2025.xlsm]360 Products '!$G:$G,"")

So I am choosing to use INDIRECT("'[&file name&".xlsm]"&tab name&"'!cell name")

Assume these are in column X:Z and rows 1:2

File Folder	File	Tab
S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	360 Products

Replacing the green text with INDIRECT("'CONCAT(X2,'\[',Y2,".xlsm]")'"&Z2&"'!$D:$D)

XLOOKUP(B5,'INDIRECT("'CONCAT(X2,'\[',Y2,".xlsm]")'"&Z2&"'!$D:$D),'S:\2025 Purchasing\2.SPREADSHEETS\[Cycle Count 1 (Floor) 2025.xlsm]360 Products '!$G:$G,"")

Excel sites an error and highlights this part of the formula "\[". The " and ' mix me up. I think I am close.

Thank you for your help.

 

[jkpieterse]

Your formula should read:

XLOOKUP(B5,"'"&X2&"\["&Y2&"]"&Z2&"'!D:D","'"&X2&"\["&Y2&"]"&Z2&"'!G:G","")

Even if this is correct, for this to work the workbooks in question must be open in Excel

 

[itsrich]

You are correct, it only works when both workbooks are open. I am collecting data from 15 spreadsheets across 160 tabs.

One XLookUp with two similar INDIRECT functions allow me to use one formula to reach all the info.

My issue is getting the syntax of the formula correct. (Is Syntax the correct term here)

XLOOKUP(B5,"'"&X2&"\["&Y2&"]"&Z2&"'!D:D","'"&X2&"\["&Y2&"]"&Z2&"'!G:G","") works, BUT "&X2&"\["&Y2&"]"&Z2&" returns text. XLookUp does not recognize text. INDIRECT makes text formula friendly.

 

[RoryA]

You don't want the CONCAT inside the quotes as you had it originally.

=XLOOKUP(B5,INDIRECT("'"&X2&"\["&Y2&".xlsm]"&Z2&"'!$D:$D"),'S:\2025 Purchasing\2.SPREADSHEETS\[Cycle Count 1 (Floor) 2025.xlsm]360 Products '!$G:$G,"")

 

[jkpieterse]

I apologize, I was so much focussed on getting the text concatenation right that I forgot to include the INDIRECT function in my example

 

[itsrich]

I should have done this from the start (I didn't think work would let me install Xl2bb).

Goal, develop a formula that will using the info in Master - rework 2024.09.10.xlsx to find the price in Cycle Count 1 (Floor) 2025.xlsm

• Column B is the the search criteria
• Colunm C, D, and E are the locations
  • Multiple File folders in column C (this may not be usable, INDIRECT may not be able to use this)
  • Multiple Files in column D
  • Multiple Tabs/Sheets in column E (I only supplied one TAB)
  • I am collecting data from 15 spreadsheets across 160 tabs in multiple directories
• The results will be in column F

You can see the formula in F5 works. I am avoiding using this because I don't want to code that many formulas.

I have been attempting to make INDIRECT do the work, but I can't figure it out.

I think I am stuck on the signle and double quotes. I am so close, TY for your help!

Master - rework 2024.09.10.xlsx
	A	B	C	D	E	F
2	FR #	Vendor PN	File Folder	File	Tab	Vendor ID Cycle Count 1 (floor) 2025
3	F100309670	DT-SUPPORT	Unkown	Unkown	Unkown
4	F100270595	540G2	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	360 Products
5	F100503650	45210765	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025.xlsx	ADORN	23.21
6	F100405034	45210772	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	ADORN	#REF!
7	F100381331	2259-180	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	BOYD INDIANA
8	F100240548	2259-96	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	BOYD INDIANA
9	F100066852	3840RBK	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	D & W
10	F100424556	3940BK	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	D & W
11	F100349255	3U115	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	H.B FULLER
12	F100318239	3U316	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	H.B FULLER
13	F100349578	AERO 5324P	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	HAHN SYSTEMS, LLC
14	F100358023	AEROSMITH 5323HPST	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	HAHN SYSTEMS, LLC
Sheet4 (2)

Cell Formulas
Range	Formula
F5	F5	=XLOOKUP(B5,'[Cycle Count 1 (Floor) 2025.xlsm]ADORN'!$D:$D,'[Cycle Count 1 (Floor) 2025.xlsm]ADORN'!$F:$F)
F6	F6	=XLOOKUP(B6,INDIRECT("'["&J6&".xlsm]"&K6&"'!$D:$D"),INDIRECT("'["&J6&".xlsm]"&K6&"'!$F:$F,"""))

Cycle Count 1 (Floor) 2025.xlsm
	A	B	C	D	E	F	G
1		QTY ORD'D	F100 #	PART #	DESCRIPTION	PRICE	EXT. PRICE
2			F100275851	45216818	MP 1/8X48X96 LONDON ASH 45G LAUAN	$ 18.02	$ -
3			F100275814	45217324	MP 1/8X48X108 LONDON ASH 45G LAUAN G2S	$ 29.62	$ -
4			F100275850	45216819	MP 1/8X48X96 AMERICANO 45G LAUAN	$ 16.63	$ -
5			F100275813	45217330	MP 1/8X48X108 AMERICANO 45G LAUAN G2S	$ 29.63	$ -
6			F100413747	45210798	MP 1/8X48X96 TARTAN FOSSIL .004 LAUAN	$ 17.67	$ -
7			F100405034	45210772	MP 1/8X48X96 MONET RIDGE EXPRESS .004 LAUAN	$ 18.73	$ -
8			F100410861	45210774	MP 1/8X48X108 MONET RIDGE EMPRESS .004 LAUAN L1S -	$ 28.22	$ -
9			F100413745	45210795	MP 5/32X47-3/4X100 - MALONEY MIST LAUAN *CEILING* PHIL	$ 28.46	$ -
10			F100416218	45211256	MP 5/32X48X 96 x 2.7- MALONEY MIST LAUAN *CEILING* DUANE	$ 22.27	$ -
11			F100413746	45210796	MP 2.7MMX48X116 MONET RIDGE EMPRESS .004 AZDEL PUR	$ 27.04	$ -
12			F100503650	45210765	2.7MM 48" X 83.875" MONET RIDGE EMPRESS AZDEL	$ 23.21	$ -
13			F100503651	45212045	MP 2.7MMX48X119 MONET RIDGE EMPRESS .004 AZDEL L1S	$ 28.62	$ -
14			F100503652	45212044	MP 2.7MMX48X114 MONET RIDGE EMPRESS .004 AZDEL L1S 9	$ 26.87	$ -
ADORN (2)

 

[jkpieterse]

If J6 already contains the extension .xlsm, there is no need to add that in the formula. But your table doesn't show column J so we have no way of knowing

=XLOOKUP(B6,INDIRECT("'["&J6&"]"&K6&"'!$D:$D"),INDIRECT("'["&J6&"]"&K6&"'!$F:$F,"""))

 

[itsrich]

Jan Karel, Thank you. Thank you on two levels! First, this formula works! Second, for pointing out how to improve!

I missed the file extension! I may have had the formula correct in my efforts, but I had not even considered I need to add the file extension to the formula or the data!

Thank you for pointing out the file is a .xlsm and not .xlsx.

I need to correct the formula

=XLOOKUP(B6,INDIRECT("'["&J6&"]"&K6&"'!$D:$D"),INDIRECT("'["&J6&"]"&K6&"'!$F:$F"),"")

• The INDIRECT part of the formula (Jan Karel's part!) is correct
• Compare the 3rd part of the XLookUp and you see the difference.

I have finished the formula a 3rd XLookUp statement

=XLOOKUP(B39,INDIRECT("'["&J39&"]"&K39&"'!$D:$D"),INDIRECT("'["&J39&"]"&K39&"'!$F:$F"),CONCAT("No price on ",K39))

Again, thank you Jan Karel!