selecting a single dropdown option produces a full list of linked items

[splreece]

Hi all,

I am looking to create a dropdown list on the excel sheet (avoiding userform if possible).

The list will hold office locations (8-10)


Is it possible to have something where if the user selects "office location 1", then on a prefined cell, any team associated with office location 1 is reproduced from that cell.

This is more database thinking, but ideally I need to know how I would link say

office 1
teama
teamb
teamc
teamd
office 2
teame
teamf
teamg
teamh


etc.etc..

If this link is possible, then the user selects "office 2" from cell q14 and automatically the vba or calculation fills cell j19 to j23 with
"teame"
"teamf"
"teamg"
"teamh"

I am unsure how to do this in excel on a flat excel sheet, but ideally I need to do this via named ranges/lists so the user has full control over what he/she sees.

many thanks ahead of time.

 

[Eric W]

Here's one way to set that up:

	A	B	C	D	E	F	G	H
1	Office 1	Office 2	Office 3	Office 4			Office 3	jessica
2	ann	doug	frank	kent
3	bob	elaine	gail	linda
4	cindy		ike	mike
5			jessica
6

<tbody>
</tbody>

Sheet11​

Put a list of the offices and staff in A:D as shown. You can change the names in the lists as you desire, even add a new column to the right if you want.

Then select your office cell (q14 in your message, G1 in my example) and from the Data tab, click Data Validation > Allow: List > Source: =A1:D1 > OK. Now you have a drop down list of offices.

If you want a drop down list of names, select another cell (r14 maybe, H1 in my example) and follow the same steps, using this for the Source:
=OFFSET($A$1,1,MATCH(G1,A1:D1,0)-1,COUNTA(OFFSET($A$1,1,MATCH(G1,A1:D1,0)-1,100,1)))

If you actually want J19 to J23 to fill with all the names, you can select J19:J23, enter that same formula (with all 5 cells still selected), and press Control+Shift+Enter.
You can also use:
=OFFSET($A$1,1,MATCH(G1,A1:D1,0)-1,5,1)&""
which will avoid the #N/A on the end if the list is shorter.

There are other options, but let me know if this is on the right track for you.