General discussion

Locked

Drop Down Boxes in Excel

By jspain ·
Can anyone help?????
I have three drop down boxes on a spreadsheet that are linked to three tables imported from Access.
In Access these boxes are linked so that I can pick on the first one and dependant items in the second and third boxes are loaded. They are selected in Access by the SELECT DISTINCTROW()statement.
Having now imported the three tabls into Excel how do I now get the dropdown boxes to work the same as in Access.

I hope this is not to to confusing,
A QUICK REPLY WOULD BE MUCH APRECIATED

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Drop Down Boxes in Excel

by Shanghai Sam In reply to Drop Down Boxes in Excel

Assuming 1) your imported tables are not linked back to access AND 2) the drop down boxes are created with Data Validation in Excel.
You will need to re-establish the logic that you built into Access whereby the content of the second list is dependant upon the choice in the first list. The cells used for validation can have formulas so they become dynamic lists
Eg: use a lookup or IF formula in the data validation cells of the dependant list(s).

If DROP DOWN 1 = A or B
Use a Lookup table: (say cell R1C1 to R4C2)
A C1
A C2
B C3
B C4
To only show options available for the choice made in Drop Down 1 - your DROP DOWN 2 VALIDATION CELLS should refer to this lookup table using either the IF formula based on selection in drop down 1 (OR a LOOKUP formula using drop down one as the lookup value).

If user can then go back and change Drop Down List 1 you may need a macro to check/force update of the dependant cells before completing the data entry.

Result -
IF A selected see only C1 and C2 OR
if B selected see only C3 and C4

Note if you have different length lists, Eg: option B requiring a third C5 value, you will need to manage any blanks in the shorter validation list for A - I have not looked into how to do that but may not be an issue is you have some blanks?

You may also have to control the users so they go through the drop down lists in the correct order.
Hope this helps.

Collapse -

Drop Down Boxes in Excel

by jspain In reply to Drop Down Boxes in Excel

Poster rated this answer

Collapse -

Drop Down Boxes in Excel

by jspain In reply to Drop Down Boxes in Excel

This question was closed by the author

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums