Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

Simple Formula

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
Bright01
Asked:
Bright01
  • 2
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Bright01Author Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Bright01Author Commented:
EXCELLENT!  Thank you for the quick solution.  It "works like a charm".

B.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now