Formula help - Input message if criteria is met

rjs3434

Board Regular
Joined
Feb 11, 2003
Messages
84
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
Joined
Feb 13, 2004
Messages
4,814
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
have a look @ trhe hall of fame forum for aladin's post on dependent lists.
 

rjs3434

Board Regular
Joined
Feb 11, 2003
Messages
84
Hi. Thanks for your assistance. However, I receive an error in BK38 of:

#value!
 

rjs3434

Board Regular
Joined
Feb 11, 2003
Messages
84
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
Joined
Feb 13, 2004
Messages
4,814
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top