• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Applying multiple validations

S

sowmya.ch

New Member
Hi,


Am trying to apply a couple of validations to my worksheet.

Firstly, I want to make sure that in a specific range of cells(say A1:F1), the sum of the cell values does not exceed 100. I used the data validation tab and specified a custom validation with the formula (=SUM(A1:F1)<=100). That did not work, so I changed the formula to use cell references (=SUM($A$1:$F$1)<=100).


The second validation is to make sure that every cell from A1 to F1 contains only a decimal number. Since I can't apply a new validation on those cells, I tried to change the above formula to (=AND((=SUM($A$1:$F$1)<=100),(ISNUMBER(SUM($A1$1:$F$1)))).


The changed formula did not work at all. Can you please let me know if there are any alternatives to apply the validations I need?


Thank you!
 
use a cell as an auxiliary where you put the formula


= 100 - sum(A1:F1)


then, select your range you want to force validate (A1:F1) and go to Data Validation


allow Decimals that are "less than or equal to" a "Maximum:" in the auxiliary formula (if it was, for instance, cell G1, put =$G$1 in the "Maximum:" field... dont forget to do this with the whole range selected and dont forget the "$" to lock the Maximum cell...
 
Hey Nunes, I have a doubt here, I have done everything as you mentioned above and If i enter 32.70 in the f7, it is not allowing me. Could you please help me with this.


a6 b7 c7 d7 e7 f7 auxilary formula

10 15 15.57 10.5 16.23 32.70
 
yes, i see the problem, im getting it too...


the problem is excel is calculating the auxiliary formula value and only then forcing validation...


thats major bug in my solution, ill try to check if theres any way i can turn this around.
 
hi


yesterday when i thought of this solution i had my excel with manual calculation, which made this work, when it actually doesnt...


with the auxiliary value being 100 (the max sum allowed) when every cell in the validated range is zero, if you put something like 60 in the first cell, it first calculates the new value of the auxiliary cell, which will be 40 (100-60) and only the checking validation of range, which will fail cuz 60 is larger than the aux cell (40)...


this can be worked around, if you have as many auxiliary cells as cells to validate... for instance, in our example we hade range A1:F1 to validate, which means 6 cell to validate... we should have 6 auxiliary cells... in auxiliary cell #1 we should put =100-sum(cells to validate excluding cell #1), and so on...


then we should use data validation going from cell #1 allowing decimals that are less than or equal to auxiliary cell #1 (now without locking validation cell with $) and copying this cell to every other cell to validate...


this is getting quite confusing but its trully simple, really, ill try to post an example in a few hours...
 
check an example here... i believe its now working


https://docs.google.com/leaf?id=0B5piylDHt3ybNWJjMzNkMjktZTI3NC00MWI0LTgxMjgtZjk1N2JlNWQ4MzAx&hl=en_US


choose "download" to get sheet and check formulas and validation rules
 
Hi Nunes,


I had the same problem when testing the worksheet and was about to post about it when I noticed you and vijay_s_76 discussing it already. I downloaded the sheet with the new solution but I was wondering if there was any other way(if possible, simpler) to do this. I have around 67 columns in my sheet and using auxiliary columns for each one of those will not be easy. Moreover, I've to implement this programatically once I manage to work it in the worksheet.


Do you think I can modify the formula to calculate the difference after entering the value?


Thank you.
 
hey there,


you can use one of the data validation options which is "Custom" where we now have "Decimal" and in the Formula field put something like


"=SUM($B3:$F3)<=100"


note i locked the columns with $B and $F.


this way it works 100% but you loose the decimal validation, which means i can now enter text there...
 
SOLVED!!! :D


in the Custom option of Data Validation, you can write the Formula:


"=AND(ISNUMBER(B3);SUM($B3:$F3)<=100)"


note this is for cell B3, you can then copy cell B3 to range B3:F3 and the isnumber check inside the formula will propagate the cell address to be verified! ;)


i think this is it
 
Great! That formula was what I started with initially but I kept applying that to a range.. I guess applying to a single cell at a time worked now :)


Thank you and great response time!
 
no prob, my friend.


RSS feeds keep me updated with whats going on so i respond quickly! :D
 
You must log in or register to reply here.
Back
Top