Solved

# Simple Formula

Posted on 2011-10-17
129 Views
EE Professionals,

I am attempting to control the parameters of two cell inputs without writing a macro or putting in error handling code.

I have two contiguous cells where they start out with the condition "n/a" (i.e. C5 and D5).  I am looking for a data validation "custom" formula for C5 that says, the input number must be a whole number from 1 to 10 but must also be lower then any number put in D5 which is populated after C5 (i.e. it still has "n/a" in Cell D5 until after C5 is chosen.   D5 must be a number equal to or higher then C5 but less then 11 (i.e. 1-10 whole number) but may also be valid if it has "n/a" in the cell. Remember that both cells start out with "n/a" in them and will be reset to that so the validation must also be able to treat "n/a" without an error condition and I believe it automatically assigns a value of -0- to it.

Your help on this is much appreciated.

B.

0
Question by:Bright01

LVL 50

Expert Comment

Hello,

try these two custom data validation formulas:

C5 =IF(C5="n/a",TRUE,AND(C5<10,C5>0,MOD(C5,1)=0,C5<D5))
D5 =IF(D5="n/a",TRUE,AND(D5+C5<11,D5>=C5,MOD(D5,1)=0))

If D5 must be higher than C5, it would make sense to limit C5 to a maximum value of 5, otherwise there will be no valid option to be entered into D5.

cheers, teylyn
0

Author Comment

Teylyn,

Greetings!  Two things;

1.) C5 could be 6 and D5 could be 7.  Or C5 could be 9 and D5 could be 10.  Either cell can be between 1-10 except for the fact that D5 always has to be either n/a or higher then C5, up to 10.  What I'm also trying to avoid is the case where someone changes C5 after putting in numbers for C5 and D5 and then C5 must be less then D5.  Make sense?  That's why I think the limit is still 1-10 for both cells.

2.) I tried to reach you by your outside email address and got bounced each time.  In AUZ but will be unable to get to NZ on this trip.

B.
0

LVL 50

Accepted Solution

OK,

try these two

C5 =IF(C5="n/a",TRUE,AND(C5<=10,C5>=0,MOD(C5,1)=0,C5<D5))
D5 =IF(D5="n/a",TRUE,AND(D5<=10,OR(D5>C5,C5="n/a"),MOD(D5,1)=0))

cheers, teylyn
0

Author Closing Comment

EXCELLENT!  Thank you for the quick solution.  It "works like a charm".

B.
0

## Featured Post

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.