Fetch multiple values based on criteria

[Lucero]

Hi, I have this data below as example and underneath the expected data output.
What's needed is a drop down box menu which allows user to choose an Agency and then to automatically populate in separate cells its respective Centers from "CentersList" and its respective IDs from the "IDList".

Is this something that can be done just with regular excel or does it need VBA? This is for my work and have been trying for two days, the drop down box I have shows 4 times the Agency name and I have no idea how to make it only once and for the Centers & its ID's to show in other cells.
I'm not that advanced in excel, but eager to learn. I'd appreciate ANY help anyone can give me! Thank you!


AgencyList

CentersList

IDList



ABC Agency

ABC Center 1

1231



ABC Agency

ABC Center 2

1232



ABC Agency

ABC Center 2

1233



ABC Agency

ABC Center 3

1234



BCC Agency

BCC Center 1

1235



BCC Agency

BCC Center 2

1236



BCC Agency

BCC Center 2

1237



BCC Agency

BCC Center 3

1238



BCC Agency

BCC Center 3

1239



CDD Agency

CDD Center 1

1240



CDD Agency

CDD Center 1

1241



CDD Agency

CDD Center 1

1242



CDD Agency

CDD Center 1

1243



CDD Agency

CDD Center 2

1244



CDD Agency

CDD Center 2

1245





This is how it should look:


Agency Name:

FROM DROP DOWN MENU
ABC Agency





ID:

Center Name:




1231

ABC Center 1




1231

ABC Center 2




1233

ABC Center 3




1234

ABC Center 4





by the way "ABC Center 2" is twice because there are actually two but each have a different ID.

[Admin]

Hi Lucero,

Welcome to ExcelFox !!

Try this http://www.excelfox.com/forum/f12/dependent-data-validation-using-only-formulas-111/?highlight=dependent

[Rick Rothstein]

Except for the position of the columns, it sounds like AutoFilter may produce the lists you want to see. You didn't tell us the version of Excel that you are working on, so we cannot give you specific directions on how to find it, but you would start by selecting the Agency Name column and then turn on AutoFilter. Look for it either in the Data menu item of XL2003 or the Data tab on later versions of Excel.

[Lucero]

Except for the position of the columns, it sounds like AutoFilter may produce the lists you want to see. You didn't tell us the version of Excel that you are working on, so we cannot give you specific directions on how to find it, but you would start by selecting the Agency Name column and then turn on AutoFilter. Look for it either in the Data menu item of XL2003 or the Data tab on later versions of Excel.

Hi Rick, Im using excel 2007. Auto filter wouldn't work because the drop down menu is part of a budget template and the list of data needs to be hidden. Thank you though for your willingness to help me. Take care.

[Lucero]

Dear Admin, This is not working... What it's needed only one Drop Down box for the Agency and as soon as an agency is chosen, the Centers and ID's for that chosen agency should be populated in the cells below like the example. I'm sad I cannot figure it out and I'm not finding the right answer. Thank you though!! Take care :)

[Admin]

Hi,

Assume your data in A1:C16 row 1 being the header

Type the Agency name in G1

In G2 and copied down and across,

=IFERROR(INDEX(B$2:B$16,SMALL(IF($A$2:$A$16=$G$1,R OW(B$2:B$16)-ROW(B$2)+1),ROWS(G$2:G2))),"")

It's an array formula. Confirmed with CTRL + SHIFT + ENTER

[Lucero]

Dear Admin,
Thank you so much for your help!! The array formula is exactly what I needed!!! You Rock!!!

[Lucero]

Thank you so much for your help!! The array formula is exactly what I needed!!! You Rock!!! :D

[Admin]

Thanks for the feedback :cheers: