Question to Validation list

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
Hi,
Is there a way to adjust the validation list of the cells programmably?

For instance, depending on the value of A column, the validation list on B column would be adjusted, during editing the sheet. If the value on Column A is "Servers (IBM & HP):", then B column would only have the validation list like

HP-SRV-001
HP-SRV-002
HP-SRV-003

If the value on Column A is "Laptops", then B column would only have the validation list like

HP-LAP-031
HP-LAP-032

How?

tt505.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Group Finance Manager
Commented:
You can do this with Named Ranges andthe Indirect Function

I have Data Validation in A1 of sheet2 to pick from using a Named Range called List. There are two other Named Ranges - Laptops and Servers. Select in A1 and the Data Validation in B1 is populated using the formula INDIRECT(A1)

Use the Name Manager to view and create  the Named Ranges. Look at the Dta Validation in the cells to see the references
EE--Cascading-Validation.xlsx
HuaMin ChenProblem resolver

Author

Commented:
Many thanks Roy.
Where can I edit the relevant list of cell B1?
Roy CoxGroup Finance Manager

Commented:
You Edit names in the Name Manager which is in the Formulas Tab of the Ribbon
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HuaMin ChenProblem resolver

Author

Commented:
Thanks a lot.
How to edit Value below?
tt509.png
Can I adjust the validation list of the cell, using VBA, during run-time?
Roy CoxGroup Finance Manager

Commented:
You never mentioned VBA, why would you use VBA with data validation?

To change that names reference just click on the icon next to the address box and select the new range
HuaMin ChenProblem resolver

Author

Commented:
Where is the icon you've talked about below?
tt510.png
Roy CoxGroup Finance Manager

Commented:
The icon is in the Name manager at the bottom above the close button
HuaMin ChenProblem resolver

Author

Commented:
Thanks a lot.
Is there no way to adjust the validation list during run-time?
HuaMin ChenProblem resolver

Author

Commented:
Sorry Roy.
Originally in this thread, I've asked if there is a way to adjust Validation list programmably?
Roy CoxGroup Finance Manager

Commented:
You don't need VBA to do this
HuaMin ChenProblem resolver

Author

Commented:
Within the Excel file, per given value from Column A, there're different Validation list shown in Column B. Moreover, per given value from Column B, there're different Validation list shown in Column C. Now within Cells D2-52, I am to choose the validation list (that is for Column B). How to apply dependent Lookup on Column E and F?
test.xls
Roy CoxGroup Finance Manager

Commented:
As with most things in Excel design is of major importance

Your lists are not designed to work as Validation sources.

You have MergedCells which makes working with lists and most tasks in Excel difficult. You should avoid using MergedCells.

Which version of Excel are you using?
HuaMin ChenProblem resolver

Author

Commented:
I'm using Excel 2010.
HuaMin ChenProblem resolver

Author

Commented:
Is there a way to achieve what I've shown in above? Thanks a lot
Roy CoxGroup Finance Manager
Commented:
Not using that layout and I don't understand what you want to do.

This is similar to my previous suggestion but uses Tables allowing you to add or delete items
EE--Cascading-Validation.xlsx
HuaMin ChenProblem resolver

Author

Commented:
To my Excel file in above, I need to 2 other dependent validation list on Column E, F. If I have selected Hardware purchase column D, then within Column E, I only can choose those topic that is under Hardware purchase.

If I have selected 'PC' on column E, then on column F, I only can choose those Item codes that are under Topic PC.
HuaMin ChenProblem resolver

Author

Commented:
To my Excel file in above, I need to 2 other dependent validation list on Column E, F. If I have selected Hardware purchase on column D, then within Column E, I only can choose those topic that is under Hardware purchase.

If I have selected 'PC' on column E, then on column F, I only can choose those Item codes that are under Topic PC.
HuaMin ChenProblem resolver

Author

Commented:
In your example, how do you create Table 4 line below?
tt513.png
HuaMin ChenProblem resolver

Author

Commented:
Please omit my last reply.
Roy CoxGroup Finance Manager

Commented:
Is this question solved. If not attach your latest workbook and say what it needs.
Roy CoxGroup Finance Manager

Commented:
Pleased to help
HuaMin ChenProblem resolver

Author

Commented:
Very nice guy! Take care

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial