Help with MsgBox in VBA or Data Validation

[wilkisa]

Here's my scenario: Range BH6:BH35, Sheet name APR 2013
Cell BH6 is associated with the date of 4/1/2013 in cell A6, and so on through the month of April to cell A35.
The formula is cell BH6 is:
=IF(SUM(B6,G6,L6,Q6,V6,AA6,AF6,AK6,AP6,AU6,AZ6)>BG6,SUM(D6,I6,N6,S6,X6,AC6,AH6,AM6,AR6,AW6,BB6),0)

I have tried Data Validation to pop up a warning box if the value of the formula exceeds the value in BG6 but I didn't get it to pop up. I then tried it if the formula exceeds a fixed number of 205 but I can't seem to make it work if it is evaluating the results of a formula.

I also tried creating a macro to have a msgbox pop up if the value exceeds the value in BG6 (205) but I can't seem to make it follow down each day, changing to the next cell in BH.

Here's the wrench in the works: I need this macro to work in the same cell range on every sheet for the entire year and we change from month to month.

Help! I think Data Validation should work, which would make this much easier, but I'll take any help I can get at this point.

 

[Jonmo1]

For the data validation to work with the result of a formula, you have to put it in the cell(s) that you enter values to change that formula.

So the data validation would have to go in each of the cells involved in the sum (B6 G6 L6 etc..)

 

[wilkisa]

Hello, JonMo, and thanks for replying. I don't know what is wrong but I cannot see your reply unless I go into Printable View. Regardless, only if the sum of all of the values is greater than 205 do I want the warning box. I can't put a DV in each cell that is being summed because each value will be much less than 205.

Is there a macro that will run automatically when the sum per each day reaches 206 or more?

 

[Jonmo1]

You put the same data validation formula in each of the cells in the sum.
You're not testing each cell for > 205.
You're validating each cell if the SUM of all cells > 205

Also, you'll need to add $'s to the original formula to make sure the correct references are included..

so select ALL the cells at once, B6 G6 L6 Q6 V6 AA6 AF6 AK6 AP6 AU6 and AZ6
In data validation, use custom and enter the formula as
=SUM($B6,$G6,$L6,$Q6,$V6,$AA6,$AF6,$AK6,$AP6,$AU6,$AZ6)>=205
or
=SUM($B6,$G6,$L6,$Q6,$V6,$AA6,$AF6,$AK6,$AP6,$AU6,$AZ6)>=$BG6
Click OK

so now each time you change B6, it checks the whole sum. If you change g6 or l6 or whatever, it checks the whole sum.

then you can also fill those down to apply the validation to rows below, the 6's will incriment to 7's and 8's etc.

 

[wilkisa]

Thanks, JonMo, I think I was being a little dense earlier. Now I have a related problem. This works great but when I move to the next day, I still get the popup for the previous day. How do stop the message from appearing for previous data? I only want to see it for current data.

 

[Jonmo1]

I don't think I understand "move to the next day"

Are you talking about having the data validition filled down to row 7, then row 8 ??

 

[wilkisa]

I was able to fill it down but if on 4/1/2013 we were at 210, the message kicks off just fine. However, when we start filling in data for 4/2/2013, we are still getting the message from 4/1. I just don't know how to turn it off without deleting all of them from 4/1.

 

[Jonmo1]

Sorry, I can't visualize the issue.

Try using the Excel Jeanie (see my signature for a link) to post an example of your sheet

 

[wilkisa]

JonMo, believe it or not, your website is blocked by our internet watchdog. Would you allow me to send a dummy version of my workbook to you? I won't be able to do that until tomorrow morning, however.

 

[Jonmo1]

OK, I got your email and see the problem..

We have to reverse our thinking of what Data validation does.

Data Validation doesn't so much "Restrict" you from entering a wrong value..
Instead, it thinks about what value it "Allows" you to enter.

In other words, if you only want the cell to contain numbers from 1 to 10.
It doesn't say "is this value greater than 10?"
Instead, it says "is this value between 1 and 10?"

It essentially looks for a TRUE result from the formula you put in.
If the formula in Data Validation results in TRUE considering the value you're trying to enter, then it allows you to enter that value.
Otherwise, it does not allow you to enter that value.

So we want to "Allow" values to be entered so long as that sum formula is LESS than 205.
so we need to change

=SUM($B6,$G6,$L6,$Q6,$V6,$AA6,$AF6,$AK6,$AP6,$AU6,$AZ6)>205
to
=SUM($B6,$G6,$L6,$Q6,$V6,$AA6,$AF6,$AK6,$AP6,$AU6,$AZ6)<205
or perhaps you want less than OR equal to.
=SUM($B6,$G6,$L6,$Q6,$V6,$AA6,$AF6,$AK6,$AP6,$AU6,$AZ6)<=205