IF statement and Combo Boxes
 

Can anyone help with the following?

Hi Adam

I'm trying to design something for THT and I have run into a problem with a
formula and wondered if you could help or ask someone for me. The example is
below.

=(IF(A10="Counselling Room
1",(D10*5)+(E10*50)+(F10*50)+(G10*100),0))......an d so on. T

This works OK if the cell A10 contains a value like "Counselling Room 1"
shown here. However rather than letting the end user enter freeform text in
here I want to put a combo box in the cell so they only have a specific
number of option sto choose from. In the formula instead then of having the
above I have put the following so that it looks at the value chosen in the
Drop Down Box.

=(IF("Drop Down Box 12"="Counselling Room
1",(D10*5)+(E10*50)+(F10*50)+(G10*100),0))......an d so on.

It doesn't complain that the formula above has an error but it doesn't seem
to calculate the formula for me. I have also included a copy of the
spreadsheet. Your help would be very much appreciated.

--
Niall Herron

IF statement and Combo Boxes
 

I would suggest using a data validation list rather than a combo box. Create
your list and then refer to the list as follows:

Choose Data Validation (on the Settings tab) Allow 'List' and then in the
'source' box you will put the cell references for your list values.
--
hope to help,
cm


"nkwherron" wrote:

Can anyone help with the following?

Hi Adam

I'm trying to design something for THT and I have run into a problem with a
formula and wondered if you could help or ask someone for me. The example is
below.

=(IF(A10="Counselling Room
1",(D10*5)+(E10*50)+(F10*50)+(G10*100),0))......an d so on. T

This works OK if the cell A10 contains a value like "Counselling Room 1"
shown here. However rather than letting the end user enter freeform text in
here I want to put a combo box in the cell so they only have a specific
number of option sto choose from. In the formula instead then of having the
above I have put the following so that it looks at the value chosen in the
Drop Down Box.

=(IF("Drop Down Box 12"="Counselling Room
1",(D10*5)+(E10*50)+(F10*50)+(G10*100),0))......an d so on.

It doesn't complain that the formula above has an error but it doesn't seem
to calculate the formula for me. I have also included a copy of the
spreadsheet. Your help would be very much appreciated.

--
Niall Herron

IF statement and Combo Boxes
 

On Tue, 3 Nov 2009 13:28:01 -0800, cm
wrote:

I would suggest using a data validation list rather than a combo box. Create
your list and then refer to the list as follows:

Choose Data Validation (on the Settings tab) Allow 'List' and then in the
'source' box you will put the cell references for your list values.


I make an array of cells containing the desired drop down box list, and
then NAME that array.

You can name several, actually, and then validate to that named range
in the validation dialogs, as in: =RangeName

Where "RangeName" is equal to the named range "name" you gave it.

If you want it to be dynamic, you can use =INDIRECT() to assist with
that.