Dynamic Text List, ignoring "0"

[TheSubject]

I am making a weekly summary file for my bar.
End of each week I "move or copy" the 7 daily cash up sheets into the weekly sheet, named by the days of the week.
In the weekly sheet, I have the cash summary page, which has a column of various headers (denomination, pdq, amex, discrepancies, etc) 7 columns for each day and a ninth column for totals.

On the next page I now want to summarise my petty cash.

Each day's sheets has 8 rows for petty cash input, but for the most part only one or two rows are used.
I want to now have each "list" put into one list, so ranging from just a couple of entries to lots of them, in date order, but ignoring blank entries.

It's worth noting that there may be duplicate purchases - say a really hot week where we go through all our ice, my freezer capacity is minimal so we may have to buy ice several days in the week, meaning the list can't be a unique list.

If I just say =Tuesday!B23 (for instance) and theres no purchase there it comes up with "0", so I need a kind of:

=Range(sunday!b22-b29, if no more data, then monday!b22-29, if no more.....)

any ideas?

Thanks in advance

 

[pbornemeier]

It probably could be done with formulas, but I have no idea how to do that.

Add the following code to a standard module.
The workbook with the weekday worksheets must be the active workbook when the code is run.

Option Explicit

Sub CreatePettyCashSummaryList()
    'Examine B22:B29 on each of the weekday worksheets and copy any entries to the summary list
    'Optionally include the day of the expense at the start of the entry e.g.: (Wed)
    'https://www.mrexcel.com/forum/excel-questions/1100556-dynamic-text-list-ignoring-0-a.html
    
    'Modify the next 3 lines to meet your requirements
    Const sTargetWorksheetName As String = "Petty Cash"     'Name of worksheet where summary list will go
    Const sStartAddress As String = "C3"                    'Cell to contain first item in summary list
    Const bIncludeDay As Boolean = True                     'True If you want day of purchase in summary list
    
    'Other variables
    Dim lEntryCheck As Long
    Dim aryWeekdays() As Variant, lDayIndex As Long
    Dim lNextWriteRow As Long, lWriteColumn As Long
    Dim sWksName As String
    Dim lEntryIndex As Long
    Dim sEntry As String
    Dim rngSummaryListRange As Range
    
    aryWeekdays = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
    lNextWriteRow = Range(sStartAddress).Row
    lWriteColumn = Range(sStartAddress).Column
    
    'Is there any filled cells in the summary list destination?
    With Worksheets(sTargetWorksheetName)
        Set rngSummaryListRange = .Range(.Cells(lNextWriteRow, lWriteColumn), .Cells(lNextWriteRow + 55, lWriteColumn))
        lEntryCheck = Application.WorksheetFunction.CountA(rngSummaryListRange)
    End With
    'If so, delete them and continue or quit
    If lEntryCheck > 0 Then
        Application.Goto rngSummaryListRange, scroll:=True
        Select Case MsgBox("Entries exist in " & sTargetWorksheetName & "!" & rngSummaryListRange.Address & vbLf & vbLf & _
            "This program will erase that data and update that area with the current entries from the weekday worksheets." & vbLf & vbLf & _
            "    Yes" & vbTab & " to automatically delete the entries and create new list." & vbLf & _
            "    No" & vbTab & " to stop the program and allow you to examine that area.", vbYesNo + vbDefaultButton1, "Summary Area Already Has Entries")
        Case vbYes
            rngSummaryListRange.Cells.ClearContents
        Case Else
            GoTo End_Sub
        End Select
    End If
    
    'Create new summary list
    For lDayIndex = 0 To 6
        'Examine each day worksheet
        sWksName = aryWeekdays(lDayIndex)
        With Worksheets(sWksName)
            'Examine each cell in B22-B29 in the worksheet
            For lEntryIndex = 22 To 29
                'Does the cell have text in it?
                If .Cells(lEntryIndex, "B").Value <> vbNullString Then
                    'Yes it does
                    'Build entry for summary list with optional day at front
                    sEntry = IIf(bIncludeDay, "(" & Left(sWksName, 3) & ") ", "") & _
                        .Cells(lEntryIndex, "B").Value
                    'Copy it to the next blank spot in summary list
                    Worksheets(sTargetWorksheetName).Cells(lNextWriteRow, lWriteColumn).Value = sEntry
                    'Increment the write row counter
                    lNextWriteRow = lNextWriteRow + 1
                End If
            Next
        End With
    Next
    
End_Sub:

End Sub