# Formula help - Input message if criteria is met

#### rjs3434

##### Board Regular
Hi. I have 4 dropdown and 4 checkboxes on a sheet. I need to create a formula or macro that will input information into cell "BK38" if certain criteria are met.

Dropdown example:

# OF TRIPS DRIVEN BEYOND A 50 MILE RADIUS

(if the person clicks a value >24 for the following #', the following messages should appear in cell "BK38")

51-100 "A srg will apply"
101-150 "Non-bind"
151-200 "Non-bind"
201+ "Not-eligible"

(The message order of priority is 201+, 151-200, 101-150, 51-100. For ex. If 25 is checked for 201+ and 25 is checked for 151-200, the message "Not-eligible" appears.)

--------------------------------------------------------------------------------
There are also 4 checkboxes.

Retail
Wholesale
Farm
For-Hire

If "For-Hire" is checked, the message "Non-binding" should appear. If farm is checked, "Revenue producing?" should appear.

Any assistance would be greatly appreciated.

Thanks
RJ

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### acw

##### MrExcel MVP
RJ

For the first part:
IF you have the ranges in A4:A7 and the values are selected in cells B4:B7 then array enter (shift, ctrl, enter) the following formula in BK38.

=VLOOKUP(MAX(IF(B4:B7>24,ROW(B4:B7))),{0,"";4,"A srg will apply";5,"Non-bind";6,"Non-bind";7,"Not-eligible"},2,FALSE)

This will have to be modified to suit your actual data layout.

For the second part, can you specify whether the checkboxes are from the form menu, or the control toolbox menu. Also, where is the output to appear? Next to the checkbox, somewhere else???

Tony

##### MrExcel MVP
have a look @ trhe hall of fame forum for aladin's post on dependent lists.

#### rjs3434

##### Board Regular
Hi. Thanks for your assistance. However, I receive an error in BK38 of:

#value!

#### rjs3434

##### Board Regular
Hi. Thanks for your assistance. However, I receive an error in BK38 of:

#value!

__________________________________________________________________
2nd part:

The checkboxes are from the control toolbox. The output should appear in cell A5.

Thank You

#### acw

##### MrExcel MVP
HI

Make sure that the formula is array entered and that there are numbers only in the relevant range (no text).

With the control toolboxes, what happens if more than one is selected? Is there a hierarchy?

Tony

Replies
3
Views
234
Replies
31
Views
2K
Replies
0
Views
204
Replies
9
Views
136
Replies
2
Views
178

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,093
Messages
5,768,047
Members
425,451
Latest member
JohnBrooksBiddle

### 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.

### Which adblocker are you using?

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

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