How can I create 2 dependant scroll-down menues in 2 different cells in Excel 2007 directly in these cells without using any data reference?

Creating one scroll-down menu is easy, I just go to tab Data and choose Data validation and can insert a scroll-down menu as a list and just enter the values in this list directly in the Data validation window (no need to reference to any cells containing data).

I would like to do the same thing but now using two dependant scroll-down menues in 2 different cells, this time also by not having to enter data "externally" somewhere on the Excel sheet but handle everything neat and tidy inside this Data validation window. If that is not possible, can I use VBA to achieve this?

An example of the drop-down menues:
Cell A1: Winter;Spring;Summer;Autumn

Cell A2:
If "Winter" is selected in A1, then display this scroll-down menu: November;December;January;February;March
If "Spring" is selected in A1, then display this scroll-down menu: April;May
If "Summer" is selected in A1, then display this scroll-down menu: June;July;August
If "Autumn" is selected in A2, then display this scroll-down menu: September;October

Furthermore, I would like to display a hint/info text for each of the alternatives inside the drop-down menu if possible (for example for "December" the hint/info text "Very cold month" should be displayed).
LVL 1
hermesalphaAsked:
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.

Saurabh Singh TeotiaCommented:
Have a look over Here

This is exactly what you are looking for as this talks about in detail about how to create dependent data validation...

Saurabh...
0
regmigrantCommented:
The article Saurabh recommends is comprehensive and covers all sort of options so I think you should pursue that route if you want a flexible, robust solution and you have time.

for a quick result you can define the following as a 'named cell' by going to formulas, Name Manager, New, give it a useful name (eg: Seasons), and copy paste the following into the 'refers to' box
=IF($A$1="Winter","November,December,January",IF($A$1="Spring","April,May",IF($A$1="Summer","June, July,August",IF($A$1="Autumn","September,October",""))))

Then in the 'list' section of the data validation box put "=Seasons"

Neither approach can change the fact that the hint text within data validation applies to the cell itself (not it's content) but you could have an extra cell giving the hint text by applying the above logic and defining a Named object that returns the appropriate text

ps: - what happened to February and march?
0
hermesalphaAuthor Commented:
regmigrant, I think your suggestion would work. By the way, I said wrong in the original question: What I need is that these 2 dropdown menues are in the same cell.

I tried the data validation method now and entered alternatives for the list separated with a semicolon, but the length of the list is quite limited. How can I enter a longer list (each item rather lengthy)?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

regmigrantCommented:
If you follow the suggestion then the content of the list field in 'Data Validation' should just be a named range such as '=Seasons'.

If the problem is entering the details into the 'Manage Names' dialog then you will have to consider putting the data into a hidden sheet and defining the name by pointing to the that - in this way your list can be as long as a you want (if you exceed 255 characters you will need to split it across cells but the principle will remain the same).

Adding two drop downs to the same cell sounds very unlikely, each drop down is designed to produce one result which is then put into the cell. you might be able to get closer with VBA but you will be breaking one of the basic navigation standards and might find people struggle to understand
0

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
hermesalphaAuthor Commented:
regmigrant, I don't mind if navigation standards are broken because this will be for myself, so as long as I understand it it's no problems.

Wouldn't it be possible, using VBA, to run a macro from the first drop-down list that triggers the second drop-down list inside the same cell?:

http://www.get-digital-help.com/2012/12/28/run-a-macro-from-a-drop-down-list-vba/
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
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 Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.