Getting a list of Sheet Names by excluding certain Sheet Names instantenously..

Getting a list of Sheet Names by excluding certain Sheet Names instantenously..

Dear Forum,

I need to get the Sheet Names of all the Sheets except a few Sheets viz the Parent SHeet in which these Names have to be displayed and also 1 or 2 Additional Sheets..which are not required to be included such as Sheets which are required to store Data Validation Elements and other information but not meant for the specific operation of the Sheet Names Data to be added.

Example:

Total\100\105\List1\List2\141\32

Now as seen above there are 7 SHeets in all and from the 7 Sheets I want to actually work only from the Sheets which are 100\105\141\32 and this list needs to be shown in the Range from O2 till whatever "O"RowNo till SHeets are added..
So Now it will be just O2:O5 FOR 4 Sheets.

I want this List to appear instantly as and when any NEw SHeets are added or removed, but I also dont want to include 3 Sheets one is the Parent SHeet where the List is to appear that is the SHeet "Total" and the other Sheets are List1 and List2.

So, Preferably, I dont want to mention the Name "Total" as this Name if changed the code should not cease to work some Active.Sheet Fundaa can be used but for other Sheets i.e List1 and List2 I can explicitly mention them somewhere in the code which I get as HELP.

OR Have a List of NON-Required SHeets OR maintain a pattern that the Sheets which are not reuired will start as NON-List1 so we can tap the word "NON"

SO, please allow these changes to be incorporated in the VBA code so that in future too I can add any SHeets such as List3 and still not include them in the list and more importantly this has to be instantenous as there are certain NON_VBA approaches which give the List but do not update immediately..

Warm Regards
e4excel

perhaps: http://www.excelforum.com/excel-prog...=1#post2673188 will be of interest.

Dear DO,

Wow thats exactly what I needed however, I am not able to use it because of my limited VBA skills..

http://www.excelforum.com/excel-gene...le-sheets.html

Please find the code which I have changed but not followed completely..

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim ws As Worksheet
    With Sheets("Total")
        .Columns(17).Clear
        For Each ws In ThisWorkbook.Worksheets
            Select Case UCase(ws.Name)
                Case "Total", "List1", "List2"
                    'ignore
                Case Else
                    .Cells(.Rows.Count, "Q").End(xlUp).Offset(1).Value = ws.Name
            End Select
        Next ws
        .Cells(17).Delete xlUp
        'ThisWorkbook.Names.Add "_3DList", "='" & .Name & "'!" & Intersect(.UsedRange, .Columns(1)).Address
    End With
End Sub

I have commented the Name DEfine portion and then I did not quite follow whether the UCASE would be needed in my case?
ANd I want the actual Range to be in the COlumn O starting from 2 till no of relevant sheets..but used temporarily in COlumn Q

Actually I did not know of this WOrkbOOK Sheet Activate Event..!

New thing learned is of Multiple cases followed by a comma and then I did not understant the comment 'ignore , I mean is there something which needs to be written differently to actually ignore or thats good as it works in your sample file but in my effort I get all the SHeet Names including the Parent TOTal as well as List1,2 and others in the same order as their placement..

Warm REgards
e4excel

Originally Posted by e4excel

I did not quite follow whether the UCASE would be needed in my case?

VBA is Case Sensitive by default so if using a UCASE comparison (so as to remove case sensitivity) you must convert all values accordingly:

Select Case UCase(ws.Name)

    Case "TOTAL","LIST1","LIST2"

    Case Else

End Select

See also: Option Compare Text in VBE Help

Originally Posted by e4excel

I did not understant the comment 'ignore

It is merely a comment to reiterate the fact that for those select sheets nothing should happen (you do not want them in your sheet listing)

Originally Posted by e4excel

I have commented the Name DEfine portion...

Can I ask why ?

