Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts

    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. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks for the reply

    I had a re-think after receiving your reply and have also attached sample data

    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
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
    PS the items could be added to your index page..
    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...
    Last edited by sdckapr; 2013-11-10 at 07:14.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the fancy formula. It does the trick

    Howard

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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
        Var = Worksheets(i).Columns(1).Find(what:="Variance").Address 'FIND VARIANCE ADDRESS
        If Var = "" Then
            Var = Worksheets(i).Columns(1).Find(what:="Var").Address 'FIND VARIANCE ADDRESS
        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
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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
  •