drop down list + filtering

[jermz]

hi sirs i just wanna ask how to go over this. im kinda playing with the drop down list and i decided to make a list about the nba teams.

heres the breakdown:

1st row
conference: east/west

2nd row
east: ATLANTIC/CENTRAL/SOUTHEAST
west: SOUTHWEST/NORTHWEST/PACIFIC

3rd row
the teams list is next.


now ive learned how to do a drop down list but i cant find a way to filter the 2nd row. i hope you can gimme some tips or ways on how to go over this. i tried data>filter but thats not what i need.

here is an attachment and i used sheet 1 for the input and sheet 2 for the list of the selections.

thanks

[NBVC]

You are almost there... you had the intuition to create named ranges based on the selections which is what you need to do... Great!

So now, in C6, you create another Data Validation List and enter the formula =Indirect($B6)

In D6, do the same and enter formula =Indirect($C6)

Then you can copy|Paste Special >> Validation over the remaining cells.

[jermz]

You are almost there... you had the intuition to create named ranges based on the selections which is what you need to do... Great!

So now, in C6, you create another Data Validation List and enter the formula =Indirect($B6)

In D6, do the same and enter formula =Indirect($C6)

Then you can copy|Paste Special >> Validation over the remaining cells.

wow thats really great thanks! this is freakin awesome

[NBVC]

You are welcome.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[jermz]

uhm i have 1 more thing that i needed to add. its a 4th row and its a win status. if i select a team it will automatically show the number of wins in E. now what i did on sheet 2 is that i added numbers in the cells beside the pacific teams just to test it:

WARRIORS 12
CLIPPERS 11
LAKERS 9
SUNS 7
KINGS 6

i guess i need to use IF or lookup or match but how do i do it using the list on sheet 2?

heres another attachment

thanks!

[NBVC]

Since you named that range "pacific1", you can use that in a Vlookup

try:
=VLOOKUP(D5,pacific1,2,FALSE)

or if you intend to always match up with what's in Column C and have a named range for each with a Win column... then

=VLOOKUP(D5,INDIRECT(C5&1),2,FALSE)

would look at C5 and add a 1 to the end of it and then look for that combined named range for the lookup items..

[NBVC]

or if you don't want to create new named ranges and just want to insert a win column between each Region... then you can use something like

=OFFSET(INDIRECT(C5),MATCH(D5,INDIRECT(C5),0)-1,1,1,1)

which uses your original named ranges and looks to the column to the right for values to bring back

[jermz]

or if you don't want to create new named ranges and just want to insert a win column between each Region... then you can use something like

=OFFSET(INDIRECT(C5),MATCH(D5,INDIRECT(C5),0)-1,1,1,1)

which uses your original named ranges and looks to the column to the right for values to bring back

whoa this is what ive been looking for sir. i did place the wins per team to the right and it worked really well.

thanks again sir. will close this thread now.