IF statement and Combo Boxes

nkwherron · External Since: Nov 03, 2009 Posts: 1

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))......and 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))......and 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

cm · External Since: Sep 25, 2009 Posts: 4

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))......and 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))......and 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

CellShocked · External Since: Oct 22, 2009 Posts: 5

On Tue, 3 Nov 2009 13:28:01 -0800, cm <cm.RemoveThis@discussions.microsoft.com>
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.