Question to Validation list

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
LVL 12
HuaMin ChenProblem resolverAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverAuthor Commented:
Many thanks Roy.
Where can I edit the relevant list of cell B1?
Roy CoxGroup Finance ManagerCommented:
You Edit names in the Name Manager which is in the Formulas Tab of the Ribbon
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

HuaMin ChenProblem resolverAuthor 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 ManagerCommented:
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 resolverAuthor Commented:
Where is the icon you've talked about below?
tt510.png
Roy CoxGroup Finance ManagerCommented:
The icon is in the Name manager at the bottom above the close button
HuaMin ChenProblem resolverAuthor Commented:
Thanks a lot.
Is there no way to adjust the validation list during run-time?
HuaMin ChenProblem resolverAuthor Commented:
Sorry Roy.
Originally in this thread, I've asked if there is a way to adjust Validation list programmably?
Roy CoxGroup Finance ManagerCommented:
You don't need VBA to do this
HuaMin ChenProblem resolverAuthor 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 ManagerCommented:
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 resolverAuthor Commented:
I'm using Excel 2010.
HuaMin ChenProblem resolverAuthor Commented:
Is there a way to achieve what I've shown in above? Thanks a lot
Roy CoxGroup Finance ManagerCommented:
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 resolverAuthor 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 resolverAuthor 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 resolverAuthor Commented:
In your example, how do you create Table 4 line below?
tt513.png
HuaMin ChenProblem resolverAuthor Commented:
Please omit my last reply.
Roy CoxGroup Finance ManagerCommented:
Is this question solved. If not attach your latest workbook and say what it needs.
Roy CoxGroup Finance ManagerCommented:
Pleased to help
HuaMin ChenProblem resolverAuthor Commented:
Very nice guy! Take care
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.