Link to home
Avatar of Peter Chan
Peter ChanFlag for Hong Kong

asked on

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?

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Peter Chan

ASKER

Many thanks Roy.
Where can I edit the relevant list of cell B1?
You Edit names in the Name Manager which is in the Formulas Tab of the Ribbon
Thanks a lot.
How to edit Value below?
User generated image
Can I adjust the validation list of the cell, using VBA, during run-time?
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
Where is the icon you've talked about below?
User generated image
The icon is in the Name manager at the bottom above the close button
Thanks a lot.
Is there no way to adjust the validation list during run-time?
Sorry Roy.
Originally in this thread, I've asked if there is a way to adjust Validation list programmably?
You don't need VBA to do this
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
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?
I'm using Excel 2010.
Is there a way to achieve what I've shown in above? Thanks a lot
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
In your example, how do you create Table 4 line below?
User generated image
Please omit my last reply.
Is this question solved. If not attach your latest workbook and say what it needs.
Pleased to help
Very nice guy! Take care