Index Match or Vlookup with reference to a tab name in a different file

[ELFIJKA]

Index Match or Vlookup with reference to a tab name in a different file

I have 2 files:
- Price List file contains many Tabs - One Tab is one customer pricelist, with products listed down (they all are identical, only different prices)
- Summary Book - is a 1 mass table with all customer Pricelists on one page. Again Products listed down but all customers listed across.

I'm looking for a formula for Summary page that I can pull across and don't need to reference to each tab in Price Lists separately.

I've attached picture and 2 sample files, hope you can help!!! I found solution with INDIRECT formula if all data in located in one file, but I really need to keep them separately.

[AliGW]

10 is not an Office version - which version do you have? Please update your forum profile. Thanks.

[Pete_UK]

You would need to use INDIRECT if you wanted to pull the sheet name from cell B1, but you need to be aware that INDIRECT does not work with closed files, so you would need to ensure that the source file (PriceLists.xlsx) is open at the same time that your Summary file is open.

Hope this helps.

Pete

[ELFIJKA]

You would need to use INDIRECT if you wanted to pull the sheet name from cell B1, but you need to be aware that INDIRECT does not work with closed files, so you would need to ensure that the source file (PriceLists.xlsx) is open at the same time that your Summary file is open.

Hope this helps.

Pete

Ok i have tried and tried but i can't make up a formula, so that it works. Can you please help?

[Pete_UK]

You can use this formula in B3 in the Summary file:

=VLOOKUP($A3,INDIRECT("'[Price Lists.xlsx]"&B$1&"'!$A:$B"),2,0)

then copy across and down.

Hope this helps.

Pete