need drop down trick

[chetanmehra1989]

hello frd


can you help me to sort out this small matter



this is my simple question

Area Chetan Amit raj mehra
New Delhi 1 1 1 1
Old Delhi 1 1 1
Uttam Nagar 1 1
Tilak Nagar 1
Janak puri 1 1 1 1

i want simple drop down
for example
1. if i select chetan then only show in drop down new delhi, old delhi, and janak puri (not showing uttam nagar and tilak nagar)
2. if i select amit then only show in drop down new delhi old delhi, uttam nagar, tilak nagar and janak puri
and future if chetan want to see the uttam nagar (if i selected 1 in chetan at uttam nagar ) than show in drop down list


can anybody tell me the trick or something tips in drop down

[jason.b75]

The closest thing possible to what you want is demonstrated here

http://www.contextures.com/xlDataVal02.html

[chetanmehra1989]

thanks for the link but i want remove blank data
if i have that type of data

chetan
blank
blank
blank
amit


i want any indirect types of formulas

pls help me

[jason.b75]

In K10, note that this is an array formula and must be confirmed with Shift Ctrl Enter

Please Login or Register  to view this content.

Use the fill handle to copy the formula right to N10, then down to N14.

Create the validation list for G14 using

Please Login or Register  to view this content.

[chetanmehra1989]

thank for the formulas but its showing error after 1st step

can u use the formula in excel sheet
i already uploaded one excel sheet.
can u do it as soon as possible


thanks & regards
Chetan

[jason.b75]

thank for the formulas but its showing error after 1st step

What error, and where?

Did you make any changes to the formula?

Did you remember to array confirm the formula?
I used your sheet from post #1 to test the formula before posting it and there were no errors.

[chetanmehra1989]

can u send me with attachment

[jason.b75]

My guess is that you didn't array confirm the formula which would have resulted in #NUM! errors.

When someone trying to help you asks you some simple questions in an effort to solve your problem it would be considered courteous to make a similar effort to answer those questions and maybe try to learn something, not to just ask for a completed attachment so you don't have to make the effort.