2 formulas needed for data validation dropdown lists

**jc786** · 12-31-2011, 07:23 AM

Hi
I have a large complicated db but for the sake of simplicity i have attached a sample worksheet based on the structure of my original.
Please note you are not alowed to make ANY AMMENDMENTS to sheet 1. It can ONLY be used as a source of data.

I SIMPLY NEED THE 2 FORMULAS that go in cell F4 and G4 in sheet 2. HOWEVER please note that these cells should be dropdown lists so if in the cell F4 you choose MEAT the cell G4 should have dropdown list of mutton or chicken.

To help you along i figured out that cell A2 lists only those values in col B that have the corrsponding value "CATEGORY" in col c...easy.

Similarly the formula for cell G4 would list all the values from col B if the code in col D matches the code in col A for the chosen item in cell F4....not so easy.

I hope someone can write these 2 simple formulas bearing in mind that you cannot change the layout of the database sheet.
Thanks a million.

**Palmetto** · 12-31-2011, 09:21 AM

Suggest you have a read here: Excel Data Validation -- Create Dependent Lists

**Fotis1991** · 12-31-2011, 10:03 AM

Hi

You need named lists and indirect functions.

Take a look to the attachement

Hope to helps you.

***ooops!! Palmetto. Sorry i haven't see your answer..
Happy New Year!!

**jc786** · 01-01-2012, 06:08 AM

Hi Fotis
Thank you for your reply but i am working under specific parameters which means i cannot ammend my db or create lists etc. I can only use the db as a source of data in a seperate sheet to the sheet that i want to extract the dropdown lists onto. The reason for this is that i download the huge database every 3 or 4 days into a new sheet and then i want to see just a small part of that data in the 2 dropdown lists i mentioned.

So that i can get some help from this forum i have recreated part of the db layout in the example db i attached. I have to use this layout and cannot create lists from it.

Can anyone at least provide the formula for the first cell which in non-technical language is: cell A2= lists only those values in col B IF corrsponding value = "CATEGORY" in col c

Thanks