asked on

Dynamic Dependent Data Validation

Attached is a workbook where I need some assistance with Data Validation. On the first sheet, Change Status, F7, I would like a Data Validation list to appear based upon the list selection from Column D (D7). The list would be compiled according to the entries from the Dropdown tab (Sender).

I'm not concerned with any other Column...just F.
Validation-List.xlsx

Rey Obrero (Capricorn1)

with the selected item in D7 BLDG 104, wha t do you expect the validation list for F7? CAMP1040?

Cook09

ASKER

Bldg 104 MAIL, yes....CAMP1040
Bldg 102 MAIL 10AM
7400-010
7400-012
7400-013
7400-016
7400-019
7400-01A
7400-01B
7400-01C
7400-01F
7400-01G
7400-01H
7400-01J
7400-01M
7400-01P
7400-01R
7400-01V
7400-01X
7400-01Z
7400-021
7400-022
7400-023
7400-025
7400-027
7400-028
7400-029
7400-02A
7400-02B
7400-02D
7400-02F
7400-02H
7400-02L
7400-02P
7400-02R

Rey Obrero (Capricorn1)

test this
Validation-List.xlsm

Cook09

ASKER

I had some issues with opening the workbook...it tells me there is an issue with some unreadable content..../xl/worksheets/sheet1.xml part.
I can work on it more tomorrow. However, is there a non-vba solution? Just curious...no one else here understands it all and would like to share.

Cook09

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

You can do this with a Dynamic named range.

Create a named range using the following formula and call it for example SENDERS:

=OFFSET(SAMACS[[#Headers],[Sender]],MATCH('Change Status'!$D$7,SAMACS[[#All],[Route]],0),0,COUNTIF(SAMACS[[#All],[Route]],'Change Status'!$D$7),1)

In F7 then use a Data Validation list and make the source =SENDERS

Thanks
Rob H

Rob Henson

One possible downside, when the value in D7 changes the value in F7 stays and may not be valid.

How do you want to handle this? You could automatically delete the enhtry from F7 when D7 changes or you could just highlight it so that the user can see that it is invalid.

Thanks
Rob H

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cook09

ASKER

Sorry I have not responded today...some urgent meetings came up...will look first thing tomorrow...

Cook09

Cook09

ASKER

Shums formula was exactly what I needed for this situation...but appreciate the VBA solution as well.

Thanks for your assistance and sorry about the delay...

Shums Faruk

You'r Welcome Cook09! Glad I was able to help.

Rob Henson

So did you try my suggestion?

Virtually same as Shums' suggestion but not even any assistance recognition.