Yesterday's netcast showed how to set up data validation to create a dropdown for a cell. The one problem is that someone might inadvertently delete a row from your list of values. Technically, Microsoft says that the validation list can not be on another worksheet, but Episode 379 shows you the workaround.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
On yesterday's netcast I showed you how to use Data Validation to add a drop-down right to a sheet.
And in that netcast I suggested that we should use an area out to the right-hand side to hide the list, maybe out in column Z or AZ or somewhere where no one's going to go.
The problem with that though is, what if someone comes along and deletes one of the rows, so they're working over here and column B and they delete cell B6, that's going to shorten your list.
It would be really good if we could have that validation list hidden in another worksheet.
Now technically, though, Microsoft says that the validation list has to be on the current worksheet.
You try and set up Data Validation, point it at something on another worksheet, you get a big error message saying “No, that's not going to work.” I have a great work around.
I've inserted an extra sheet here, and I've copied the valid list of districts to that sheet.
I'm going to select the list and name the list, I'm going to click in the name box and say maybe DistrictList, and hit Enter, now I have a named range that defines those values.
Now the interesting thing about a named range is that Excel thinks that a workbook-level name is valid on every single worksheet.
So, if I come here on my validation worksheet and say Data, Validation, I want to allow a list.
And then what's the source, I’m going to say =DistrictList, because this is a named range, even though it's on another worksheet, Excel gives me no problems at all.
So now I can get rid of this DistrictList that's out here on the right, my districts on the hidden worksheet are good and the validation works.
Now to hide a worksheet, just select the worksheet and use Format, Sheet, Hide.
And basically now you've created a validation list that’s almost foolproof, you can send it out and no one will be able to see the list or change the list or add new values to the list.
Great way to go, to use Data Validation.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
On yesterday's netcast I showed you how to use Data Validation to add a drop-down right to a sheet.
And in that netcast I suggested that we should use an area out to the right-hand side to hide the list, maybe out in column Z or AZ or somewhere where no one's going to go.
The problem with that though is, what if someone comes along and deletes one of the rows, so they're working over here and column B and they delete cell B6, that's going to shorten your list.
It would be really good if we could have that validation list hidden in another worksheet.
Now technically, though, Microsoft says that the validation list has to be on the current worksheet.
You try and set up Data Validation, point it at something on another worksheet, you get a big error message saying “No, that's not going to work.” I have a great work around.
I've inserted an extra sheet here, and I've copied the valid list of districts to that sheet.
I'm going to select the list and name the list, I'm going to click in the name box and say maybe DistrictList, and hit Enter, now I have a named range that defines those values.
Now the interesting thing about a named range is that Excel thinks that a workbook-level name is valid on every single worksheet.
So, if I come here on my validation worksheet and say Data, Validation, I want to allow a list.
And then what's the source, I’m going to say =DistrictList, because this is a named range, even though it's on another worksheet, Excel gives me no problems at all.
So now I can get rid of this DistrictList that's out here on the right, my districts on the hidden worksheet are good and the validation works.
Now to hide a worksheet, just select the worksheet and use Format, Sheet, Hide.
And basically now you've created a validation list that’s almost foolproof, you can send it out and no one will be able to see the list or change the list or add new values to the list.
Great way to go, to use Data Validation.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
