Lookup against multiple external tabs

[BCoke]

Hi Guys,

I have a table, which I can't draw so I will explain!

I have several columns with headings such as "BOGOF" and "2F£2" etc. Down the side, I have several products such as "Cans" and "500ml".

I am looking to extract data from an external file, problem being all the various packs have a different tab in the external file. Ie. tab for Cans and a tab for 500ml and so on.

The good thing is that all the tabs in the external file are the exact same template and I have taken the exact same headings and pack names (so should be able to look up).

I need to derive a formula which would look the pack up against the tab name (or in cell C4, where it is repeated) and then perform a Hlookup against row 11 and below (into a table) to pick out the correct data.

Is this possible? So my formula would 1) lookup against tab name or cell C4 of every tab to match to the product in my table and 2) perform a Hlookup in the correct tab to bring back the volume from row 11.

Can I please get some help with this?!

Thanks so much in advance!!

[6StringJazzer]

This should be possible using the INDIRECT function if you can have the external file open when you need to use the first file. If the external file is not open INDIRECT will return an error.

I can provide more detail if you can attach both files to see the data. In your description you start talking about "packs" without explaining what those are or where they are located. I'm not clear on the relationships between the two files.

[BCoke]

Hi 6StringJazzer,

Sorry for the duplicate request and for not attaching the file.

I have attached a couple of examples without the correct formatting, but everything is in the same place.

Essentially, I am looking to complete my table, such as the one in "Example 1", by looking up against a file with the layout such as "Example 2". I would look up the £2 mechanic in my table, for example in row 11 and bring back the volume from row 14, provided the tab is the same as the product I am look at.

Does this make sense?

Let me know if there is anything else you need from me, hope I've attached the files correctly!

[6StringJazzer]

Where do the tab names come into play? Your example just has one tab, generically called Sheet1.

[BCoke]

Hi 6StringJazzer,

Sorry, that sheet 1 would be called "Cans" for example and there are several of those tabs.

[6StringJazzer]

I have put in a formula that will look up the Gross Volume in row 14. You have "Mechanic" in row 11, not any kind of volume. You have at least five different rows with some sort of volume so I just picked one.

[delete following text--I figured out before attaching the files that the lookup value for these columns was lower down You also have 10 columns of data, and I don't know which one you need to look up.]

There is only one tab, Cans, so the other two references to non-existent tabs cause errors.

Some of your categories are missing, so those cause errors.

[BCoke]

Thanks 6StringJazzer

[6StringJazzer]

You're welcome, let me know if you need help adapting this to your full files.