Data validation across multile columns

[Drainman]

hello all!

I have a spreadsheet which is an order form with products down the left, then columns where you can enter quantities required, then the line total is calculated on the far right. What i am trying to achieve is to limit the total value of items that can be added to a maximum value of 15.00GBP. So, the range E9 to I89 contain the cells where people can enter quantities. What i have tried so far is a data validation formula =MIN(K7,SUM(E89:I169)). K7 is a cell which contains the value 15.00. I have also tried changing the formula by locking the columns or the rows using the F4 key but that hasnt solved it. Please can someone help?? thanks...

[Aardigspook]

Hi, welcome to the forum.

I'm not sure if your range is E9:I89 or E89:I169 as you've given both, so change the range as needed in this Data Validation formula:

=SUM($E$9:$I$89)<=$K$7

Apply it by first selecting the range, then going to Data Validation, select 'Custom' and enter the formula. Make sure you include the $s to make the references absolute.

Hope that does what you want.


Edit: when invalid data is entered, the default error message is 'The value you entered is not valid. A user has restricted values that can be entered into this cell.' You can change this message using the 'Error Alert' tab in the Data Validation dialogue box.

[Drainman]

Many thanks for your response! Apologies for the confusion it is E9:I89. have done exactly as you said... however it doesn't prevent a user exceeding 15.00... any ideas?? Thank you

[Aardigspook]

It seems to work okay for me. In the attached sheet I've applied the DV to E9:I89 and entered £15.00 in K7, then entered £0.50 in 30 cells within the DV range, totalling £15.00. If I try to enter any figure in any other cell in the range, or increase any of the £0.50 entries, it throws up the error message I've entered (Your total sum entered exceeds the limit. Please review your choices.).
The sheet isn't protected in any way.
Please check if this sheet works for you.


Additional extra option:
I've put a second sheet on as well, which checks if the entry being made is a number - if not, that's blocked with an error message as well. That uses this DV formula:

=AND(ISNUMBER(E9),SUM($E$9:$I$89)<=$K$7)

[Drainman]

Thanks for your additional input. I have downloaded your sheet and reviewed your comments. My sheet is slightly different in that the input values are just quantities and the values are calculated separately. I have attached the sheet and some screenshots if you cant open the sheet - hope you are able to shed some light on it! thank you again.

[Aardigspook]

Ah, that changes things a bit. I can't open the attached sheet where I am now (firewall issues with .xlsm files) but I'll have another look when I'm at home tonight. I think I see how to do it, but can also see a potential 'gotcha' so I'll get back to you once I've had a go.

[Drainman]

Excellent - thanks in advance!

[Aardigspook]

Okay, it's actually fairly simple. Since you've got the total in cell K90, use this as the DV formula:

=$K$90<=$K$7

Since you've named K7 as 'BudgetTotal', you could use that in the formula instead. If you do the same with the K90 total (let's say Named as 'TotalSpend') then you'd get this:

=TotalSpend<=BudgetTotal

With both cells named, it doesn't matter if you add rows or columns - the DV formula will stay the same.

Hope that helps.

[Drainman]

Excellent! Thank you!! That has sorted it... - very helpful.

[Aardigspook]

You're welcome - thanks for the feedback and the rep.