If you're using this tip, there are a few things to be aware of.
You don't need to setup the list of options on a worksheet, you can just type the items into the Source box on the Data Validation dialog. ie Enter Yes,No,Don't Know to get these options.
If you are using a list on a worksheet, but don't want to put it on the same worksheet as the validated cells, name the range your using (Insert/Name/Define) then use that name when you enter the Source. ie If you range is named "Choices", enter "=Choices".
The Validation dialog won't allow direct references to other sheets, but using the named range gets around the limitiation.
Hope these help.
Discussion on:
View:
Show:
The feature truly is very flexible. Thanks of the additional information. I had planned to follow up with an entry on how to work with lists not in the current sheet, but now I don't have to! 
I have a spreadsheet I have to produce and update twice a week. I thought the first hint was really good, but with your information, I can take the cell and copy it from sheet to sheet! You just eliminated about 20 minutes a week of data entry for me.
Thankyou . the above post was really helpful.
i have another doubt ..
i have added the dropdown in the field required . now i need to know how to add another dropdown relating to the 1st dropdown box that i already added .
like for e.g., A1 have the drop down stating countries list .it have some 50 countries.
now i need next column B1 to have states for the simultaneous country.
Say if i select India in A1 i should get options only for states in India (i.e its should have only the states in india . i should not have any states in other countries).. and if i select other countries i should have option to select states only from that particular country.
could you please reply ..
thank you
i have another doubt ..
i have added the dropdown in the field required . now i need to know how to add another dropdown relating to the 1st dropdown box that i already added .
like for e.g., A1 have the drop down stating countries list .it have some 50 countries.
now i need next column B1 to have states for the simultaneous country.
Say if i select India in A1 i should get options only for states in India (i.e its should have only the states in india . i should not have any states in other countries).. and if i select other countries i should have option to select states only from that particular country.
could you please reply ..
thank you
I'm sorry, but I don't know of anyway to maintain the link in a dropdown list. The control will display them, but they won't be live links.
Once the required link is in the cell, clicking on another cell makes the inserted link live - then clicking the link opens your browser. But the list of hyperlinks must be somewhere on the same sheet.
What I don't know is how to get the data validation part to link to the other sheet holding the hyperlink list.
Perhaps entering the data link manually would be possible, or use a VLOOKUP in the parent cell linking to the list sheet and cell refs or named range.
What I don't know is how to get the data validation part to link to the other sheet holding the hyperlink list.
Perhaps entering the data link manually would be possible, or use a VLOOKUP in the parent cell linking to the list sheet and cell refs or named range.
I can do this/have done this. The drop down seems to stop at about 750 records. My list is longer (about 800) and properly refernced but the drop down willnot let me scroll to the real bottom? Ideas on how to get all the way?
I ran into a problem similar to this. I ended up using the VLOOKUP function to solve my issue.
This article is useful. But, i wanted to know that
'How to add additional data to existing dropdown list in excel'
For. Ex. here we have selected data from A1:A4, now i want to add 2 more values in A5 and A6.
Please let me know how this can be done to get the same list with 2 new values
'How to add additional data to existing dropdown list in excel'
For. Ex. here we have selected data from A1:A4, now i want to add 2 more values in A5 and A6.
Please let me know how this can be done to get the same list with 2 new values
I might not understand your question. If you add items to the sheet, you must recreate the list. There's probably a way to update the list via a macro, but unless you do it often, it's simpler just to recreate it.
Instead of hard coding the range of cells for the list, if you use a named range then you can increase the length of the range without having to change your validation settings.
Thanks a lot man!!! This helped me a lot.....Cheers !!
Building on the suggestion of the named range, use a dynamic named range and you won't have to adjust the range parameters when you add new items.
How do I make the drop down box visible even when it is not selected
Is there a maximum list length. I cannot extend my cell list beyond 195 drop down entities
How to add color in dropdown list?
For example
cat is red
dog is blue
horse is green
and bird is on white
For example
cat is red
dog is blue
horse is green
and bird is on white
Conditional formatting will handle your color issues. An article published a couple of weeks ago explains how to use the feature. http://blogs.techrepublic.com.com/msoffice/?p=814
Using the same method for creating the drop down list select "Any value" from the Validation Criteria "Allow" list. This removes the drop down button as there is no predetermied data set to pick from.
You can also just type in the criteria box (after you have selected the Validation option) just separate the option with a comma
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
