Formula help - Input message if criteria is met

[rjs3434]

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

 

[acw]

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]

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

 

[rjs3434]

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

#value!

 

[rjs3434]

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]

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