Filepath not working with Vlookup

[angiemeh]

I have 2 files open. File 1 = Just supplier (there are many, File1A_month1,File1A_month2, File1B_month1,File1B_month2... File1C etc)
File 2 = All suppliers (each supplier on separate tab with month)

I will have to do multiple instances of this for multiple months. When both File 1 & File 2 are open- no problem. When File 1 is closed and File 2 is trying to reference it, I have an issue.File 2 has the Vlookup formula in it. It is the file with each supplier as a separate tab.

I'm using Indirect because I have File 2 with each supplier in a separate tab and I have to reference each to its parent file and month. So if I have tab SupplierA_July, I want a formula that looks up the item # and goes to correct File (matching company & month) and pulls back data in column 8. I then want to copy this formula for SupplierB_july, SupplierC_july etc.....then want to do the same for Aug, and other months. I would have to do so many separate Vlookup formulas for each tab. I thought using indirect, I could use one formula for everything:


Here is my formula:
=VLOOKUP($B9,INDIRECT("["&$C$1&".xls]"&$B$1&"!$B$4:$J$47"),8,"FALSE")
B9= Item #
C1 = tab name (from other file)
B1= File name (from other file)

That translates to:
=Vlookup(Item_number,[File1Name.xls]File1_tabname'!$B$4:$i$47,8,false)
(This one Works)- it looks at Item# in file 2(B9), finds Item# in File 1 and gives me the 8th value from Column B:


This does not work:
VLOOKUP($B9,INDIRECT("'"&"C:\CUSTOMERS\ClientFolder\Categories\Category\Individual Suppliers\"&"["&$C$1&".xls]"&$B$1&"!$B$4:$J$47"),8,"FALSE")

I have over 50 files similar to File 1, they are each an individual supplier. File 2 has all items all suppliers, each supplier in a separate tab. I have a formula in file 2 on each tab that looks up the tab name and month and I want it to vlookup from the File with that tab name. So file 2 may have tab1=Client1_Jan and tab2=Client2_Feb... So I can copy the same formula from tab to tab I want to say vlookup(item#, from client&month file, 8,false)

Any ideas?

 

[stanleydgromjr]

angiemeh,

Try adding the single apostrophe, ', before the !

=VLOOKUP($B9,INDIRECT("'"&"C:\CUSTOMERS\ClientFolder\Categories\Category\Individual Suppliers\"&"["&$C$1&".xls]"&$B$1&"'!$B$4:$J$47"),8,"FALSE")


Have a great day,
Stan

 

[angiemeh]

I did this, but it didn't work. The more I'm reading, Indirect has an issue with a closed workbook. I found a website that has a morefunction add-in called Indirect.exe and I installed this.

I can't really do a Vlookup with it (haven't figured that part out) but referencing a closed workbook seems to work. I think this will be a workaround for now

I'm in 2003, does anyone know if this is fixed in 07?

Thanks!

 

[RoryA]

The behaviour is the same in 2007.
Just as a warning, I have had some issues with the INDIRECT.EXT function returning incorrect values in the past.

 

[angiemeh]

Is there any other solution out there?

 

[angiemeh]

I'm a little scared if Indirect.exe pulls incorrect values, I'm working on something and we can't make a mistake on it. Thanks for the warning!