# Thread: Message to warn which sheets have a variance

1. ## Message to warn which sheets have a variance

I have a workbook with severals sheets with the text "variance" or "var' appearing in Col A and the value pertaining to variance or Var is in the in the same row as "variance" or "var'" (each sheet is different so the value could be Col C, D or E)

Where the value in the same row as variance or Var and is <> 0 , the message box must advise which sheets these are on

Your assistance in this regard is most appreciated

2. Howard

There is a little more information that needs to be obtained to make this work the way you want the first time:

1. What will be the trigger to make the message box appear, when the workbook is opened or perhaps, when the worksheet with the variance is activated?
2. Can the be more than one variance value in the workbook? Can there be more than one variance value on a sheet?
3. the Variance values will be in the same row as "Variance or "var" in columns C, D, or E. If the value is in column D, will columns C or E contain any data?
4. If there are more than one variance to report, do you want them listed all on one message box or separately?

Maud

3. Hi Maud

1) I would like a seperate sheet called "Variances" that will list sheet names that has a variance <> 0
2) there can only be one variance per sheet
3) There will only be one value in the same row as "Variance or "var"

Howard

4. In the separate sheet you could create a table of sheetnames. In A1 "sheet name", in B1 (column can be hidden) Var Row", In C1: "Vaiance"
In A2:A whatever, list the sheet names [your index yperlink macro from the other post) has code that can do this if desired, though ignore the INDEX and this sheet]
In B2 enter the formula:
=IF(ISNUMBER(MATCH("var",INDIRECT("'"&A2&"'!A:A"), 0)),MATCH("var",INDIRECT("'"&A2&"'!A:A"),0),MATCH( "variance",INDIRECT("'"&A2&"'!A:A"),0))

In C2 enter the formula:
=MAX(INDIRECT("'"&A2&"'!"&B2&":"&B2))
Copy B2:C2 down the columns

This will list the variance for each sheet

Steve
PPS If you do not want the intermediate calculation of Col B, you could add it to the calculation in C2:
=MAX(INDIRECT("'"&A2&"'!"&IF(ISNUMBER(MATCH("var", INDIRECT("'"&A2&"'!A:A"),0)),MATCH("var",INDIRECT( "'"&A2&"'!A:A"),0),MATCH("variance",INDIRECT("'"&A 2&"'!A:A"),0))&":"&IF(ISNUMBER(MATCH("var",INDIREC T("'"&A2&"'!A:A"),0)),MATCH("var",INDIRECT("'"&A2& "'!A:A"),0),MATCH("variance",INDIRECT("'"&A2&"'!A: A"),0))))

And deleted column B completely...

5. Hi Steve

Thanks for the fancy formula. It does the trick

Howard

6. Howard,

Here is also a VBA solution for you consideration. Placed in the new Variance Worksheet module, this code will update each time the sheet is accessed.

TaxTemplate1.png

Code:
```Private Sub Worksheet_Activate()
On Error Resume Next
Application.ScreenUpdating = False
'-----------------------------------------------
'DELARE AND SET VARIABLES
Dim Var As String
Row = 4
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A4:F" & LastRow).ClearContents
'-----------------------------------------------
'FIND VARIANCE ON SHEETS AND SEND TO VARIANCE SHEET
For i = 1 To Worksheets.Count
If Worksheets(i).Name = ActiveSheet.Name Then GoTo Skip 'IF VARIANCE SHEET
If Var = "" Then
End If
If Var <> "" Then
For j = 1 To 5  'SEARCH COLUMNS ON VARIANCE ROW FOR VALUE
variance = Worksheets(i).Range(Var).Offset(0, j).Value
If variance <> "" And variance <> "-" And variance <> 0 Then
Cells(Row, 1) = Worksheets(i).Name 'PRINT SHEET
Cells(Row, 2) = Worksheets(i).Range(Var).Offset(0, j).Value 'PRINT VALUE
Row = Row + 1
End If
Next j
End If
Skip:
Next i
Application.ScreenUpdating = True
End Sub```

7. Hi Maudibe

Thanks for the code, much appreciated

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•