Can you keep the data validation drop-down arrows visible? This video offers three alternatives.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1816.
Validation Drop-down Arrows Always Visible?
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Now, if you have the specific problem, you're going to love this video.
If you don't have the specific problem, I just stop because it is going to be a long winding.
Horrible set of steps. Fred has a great spreadsheet with some data validation and he says no one realizes that there are drop-down.
So, how can they remain visible at all times?
And sure enough, he does have some data validation but the drop-down only appears when you select the cells that have the data validation.
Now, one idea the simplest idea is just to color those cells.
So, we select all cells, [ control G ] for go to special, choose the cells that have data validation.
Click [ ok ] and then apply some sort of a color, maybe a cell style of input or just apply a background color.
I don't know and then have a note that says hey, all yellow cells are input cells, select them to have a drop-down up here.
All right! So, that's one choice, but I understand that may not be the best way to go.
So, I the first thing I thought was well, what if we could trick the people in to clicking on the cell because if they click on the cell then this drop-down appears right and so I created this shape that looks like a drop-down arrow.
Now, I'm going to walk through all this, just freeze the video here, and there's 12 steps in order to create that shape and I said well, we'll take the shape and we will put it in the cell.
Alright! So, that way if we click away the drop-down arrow still appears there but then the bad thing is when they go to click on the drop-down arrow.
It selects the shape, instead of selecting the cell and that was really, really bad and I've spent a lot of time this morning in here, under format shape, trying to find the property that says hey, don't let me be clicked.
The only thing, I can think of is maybe if I would lock the object and then protect the whole sheet.
But that's going to require a lot more work with the protection.
So, I'm not even going to go there, but what I did come up with was I put these shapes in these three cells where they need to be and then made a copy of this worksheet, I control dragged over here to the dummy worksheet and deleted everything, except for the shapes.
All right! Then I use snag it, one of my favorite tools and took a picture of everything, from this top left pixel all the way down to the bottom right pixel and save that.
Let's see if we can show you what it looks like and snag it.
Yeah, here we go.
So, it's just a big picture, lots of white with the three drop-down arrows and then I came back to my original spreadsheet, got rid of these drawing objects.
Just put that one, out there to the right and then I got rid of this yellow fill color because that's going to ruin the effect.
So home, no fill, then selecting cell A1, page layout, background specify that image that I just created.
Checked that out, so that creates something in the background, so when I actually, click on this arrow.
It selects the cell which brings up the drop-down arrow.
Now, people going to say well, hey! Why do I have to click twice?
And just you know, say well, that's the way it is.
All right! So, this is using a lot of tricky, trickery to create fake arrows, to make people think that there's a drop-down there and then the real drop-down appears.
Let's just abandon that and go with what I think is the right way to go.
We need to say have the developer tab if you don't have the developer tab, right click on the Ribbon and say customize the Ribbon.
Over here on the right hand side, you're going to choose developer.
That's unchecked by default in Excel 2010 and Excel 2013.
In Excel 2007, you want to go to the popular category right here and show developer tab in the Ribbon, is right in that area.
Once we have that, we're going to take this cell.
Now, this cell is giving us the model number, currently it is set up to use data validation.
It's grabbing data from A2 to A54.
I'm going to copy that and we are going to clear that data validation.
So, [ alt D L ] and just say it, we're going to allow any value.
Click [ ok ].
There are a lot of cells that are dependent on this cell, formulas trace dependence, so when we change this cell 1 to 3, other cells, actually all of these cells are all lookups into that cell.
So, it's really important that we end up with a model number in that particular place.
We'll clear those arrows, remove arrows.
All right! On the developer tab, under insert.
We're going to use a format control, combo box.
I want the combo box to be exactly the same size it has cell A3.
So, I start to drag here and then I hold down the alt key.
The alt key will make the box be the exact same size, right click and say format control.
The input range is pasted there, that's what I copied from the data validation and then the cell like that normally you would think, you'd want to cell link to be A3.
But these forms, drop down boxes do not work that way.
So, I'm going to use equal W1, click [ ok ].
So, here's how this works.
We open the drop down box, and we select the second item and the linked cell gives us the number 2.
It's not giving us the value that we select, it's telling us the position of the item that we select and which is very, very frustrating, right.
Okay! So, now that we have that position.
We're going to say, equal index of all of the answers, and I'm going to still paste [ ctrl V ].
Let's drag this to the right, so we can see the whole formula, comma this answer get rid of the equal sign, mini6 240V, is appearing there and the formula is showing the right thing, if we open it.
Open it up and choose another item.
So mini 2.5. 120 V that was the second item and we're getting the right answer there.
So, that formula is the formula that we're going to hide.
I'm going to copy that from the clipboard, we're going to hide that underneath the drop-down.
So, right here where it's a hard-coded value or paste that formula in.
All right! So, what we have is we have a drop down.
Now, that's always visible, when we come in here and choose it.
The under-lying value is changing even though, we can't see it.
It's kind of like the light in the refrigerator when the door is closed.
But all of these values out here are changing in response to that drop down.
You have to use the same trick for the other two input cells building link cells out here.
And you'd probably want to either hide these or move them out to column AZ or IV or wherever.
So, that way no one can see them or even just simply hide them by changing the font to be a white font So, where they don't appear out there.
All right! So, we want the data validation drop-down arrows to always be visible.
Well, there's no way to do that.
We can fool Excel by using the background image.
We could just highlight the validation arrows in yellow and put a big note here or we can use the combo box from the forms control, to try and solve that problem.
Hey! I want to thank everyone for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1816.
Validation Drop-down Arrows Always Visible?
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Now, if you have the specific problem, you're going to love this video.
If you don't have the specific problem, I just stop because it is going to be a long winding.
Horrible set of steps. Fred has a great spreadsheet with some data validation and he says no one realizes that there are drop-down.
So, how can they remain visible at all times?
And sure enough, he does have some data validation but the drop-down only appears when you select the cells that have the data validation.
Now, one idea the simplest idea is just to color those cells.
So, we select all cells, [ control G ] for go to special, choose the cells that have data validation.
Click [ ok ] and then apply some sort of a color, maybe a cell style of input or just apply a background color.
I don't know and then have a note that says hey, all yellow cells are input cells, select them to have a drop-down up here.
All right! So, that's one choice, but I understand that may not be the best way to go.
So, I the first thing I thought was well, what if we could trick the people in to clicking on the cell because if they click on the cell then this drop-down appears right and so I created this shape that looks like a drop-down arrow.
Now, I'm going to walk through all this, just freeze the video here, and there's 12 steps in order to create that shape and I said well, we'll take the shape and we will put it in the cell.
Alright! So, that way if we click away the drop-down arrow still appears there but then the bad thing is when they go to click on the drop-down arrow.
It selects the shape, instead of selecting the cell and that was really, really bad and I've spent a lot of time this morning in here, under format shape, trying to find the property that says hey, don't let me be clicked.
The only thing, I can think of is maybe if I would lock the object and then protect the whole sheet.
But that's going to require a lot more work with the protection.
So, I'm not even going to go there, but what I did come up with was I put these shapes in these three cells where they need to be and then made a copy of this worksheet, I control dragged over here to the dummy worksheet and deleted everything, except for the shapes.
All right! Then I use snag it, one of my favorite tools and took a picture of everything, from this top left pixel all the way down to the bottom right pixel and save that.
Let's see if we can show you what it looks like and snag it.
Yeah, here we go.
So, it's just a big picture, lots of white with the three drop-down arrows and then I came back to my original spreadsheet, got rid of these drawing objects.
Just put that one, out there to the right and then I got rid of this yellow fill color because that's going to ruin the effect.
So home, no fill, then selecting cell A1, page layout, background specify that image that I just created.
Checked that out, so that creates something in the background, so when I actually, click on this arrow.
It selects the cell which brings up the drop-down arrow.
Now, people going to say well, hey! Why do I have to click twice?
And just you know, say well, that's the way it is.
All right! So, this is using a lot of tricky, trickery to create fake arrows, to make people think that there's a drop-down there and then the real drop-down appears.
Let's just abandon that and go with what I think is the right way to go.
We need to say have the developer tab if you don't have the developer tab, right click on the Ribbon and say customize the Ribbon.
Over here on the right hand side, you're going to choose developer.
That's unchecked by default in Excel 2010 and Excel 2013.
In Excel 2007, you want to go to the popular category right here and show developer tab in the Ribbon, is right in that area.
Once we have that, we're going to take this cell.
Now, this cell is giving us the model number, currently it is set up to use data validation.
It's grabbing data from A2 to A54.
I'm going to copy that and we are going to clear that data validation.
So, [ alt D L ] and just say it, we're going to allow any value.
Click [ ok ].
There are a lot of cells that are dependent on this cell, formulas trace dependence, so when we change this cell 1 to 3, other cells, actually all of these cells are all lookups into that cell.
So, it's really important that we end up with a model number in that particular place.
We'll clear those arrows, remove arrows.
All right! On the developer tab, under insert.
We're going to use a format control, combo box.
I want the combo box to be exactly the same size it has cell A3.
So, I start to drag here and then I hold down the alt key.
The alt key will make the box be the exact same size, right click and say format control.
The input range is pasted there, that's what I copied from the data validation and then the cell like that normally you would think, you'd want to cell link to be A3.
But these forms, drop down boxes do not work that way.
So, I'm going to use equal W1, click [ ok ].
So, here's how this works.
We open the drop down box, and we select the second item and the linked cell gives us the number 2.
It's not giving us the value that we select, it's telling us the position of the item that we select and which is very, very frustrating, right.
Okay! So, now that we have that position.
We're going to say, equal index of all of the answers, and I'm going to still paste [ ctrl V ].
Let's drag this to the right, so we can see the whole formula, comma this answer get rid of the equal sign, mini6 240V, is appearing there and the formula is showing the right thing, if we open it.
Open it up and choose another item.
So mini 2.5. 120 V that was the second item and we're getting the right answer there.
So, that formula is the formula that we're going to hide.
I'm going to copy that from the clipboard, we're going to hide that underneath the drop-down.
So, right here where it's a hard-coded value or paste that formula in.
All right! So, what we have is we have a drop down.
Now, that's always visible, when we come in here and choose it.
The under-lying value is changing even though, we can't see it.
It's kind of like the light in the refrigerator when the door is closed.
But all of these values out here are changing in response to that drop down.
You have to use the same trick for the other two input cells building link cells out here.
And you'd probably want to either hide these or move them out to column AZ or IV or wherever.
So, that way no one can see them or even just simply hide them by changing the font to be a white font So, where they don't appear out there.
All right! So, we want the data validation drop-down arrows to always be visible.
Well, there's no way to do that.
We can fool Excel by using the background image.
We could just highlight the validation arrows in yellow and put a big note here or we can use the combo box from the forms control, to try and solve that problem.
Hey! I want to thank everyone for stopping by, we'll see you next time for another netcast from MrExcel.
