Drop down menu help

I want to create a drop down menu in the attached sheet.

The sheet functions as a tool to input 1 server name and have it update all of the commands that we may need to use for that server. I've named the commands and was hoping to get a drop down menu with the Networker Command name and then have it spit out the proper command in the output.

I just think it looks a bit cleaner because some of the commands are pretty lengthy.

For B3 use Data Validation (I forget where it is for Excel 2013, for me it is on the Data Ribbon). On settings change "Allow" to "List" and specify your range of commands. If you have a range of unknown length, it doesn't show blanks after the last entry (not 100% this is true for Excel 2013, but I think so). This will probably look better without blank lines in between.

Then for B5 use a VLOOKUP from B3 to the same range, e.g. =VLOOKUP(B3,A8:B12,2,0)

Hope that helps,

Nick

Originally Posted by AliGW

That's not true, either in Excel 2013 or Excel 365.

Hmm, it seems we are both sort of right. For 365 at least. I can't test earlier versions.

Just to ensure we are talking about the same thing, I am referring to the dropdown list created by using the List setting of the Data Validation feature.

I thought that the behaviour was that it shows in the dropdown list the whole of the defined range until the last value, but does not show blank items after the last value. So for example, you set the List range to A8 to A20, and there are values in cells A8, A10, A12 (as per the example workbook), it will show the first 5 rows in the dropdown menu.

Having looked at it a bit more, if you enter a value in A16 for example, and then delete it, it still shows the blank rows. Same is true even if you enter a value in B16. I guess it doesn't show them if they are outside the used range of the sheet.

Originally Posted by AliGW

I am glad you think you know what the OP wants to do here.

I thought I knew what the OP was after as have done a very similar thing myself before

Well, that's what it does for me. Normally when I use this feature I have the reference list on its own sheet, so that's what I've always observed. I've attached a couple of screenshots, one showing the List settings, the other showing the lack of a scroll bar in the drop down menu.

Here is the latest command I am getting an error on and I'm not sure why. I am following the same format as before but it's giving this error....


Command I need with B2 input:

="mminfo -avot -q client="&B2&" -r"client ,pool,savetime(22),ssretent,name,level,totalsize(2),ssflags,clflags,sumflags""

Error: We found a problem with this formula. Try clicking Insert Funct ....

And then it highlights this section of the formula

mminfo -avot -q client="&B2&" -r"client ,pool,savetime(22),ssretent,name,level,totalsize(2),ssflags,clflags,sumflags"

I don't know what code you are trying to create. Does it have a " in it? If not, then you have formatted your string incorrectly, the " after r (in r"client) ends the string, Excel is then expecting to see either a function or an &.

If you are trying to put in a " then you need to use CHAR(34) instead (and have & either side).

Originally Posted by nick.williams

I don't know what code you are trying to create. Does it have a " in it? If not, then you have formatted your string incorrectly, the " after r (in r"client) ends the string, Excel is then expecting to see either a function or an &.

If you are trying to put in a " then you need to use CHAR(34) instead (and have & either side).

I was trying to put in a " so I used double quotes as escape characters.

Thanks Nick!

You’re welcome.