Combobox formula changing value in a cell

[Adrian1]

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

 

[fairwinds]

Hi,

Could this be a way to go?

Formula to be entered with Ctrl + Shift + Enter.

Book1.xls
	A	B	C	D	E	F
1	Listfillrange		Box3link	Box4link	Box5link	Box6link
2	HH12MONTH		HH12MONTH	HH12MONTH		NHH12MONTH
3	HH24MONTH
4	NHH12MONTH
5	NHH24MONTH
6				NHH/HH
7
Sheet5

 

[Adrian1]

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????

 

[fairwinds]

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]

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]

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.