Warning Message for non-zero check figures

[DerpDerp24]

Hi,

I have a databook with a large number of tabs that we use check figures to ensure consistency from tab to tab. These check figures are just a simple sum function that also subtracts from another tab's subtotal and when everything is working right, should sum to zero. For example, a check figure might look like:

=sum(A1:A10)-sum('Other tab'!A20)

Currently, we have those check figures in yellow throughout the workbook but the issue is that we have to be actively looking at the check figure in order for us to notice there is an issue. I don't believe data validation will work because it only works on input values-- not values resulting from a formula, but surely there is some sort of pop-up warning message I can create that will notify us when a check figure is now non-zero as we are tinkering with the databook?

 

[jmacleary]

Hello and welcome. You could use conditional formatting of each check figure to change to red when its non-zero.

 

[DerpDerp24]

Hello and welcome. You could use conditional formatting of each check figure to change to red when its non-zero.

Thanks! Been a lurker for a while, but this question has finally gotten me in the door.

Yeah, I've considered conditional formatting as you described, but with how large each tab is in terms of columns and rows, it'd still be an issue of having our window in the right spot of the spreadsheet to be able to identify that the check figure is off with conditional formatting. That is why I'm hoping a warning pop-up is possible.

 

[jmacleary]

OK. It would be possible to create some VBA to do what you want, but depending on how often the values go non-zero wouldnt a pop-up be annoying after a while? What about freezing the top row (if it isnt already), and use conditional formatting to change the colour of the row if any check goes non-zero? Without seeing an example of your sheet its difficult to be more specific.

 

[sykes]

This needs to go into the Workbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "WS13" And Target.Address = "$D$3" And Target.Value <> 0 Then MsgBox "Sheet " & Sh.Name & " Checkcell " & Target.Address & " in error!"
End Sub

It'll fire each time a change is made anywhere in a workbook, but so quickly, you shouldn't notice - unless it finds an error, of course!!

You'll need to replace "WS13" with the sheet name where your check cell resides, and also "$D$3" with the reference to your actual check cell.
Also, you'll need to adapt it, adding each sheet's name which contains checkcells, and each checkcell's address.
If you only have a few sheets/checkcells, consider putting it into each sheet's code module instead, and adapt accordingly. That's keep down the number of times the code fires.

 

[sykes]

@DerpDerp24 Did this work for you?

 

[DerpDerp24]

OK. It would be possible to create some VBA to do what you want, but depending on how often the values go non-zero wouldnt a pop-up be annoying after a while? What about freezing the top row (if it isnt already), and use conditional formatting to change the colour of the row if any check goes non-zero? Without seeing an example of your sheet its difficult to be more specific.

This worked! I appreciate yours and the other commenter's suggestions. If I were more familiar with VBA I may have opted for his solution but I think given the setup of my databook, a frozen row at the top with the auto checks is preferable. Thanks again everyone!

 

[sykes]

Thanks for the feedback.
You can always give the VBA a go, if you fancy it, or if John's solution doesn't work out for you. Happy to talk you through it, if necessary.