Combobox formula changing value in a cell

Adrian1

Board Regular
Joined
Dec 30, 2003
Messages
126
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Could this be a way to go?

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

Adrian1

Board Regular
Joined
Dec 30, 2003
Messages
126
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????

:rolleyes:
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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?
 

Adrian1

Board Regular
Joined
Dec 30, 2003
Messages
126
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
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,205
Members
416,080
Latest member
blemon

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?

Disable AdBlock

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
Top