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?
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?