Formula to Prevent a higher % to be entered

**Preatorian** · 10-02-2009, 09:27 PM

Im looking for a way ( a formula) that will help to prevent entering a a percenta mpou

What formula can I use to accomplish the following, if it can be done.

If B9 is 100% all the rest of the % cell inputs would be 0%

If B9 is less 100% ,lets say 50% all the rest of the % cell inputs would be less than or equal to 50% until any sum of those cells equal to 100%
in which case if I enter 50% on B9 and 25% on D9 and 25% on F9 their total sum would be 100%.

If I try to enter a % on H9 it should not allow me to do it
because then the total sum on O9 would be more the the original amount on A9

I game myself a nosebleed thinking how to write this.

Any ideas.

**NBVC** · 10-02-2009, 09:36 PM

You would use Data Validation from the Data menu..

Using Custom from Allow menu

And entering formulas as

For B9,

=B9<=100%

In D9,

=SUM(D9,F9)<=100%

In F9

=SUM(D9,F9,H9)<=100%

etc.. .using same pattern.

**Paul** · 10-02-2009, 09:42 PM

You could probably use the same method, but select all entry cells (B9,D9,F9,H9,J9,L9) and then launch Data Validation, select Custom and set the formula to:

=SUM($B$9,$D$9,$F$9,$H$9,$J$9,$L$9)<=1

Then, regardless of which order you enter data into the cells, it will stop you if you try to go over 100% (1.00).

**Simon Lloyd** · 10-02-2009, 09:59 PM

Cross posted here http://www.vbaexpress.com/forum/showthread.php?t=28660 under a different name!

**Preatorian** · 10-02-2009, 10:01 PM

Thank You Very Much.

May I trouble you with another question.

If I already have lets say 90% on B9 and I try to enter 15% on D9 it wont allow me because of the total being over 100%, but how can I have an alert to say Please enter 10% or less.

Or

If I already have lets say 50% on B9 and I try to enter 20% on D9 and I try to enter 50% F9 it wont allow me because of the total being over 100%, but how can I have an alert to say Please enter 70% or less (the sum of B9+D9).

Thanks..

**NBVC** · 10-02-2009, 10:08 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

**NBVC** · 10-02-2009, 11:14 PM

Received your message... thanks. Please remember that if you do crosspost you should provide a link so others can decide whether it is worthwhile to try to assist without working on something that have already had a solution.

With regards to your question, you cannot do that with Data Validation.. you can only alter the error message to make a general statement, you can't get it to perform calculation or decisioin making and return results in an error message.

... for that you can probably use VBA.

If that interests you, please post the question in the Programming forum and mark this one as SOLVED.