Learn Excel – Data Validation Dropdown Arrows Always Visible? Podcast 1816

Learn Excel – Data Validation Dropdown Arrows Always Visible? Podcast 1816


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.

11 thoughts on “Learn Excel – Data Validation Dropdown Arrows Always Visible? Podcast 1816

  1. Honestly… I was impressed with the SnagIt approach and figured you were done. Clever to hide the index logic behind the combo box. Nicely done.

  2. How about this:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$3" Then
    SendKeys "%{down}"
    End If
    End Sub

    Then hook up the image to a simple macro to Range(A3).Activate

    In this case even if a user misses your image with a mouse and ends up in the validated cell, the macro kicks in and keeps the list of options open.

    Inspired by this on your forum:) /forum/excel-questions/505193-sendkey-show-me-dropdown.html

  3. Thanks for taking us through the whole though process and early attempts. This is perhaps more valuable than the solution.
    I've got to explore the 'backgroud' feature.

  4. Here's another possible solution for keeping the drop-down icons visible.  In the cell to the right of the validation list, use "Wingdings 3" font, insert the letter "q" or character code 128 from the symbols menu, then format the border, fill, & font colors with grey shades to make it look like a disabled drop-down icon.  It's not perfect, but another hack… 🙂  Here is a video that explains the whole thing and I also have a sample file you can download to copy it into your workbook.  http://youtu.be/oHtVmvqgZEM

  5. Hi Bill,
    Could possibly help me solve this problem with Data Validation, I’m using Excel 2011 for Mac.
    When entering first letter in the cell with drop down menu I get multiple choices that are not on my name list, it seems to pick up items above the list select.
    Example Match1, has only two teams to select “Melbourne & Sydney” but entering first letter “M” it shows: MCG, MS, Match1 & Melbourne, is there a way to the unwanted items out from the drop down list?
    Cheers,
    Don
    Below is a small sample worksheet.

    Stadium Home # Selected Away
    MCG Melbourne 8 Sydney
    ES Bulldogs 10 West Coast
    MCG Carlton 1 Adelaide
    MS Gold Coast 9 Nth Melbourne
    SPO GWS 12 Geelong
    AO Port Adelaide 11 Richmond
    ES Essendon 12 Brisbane
    MCG Hawthorn 6 Collingwood
    DS Fremantle 7 St Kilda

    Match 1 Match 2 Match 3 Match 4
    Sydney West Coast Carlton Nth Melbourne
    Melbourne Bulldogs Adelaide Gold Coast
    Melbourne Bulldogs Adelaide Gold Coast

Leave a Reply

Your email address will not be published. Required fields are marked *