I wanted to get an (albeit short) tip up before Christmas, though, and it is one of my New Year resolutions to post at least once a month in 2017.
Your Christmas tip then is a simple answer to a particularly annoying aspect of data validation dropdown boxes.
A feature of data validation drodpown lists in Excel is that if the cell already contains a value from the list, the dropdown starts with that value selected, and you need to scroll up if you want to select an earlier value.
This is normally fine except for the following (very common) scenario.
It is good practice to have the dropdown list look at a range where its entries can be edited, and to leave space at the bottom of the list to allow the list to be added to.
However, a side-effect of this is that, when your cell is empty, the dropdown will start at the bottom of the list, as it sees the empty cells at the bottom as a match for the current entry (nothing).
The simplest answer I have found to this is to have a blank cell at the top of the list as well. As this matching feature matches the first match it finds, your dropdown list will now start at the top (for an empty cell).
I often find the neatest way to do this is to have a blank row under the headers that doesn't look like part of the list:
In the example above, we could use the range A2:A14 to drive the dropdown list and cell A2 would be the first match for a blank cell, rather than A12.
That's it for now, and 2016! May you and your family have a great, safe and Merry Christmas (or whatever holiday you celebrate) and a fantastic 2017.
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".
Why would you want to leave a blank at the end?
ReplyDeleteIf your valid list is a table then it will automatically be extended when you add a new entry
jim
and don't you think you're leaving it a little late this season; I know you like to make the Escape exciting but this could be a little too far
PS
Deleteand a Happy Christmas to you too; good to see you blogging again
Jim
DeleteIf I am developing a spreadsheet for someone else to use, I try to avoid tables for user input, as Microsoft have yet to properly integrate them in sheet protection. You can't protect formulae or the sheet layout with a table on it, if you also want to add rows to the table. A defined range with space to add more can be locked down and also make it more obvious that items can be added.
And this is not late. We've started to get some decent results the last few weeks and its only Christmas. We're usually lower than this in April!
All the best
good points
Deletehow about a dynamic range which picks up all non-blank cells with conditional formatting that formats the 1st blank cell as though it were part of the range
I used to use dynamic named ranges all the time until I discovered tables, now I hardly ever need them (but I only improve others' spreadsheets to work better, not full-blooded developing)
jim
A dynamic range is a more elegant solution, but in most cases, I'm not sure there are any tangible benefits for the extra work and complexity involved in setting it up.
Delete