# Combobox formula changing value in a cell

##### Board Regular
Hello,

I am stuck!

I have combobox3, combobox4, combobox5 which each have data ranges A1:A4. The data conatined in the combobox range is 'HH 12 MONTH' 'HH 24 MONTH' 'NHH 12 MONTH' 'NHH 24 MONTH'.

What I want is some kind of formula that is in a cell so that if the the comboboxes contain any combination of HH & NHH then the cell will have the value HH or NHH accordingly.

eg. if combobox3 = 'HH 12 MONTH'
combobox4 = 'HH 24 MONTH'

...then the cell value should be HH. Similarly....

if combobox3 = 'HH 12 MONTH'
combobox4 = 'NHH 24 MONTH'
combobox5 = 'HH 24 MONTH'
combobox6 = 'NHH 12 MONTH'

....then the cell value will be NHH/HH

Hopefully this will make sense to someone. Please help as if I learn how this is done it will help me do lots of other things!!!

Thank you

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi,

Could this be a way to go?

Formula to be entered with Ctrl + Shift + Enter.
Book1.xls
ABCDEF
2HH12MONTHHH12MONTHHH12MONTHNHH12MONTH
3HH24MONTH
4NHH12MONTH
5NHH24MONTH
6NHH/HH
7
Sheet5

wow...maybe I am in over my head a bit here as I don't understand what you mean there. I tried messing about with the formula but couldn't get it to work. maybe it is worth mentioning that I am a novice and using version 98.

Maybe if I explain a little differently. I have a workbook that has got 4 comboboxes. These comboboxes allow me to pick from a listfillrange on another sheet and select either NHH 12/24 MONTH or HH 12/24 MONTH.

Once the selection has been made, the sheet is printed out.

What I want is another cell on the sheet to display whether or not the value shown in the comboboxes is HH, NHH, or both!

I know this is difficult to explain.

Maybe if I put it like this to you:

If combobox 3-6 = "NHH 12 MONTH","NHH 24 MONTH" THEN CELL VALUE IS NHH

If combobox 3-6 = "NHH 12 MONTH","NHH 24 MONTH","HH 12 MONTH","HH 24 MONTH" THEN CELL VALUE IS NHH/HH

If combobox 3-6 = "HH 12 MONTH","HH 24 MONTH" THEN CELL VALUE IS HH

...and so on until all of the possible combinations are covered in the formula to return the correct value in the cell.

Does anyone know what I mean????

As far as I can understand, thats what I did.

C2 to F2 holds the result of your combo box choices.

Paste this formula:

=CHOOSE(MAX(--(LEFT(C2:F2,2)="HH"))+MAX(--(LEFT(C2:F2,2)="NH"))*2+1,"","HH","NHH","NHH/HH")

in any cell and confirm with Ctrl + Shift + Enter (not just with Enter.)

Does that not give you what you want?

sorry fairwinds, I know you are trying and it sort of works but I don't think I have the knowledge to be able to adapt this formula to work in my spreadsheet. Let me try to simplify things a bit (for me that is!!!)

Lets say I have Sheet1 with the following cell values:

A1 = HH 12
A2 = HH 24
A3 = NHH 12
A4 = NHH 24

...I then have combobox1 which has the listfillrange A1:A4.

I want cell A6 to have a formula in it so that if the combobox has HH 12 or 24 in it then it will say HH. If it has HH 12 & NHH 12 then it will have NHH/HH in it. and so on until all the combinations are covered in the formula.

Does this make it easier?
Thanks

I think I understand your question but I don't understand what is not working with my suggestion. Please explain what is not working then maybe me or someone else can come up with a better sollution.

Replies
1
Views
217
Replies
4
Views
163
Replies
2
Views
400
Replies
9
Views
155
Replies
35
Views
2K

1,219,829
Messages
6,150,475
Members
450,967
Latest member
itzwinger

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back