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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

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
Bright01Author Commented:

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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
EXCELLENT!  Thank you for the quick solution.  It "works like a charm".

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.