From one drop down to another

I have a sheet with 3 drop down boxes on it. I want to be able to select a choice in drop down 1 and then have drop down 2 populated with choices based off of what the user selects in drop down 1, then after the user makes a choice in drop down 2, a number between 15 and 30 in drop down 3 is selected and a cell is populated with info based off of all three drop downs. Sound confusing? Here is an example...We will say drop down 1 has the choices of blank, HNL, OGG and ITO. If the user picks HNL then drop down 2 gives the choices 4L, 4R, 8L. If OGG is picked then drop down 2 lists 5, 23. So we decide to pick HNL, 4L and then 15. When this is done I want the combination of all three choices to wind up choosing a paticular value that I will have stored in a table of cells. So maybe HNL, 4L and 15 = 522 and HNL, 4L and 16 = 519 etc.

So far I have drop down 1 working with my choices and then it outputs to cell E10. I'm thinking if I can get drop down 2 to read E10 and then based off of whether it says 1, 2, 3 or 4 it chooses a different input range of say nothing (blank), G3:J3, G4:H4 or G5:H5 to use as its choices. Any idea's?

Once that's worked out the simplest way I can think of to setup drop down 3 is read the choice in drop down 2 by outputing again to a different cell(probably a different cell for each selection in drop down 1), and combine it with the number in drop down 3 and then retrieve data at a hard coded table.

So can anyone give me some advice on how to tackle this? Thanks in advance!

D

try this formula in b4

=VLOOKUP(B3,E15:L34,MATCH(B1&" "&B2,E14:L14,0))

hth

let me know how you go

Originally Posted by peejay

try this formula in b4



hth

let me know how you go

That is exactly what I was looking for..Thank you very much to both of you. This brings up 2 new questions though.

First, let's say we pick HNL, 4L and 16. B4 fills with what we want. But suppose we go back to drop down 1 and change it to OGG. Drop down 2 stays 4L even though that isn't an option. Is there a simple function to auto clear drop down 2 when drop down 1 changes? Or better yet, upon selection of drop down 1 can we write something to automatically fill drop down 2 with the first string in its list?

Secondly, I would like to transfer this to pocket excel but drop down boxes aren't supported. Is there a similar function etc that works in pocket excel that I can use? Or maybe a free add-on to download etc?

Thanks again!

enter the following event code into the VBA editor for sheet1 worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Range("$B$2").ClearContents
End If
End Sub

hth
let me know how you go

(sorry I have no idea about the Pocket Excel - Hopefully one of the other's will have some idea)