Drop Down List Options and value assignment

[inq80]

Hi,

I got an issue which i need some help with if possible:

I've got a selection of drop down lists and used the =CONCATENATE command to bring all the options picked into one cell. I've then used the =VLOOKUP command to pull the information from an array in another worksheet which matches the info in the =CONCATENATE cell.

I was wondering if it was assign a number to the options in the drop down list that the =CONCATENATE cell can display rather than the text choose?

for example if option one was "RED", option two was "BALL" and three was "HALLWAY", can i assign "1" to RED, "2" to BALL and "3" to HALLWAY, so the =CONCATENATE cell displays 123, rather than REDBALLHALLWAY.

It would make my array so much easier to set up as some of the strings needed from the options could turn out like war and peace in the =CONCATENATE cell otherwise.

Is this possible? Any suggestions appreciated...

Thanks

[Chris 53]

Set up a number of Option tables with the option text in the left hand column and the number in the right hand column and then set your concatenate cell to vlookup each option table.

something like.

=vlookup(option1,Option1table,2,false) & vlookup(option2,option2table,2,false)

That way you can use the first column in each option table as the validation list for your options, thinking about this you already say you have option lists so if these are in tables then you are half way there already.

FYI "concatenate" can be shortened to "&"


Any use?

Chris

[inq80]

Thanks Chris,

Thats fantastic and so nearly worked a treat!

Couldnt figure out why it wasn't working at first before i realised I needed to check the row rather than column so had to change it to HLOOKUP. It's working in all the option boxes accept the first though?

I added a third drop down list to create the sequence i really need and 2 & 3 work fine - they change number in the sequence perfectly dependent on the option you choose. The first box though shows the wrong number when picked and then refuses to change when other options are picked? Have i made some basic coding error:

=HLOOKUP(C4,Sheet1!B1:J2,2) & HLOOKUP(C5,Sheet1!B4:F5,2,) & HLOOKUP(C6,Sheet1!A7:D8,2,)

[inq80]

and my post has been recognised for some reason in the forum so "Bump"?

[inq80]

Hi,

I've attached the spreadsheet so you can see what I'm talking about in case I'm being hopeless at explaining it:

The lists to the left all have a value to them dependent on the option picked in the drop down list. The previous advice about combining the lookup command with concatenate command, words great - accept for the first digit and i can't figure out why?

It should return a "1" for red but is instead returning "2". Also "green" is returning "2", when it should be "4". What on earth have i done wrong?

It's being used to fetch a certain cell info from the array to the right and that won't work if the returned number is out.

can anyone please help and advise what I've done wrong?

Also...

The numbers in cell H6 only return when all 3 options have been picked; is there away to get it to work when only one or two are selected so it returns "11" or "2" for example? I would then need the vlookup command in cell K20 to display all the returns with the same 1 or 2 starting digits. If anyone can suggest what i need to research to figure this out, it would be appreciated.

Thanks

Example.xlsx

[inq80]

sorted the first issue - "red" now return "1" after i sorted the row alphabetically...

Edit:

This created a second issue with the vlookup not working because it didn't recognise the hlookup return values as numbers, even after the cell was formatted to "number".

Got round this by multiplying by 1 so

=HLOOKUP(H2,B2:E3,2,FALSE)&HLOOKUP(H3,B4:E7,2,FALSE)&HLOOKUP(H4,B8:E15,2,FALSE)

turned into

=(HLOOKUP(H2,B2:E3,2,FALSE)&HLOOKUP(H3,B4:E7,2,FALSE)&HLOOKUP(H4,B8:E15,2,FALSE))*1

resolving this thread - question posed in the title now answered.