Drop down list data

[Cobra91]

Hello all,

I am currently stuck with a drop down list problem. I am trying to make a benchmarking solution for a group of 20 stores. All stores sell products uniformly and their sales are displayed in percentages of the cumulative total. Currently there is a benchmarking page where all stores sales per product is displayed, however I would like the ability to select specific stores data from a drop down list. I have pictures displaying the basic concept of what I want to achieve.
stores.PNG
On the left a list of products and the quantities sold in percentages per store for benchmarking purposes.
What I would like to achieve relates to the drop down list on sheet 2.
store 1.PNG
Here store 1 can see its sales in percentages, and has a drop down list to compare to different stores. If store 3 is selected I would like the data to be pulled into that column from the benchmarking sheet and the same goes for the other stores of course. I dont know how to do this, any help would be much appreciated.

Regards

[The_Snook]

Hi Cobra91,

Take a look at the attached example it should cover what you need.

Regards,

Snook

[Cobra91]

Hi Snook,

Thank you for the reply. I am using Excel 07 and cant see the drop down lists, however when I change the names to store 3 or 4, it displays the correct data. The formula displayed when I click on a product is very long and complex. I am not at all familiar with this. Is there a tutorial or easier way to do this step by step?

Cobra

[The_Snook]

Hi Cobra91,

Does the attached alternative solution using HLOOKUP make it any easier?

Regards,

Snook

[Cobra91]

Thanks again Snook. It's a bit clearer. But I cant seem to replicate that formula. Do you fill it in each cell individually? How do you like it up to the dropdown menu or do you fill the formula in the box that holds the drop down formula? I'm still a bit lost, sorry.

Regards

[The_Snook]

Hi Cobra91,

Essentially the four parts to the formula are as follows (for the HLOOKUP):

1. What criteria are you looking for?
2. Where are you looking for it?
3. When you find it how many rows down do you want the formula to go to retrieve the required data?
4. Is it an approximate or exact match you require? (FALSE = exact and TRUE = approximate)

Hopefully this makes things a bit clearer?

Snook

[Cobra91]

Hi Snoop,

Thanks once more. That does make some sense, however do you fill the formula into the cell holding that displays the drop down list, or the cells with data? I assume the dropdown list? And you would repeat the steps per segment of the list?

Cobra

[The_Snook]

Hi Cobra,

Apologies, I don't really understand what you're asking?

Regards,

Snook