Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Boxes | Excel Discussion (Misc queries) | |||
Getting Combo boxes to change options based on other Combo boxes. | New Users to Excel | |||
Selecting subsets using combo boxes or list boxes | Excel Discussion (Misc queries) | |||
Questions on combo boxes and list boxes. | New Users to Excel | |||
Combo boxes | Excel Discussion (Misc queries) |