If as expected you intend to use this listing in your 3D SUMIF you need to ensure you detail only valid sheets.
Without a Dynamic Named Range should you modify the workbook (adding/removing sheets) you would in turn have to manually adjust the INDIRECT range in your 3D SUMIF Formula to pick up the revised sheet list range (which will change size).
Using a Dynamic Named Range negates this, the INDIRECT within the 3D SUMIF will utilise the Dynamic Named Range [_3DList] and thus the formula automatically adjusts as you modify workbook contents.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim ws As Worksheet
    Const c_col = "Q"
    With Sheets("Total")
        .Columns(c_col).Clear
        For Each ws In ThisWorkbook.Worksheets
            Select Case UCase(ws.Name)
                Case UCase(.Name), "LIST1", "LIST2"
                    'ignore
                Case Else
                    .Cells(.Rows.Count, c_col).End(xlUp).Offset(1).Value = ws.Name
            End Select
        Next ws
        ThisWorkbook.Names.Add "_3DList", "='" & .Name & "'!" & .Range(.Cells(2, c_col), .Cells(.Rows.Count, c_col).End(xlUp)).Address
    End With
End Sub

Originally Posted by e4excel

I want the actual Range to be in the COlumn O starting from 2 till no of relevant sheets..but used temporarily in COlumn Q

To revert to Column O change c_col Constant accordingly.

Dear DO,

The reason I had commented as before thinking of this query I had tried using a Formula for getting the SHeet Range using INDEX..

ShtRng= Total!$O$2:INDEX(Total!$O:$O,COUNTIF(Total!$O:$O,">"""),1)

and used the same..


First I tried putting the Manual values in the Column O and then used this formula to get the Defined Name for the SHeetRange.

ANd therefore I commented the Name as I wanted to try it with the Automatic Sheet List first..

NO the problem is After updating the code my file has gone in a long processsing...

I am opening and Re-opening the file and trying to remove the code as unable to do anything..

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 For Each Sh In Sheets
  If Val(Sh.Name) > 0 Then c01 = c01 & "|" & Sh.Name
 Next

 sn = Split(Mid(c01, 2), "|")
 Sheets("total").Cells(2, 15).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub

You can also use the workbook_open event:

Private Sub Workbook_Open()
 For Each Sh In Sheets
  If Val(Sh.Name) > 0 Then c01 = c01 & "|" & Sh.Name
 Next

 sn = Split(Mid(c01, 2), "|")
 Sheets("total").Cells(2, 15).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub

e4excel, I am afraid I have no desire to get involved in another 50 post thread

The code (event) provided previously does exactly as you requested; it will generate a physical sheet listing which can in turn be utilised within a 3D SUMIF and which will update as and when sheets are added, removed, renamed etc...

The dynamic named range that utilises this listing (in my code 3D List) is utilised subsequently via standard approach:

=SUMPRODUCT(SUMIF(INDIRECT("'"&_3DList&"'!A1:A10"),"x",INDIRECT("'"&_3DList&"'!B1:B10")))

In the attached (taken from previous thread) the INDIRECT utilises R1C1 notation for sake of extension.

If you are experiencing other issues (resulting from a multitude of pre-existing Volatile Arrays and SUMPRODUCTs) I am afraid that's an altogether different matter and not something that should be investigated on this thread.

Originally Posted by e4excel

there are 2 Defined Names which I dont see being used and therefore not able to understand their use

_test: as the name implies.

_3DListK:
in the original thread from which this is taken the OP asked to also see how they might store the sheet names as defined constants rather than as a range

I would reiterate the point regards using the defined name creation in the code... there's no need to add more overheard (to establish the named range) given it will always be affected by the Event itself.

Further, it will reduce risk of error - as is the case in your sample where ShtRng defintion is incorrect:

ShtRng: =Total!$O$2:INDEX(Total!$O:$O,COUNTA(Total!$O:$O),1)

Your INDEX will return 4 as there are 4 entries in that column - i.e. O2:O4 ... you must either adjust for blank header or use an alternate approach.

Originally Posted by e4excel

...if the Columns in the Sheets 100,141,32 etc if the Month Columns were not at the exact location there's no way to counter that

Assuming each sheet could differ then I would be inclined to modify the Sheet event to invoke the calculations via UDF calls or sub routine.

FWIW, attached is an alternative route which uses UDF to calculate.

Given you are updating the sheet list via the Workbook_SheetActivate Event I do not believe this UDF need be Volatile - were you updating the list manually it would given implicit precedents.
In this instance the act of rebuilding the Sheet Listing should flag all dependent UDFs as requiring recalculation and this will be done each time the result sheet is activated.
The caveat to this would be using Window Splits to view multiple sheets simultaneously - this would require the UDF be Volatile to see changes reflected real time
VERY LATE EDIT: and / or referencing "result" cells on other sheets which might be activated post changes but prior to result sheet being activated.

The UDF is below:

Function SUMIF3D(rngSheets As Range, rngMatrix As Range, vXCrit As Variant, vYCrit As Variant, _
                    Optional lngXPos As Long = 1, Optional lngYPos As Long = 1) As Variant
    
    'Declare Variables
    Dim rngWS As Range
    Dim dblTemp As Double
    Dim vXSum As Variant
     
    'need for below determined by whether or not sheet listing is updated with sheet activation; if yes then no (changing sheet list should flag UDFs as dirty)
    'Application.Volatile
    
    'Set Handler
    On Error GoTo Handler
    
    'modify criteria values for SUMIF as necessary
    If Val(vXCrit) = 0 Then
        vXCrit = Chr(34) & vXCrit & Chr(34)
    End If
    If Val(vYCrit) = 0 Then
        vYCrit = Chr(34) & vYCrit & Chr(34)
    End If
    
    'iterate worksheets to be aggregated
    For Each rngWS In rngSheets.Cells
    
        With Sheets(CStr(rngWS.Value))
        
            'validate column to sum exists
            vXSum = .Evaluate("MATCH(" & vYCrit & "," & rngMatrix.Rows(lngYPos).Address & ",0)")
            
            'perform SUMIF on this sheet and add to running total
            If IsNumeric(vXSum) Then
                dblTemp = dblTemp + .Evaluate("SUMIF(" & rngMatrix.Columns(lngXPos).Address & "," & vXCrit & "," & rngMatrix.Columns(vXSum).Address & ")")
            End If
            
        End With
        
    Next rngWS
    
    'set result
    SUMIF3D = dblTemp
    
Exit Function

    'Error Handler
Handler:
    SUMIF3D = "Invalid"

End Function

I have also modified the Event slightly

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim ws As Worksheet
    Dim xlCalc As XlCalculation
    On Error GoTo Handler
    Const c_col = "O"
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    With Sheets("Total")
        .Columns(c_col).Clear
        For Each ws In ThisWorkbook.Worksheets
            Select Case UCase(ws.Name)
                Case UCase(.Name), "LIST1", "LIST2"
                    'ignore
                Case Else
                    .Cells(.Rows.Count, c_col).End(xlUp).Offset(1).Value = ws.Name
            End Select
        Next ws
        ThisWorkbook.Names.Add "_3DList", "='" & .Name & "'!" & .Range(.Cells(2, c_col), .Cells(.Rows.Count, c_col).End(xlUp)).Address
    End With
ExitPoint:
    With Application
        .Calculation = xlCalc
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Exit Sub
    
Handler:
    MsgBox "Error Has Occurred" & vbLf & vbLf & _
            "Error Number: " & Err.Number & vbLf & vbLf & _
            "Error Desc.: " & Err.Description, _
            vbCritical, _
            "Fatal Error"
    Resume ExitPoint
    Exit Sub
End Sub

You will note in the sample that I have (re)moved columns around on the source sheets; I am also only using 2 Defined Names (one as created by the Event, the other based off Total sheet to determine matrix range).

The above will work as before re: sheet addition / removal / rename etc...

Before anyone should choose to critique / amend etc please bear in mind the above is merely a starting point for e4excel ... it is not meant as an all encompassing solution to the issue of 3D conditional aggregation.

808314_e4excel.xlsm

Originally Posted by e4excel

I had tried to un-succesfully to change the MOnth Column Location in each different Sheet

Not entirely clear to me what you mean by the above. If you modified the month header positions within the pre-defined [and consistent] matrix range then the UDF will automatically account for this and vary the column to sum on a sheet by sheet basis.

Given the sample file I attached illustrated the above concept I am assuming this is not what you mean. If the matrix is located in ranges unique to each sheet then the UDF as originally presented will cater for that. Were you to "name" the top left cell of each matrix on each sheet you could modify accordingly.