Dynamic table with different starting point

[sowerbyp]

I wish to create a dynamic range that works backwards from the last row of the data to a place higher up that is 36 whole weeks long. The table is made up of stock counts done on different dates. The dates are in ascending order however they are not done every week.

Is it possible to create a dynamic table from data pasted onto the end of a list of dates already in place and work back up the rows to the date that is the 36th whole week which is the starting point for the dynamic table to the end of the new pasted data which will then form my new table for my pivot table.

Within column A there will be multiple entries of the same date spread over the last months.

Any ideas?

 

[RasGhul]

Can you post some sample data?

 

[sowerbyp]

dates are in column A and consists of data over 2 years that I keep adding to. I would like to be able to have a rolling 36 weeks worth of data that is selected dynamically as I add more data.

22/01/19 5200026506 PLASTER HOODS (reels 50) 2400 2400 2019.09 0 28/01/19 5200404223 CELLULOSE ETHER TYLOSE MH 60005 P6 40306.29 40275 2019.09 -1 28/01/19 5200404224 CELLULOSE ETHER (U/COAT PLASTER) TYLOSE MH 60001 P4 7400.284 7401 2019.09 1 01/02/19 5200404242 CARDBOARD 1100 X 1260 BD/43 11270.232 11271 2019.09 1 03/02/19 5200404257 PLASTER PALLETS 1200 X 1000 605 605 2019.09 0 08/02/19 5200404300 GROUND BLAST FURNACE SLAG (CEMSAVE) 0 0 2019.09 0 11/02/19 5200404304 HYDRATED LIME BULK TANKER 9888.587 9880 2019.09 -1 15/02/19 5200404313 DURAFINISH PAPER BAG (reels) 11408 11408 2019.09 0 28/02/19 5200404325 KERATIN (TYPE A) 1006.73 1000 2019.09 -1 28/02/19 5200404334 PERLITE ORE 106307.22 108000 2019.09 1 28/02/19 5200404389 SILIPON FOAMING AGENT RN6031 773.287 773 2019.09 -1 28/02/19 5200404409 THISTLE UNIVERSAL ONE COAT BAG (25KG) reels 24200 24200 2019.09 0 <colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[mikerickson]

I think this function will do what you want. (assuming that the values in column A are Excel serial dates)

Function rngPreviousWeeks(Optional WeeksCount As Long = 36) As Range
    Dim NewCell As Range, OldCell As Range
    Dim i As Long
    
    With Range("A:A")
    
        Set NewCell = .Find("?*", after:=.Cells(1, 1), LookIn:=xlValues, lookAt:=xlWhole, searchdirection:=xlPrevious)
        
        For i = NewCell.Row To 1 Step -1
            With .Cells(i, 1)
                If IsDate(.Value) Then
                    Set OldCell = .Cells
                    If (WeeksCount * 7) < (NewCell.Value - OldCell.Value) Then
                        Set OldCell = OldCell.Offset(1, 0)
                        Exit For
                    End If
                End If
            End With
        Next i
    End With
    
    Set rngPreviousWeeks = Range(OldCell, NewCell)
End Function

 

[sowerbyp]

thanks Mike. How do I use this function? sorry I'm a bit of a newbie

I think this function will do what you want. (assuming that the values in column A are Excel serial dates)

Function rngPreviousWeeks(Optional WeeksCount As Long = 36) As Range
    Dim NewCell As Range, OldCell As Range
    Dim i As Long
    
    With Range("A:A")
    
        Set NewCell = .Find("?*", after:=.Cells(1, 1), LookIn:=xlValues, lookAt:=xlWhole, searchdirection:=xlPrevious)
        
        For i = NewCell.Row To 1 Step -1
            With .Cells(i, 1)
                If IsDate(.Value) Then
                    Set OldCell = .Cells
                    If (WeeksCount * 7) < (NewCell.Value - OldCell.Value) Then
                        Set OldCell = OldCell.Offset(1, 0)
                        Exit For
                    End If
                End If
            End With
        Next i
    End With
    
    Set rngPreviousWeeks = Range(OldCell, NewCell)
End Function

 

[mikerickson]

I'm not sure how you were planning on using range.

To find the range of the most recent 36 weeks (assuming the dates are in column A, sorted oldest to newest)

If you put the UDF in a module. This sub is an example of how it could be used.

Sub Test()
    MsgBox "The last 36 weeks of entries are in " & rngPreveiousWeeks.Address
End Sub

Function rngPreviousWeeks(Optional WeeksCount As Long = 36) As Range
    Dim NewCell As Range, OldCell As Range
    Dim i As Long
    
    With Range("A:A")
    
        Set NewCell = .Find("?*", after:=.Cells(1, 1), LookIn:=xlValues, lookAt:=xlWhole, searchdirection:=xlPrevious)
        
        For i = NewCell.Row To 1 Step -1
            With .Cells(i, 1)
                If IsDate(.Value) Then
                    Set OldCell = .Cells
                    If (WeeksCount * 7) < (NewCell.Value - OldCell.Value) Then
                        Set OldCell = OldCell.Offset(1, 0)
                        Exit For
                    End If
                End If
            End With
        Next i
    End With
    
    Set rngPreviousWeeks = Range(OldCell, NewCell)
End Function

Note that you can pass the number of weeks as an argument for the function.

 

[mikerickson]

If you want a formula based solution,

=INDEX(A:A, MATCH(9E+99,A:A), 1) is the last cell in column A that holds a date

=INDEX(A:A, MATCH(MAX(A:A)-36*7, A:A) + 1, 1) is the first cell in the holds a date after the last date minus 36 weeks, so

INDEX(A:A, MATCH(9E+99,A:A), 1) : INDEX(A:A, MATCH(MAX(A:A)-36*7, A:A) + 1, 1) is the range of those 36 weeks worth of cells.

 

[sowerbyp]

I actually want a named range that I can create a Pivot Table from that refers to the last 36 weeks worth of data. I think your function will work as I just need to run the Sub after adding new data.

Your function has highlighted a problem for me in that weeks 31 & 40 are missing from my dates as we did not count stock that week so I am missing some data.

Are we able to make your function adjust for any missing weeks and still give me 36 weeks worth of data regardless of dates?

Thanks for the help so far Mike

I'm not sure how you were planning on using range.

To find the range of the most recent 36 weeks (assuming the dates are in column A, sorted oldest to newest)

If you put the UDF in a module. This sub is an example of how it could be used.

Sub Test()
    MsgBox "The last 36 weeks of entries are in " & rngPreveiousWeeks.Address
End Sub

Function rngPreviousWeeks(Optional WeeksCount As Long = 36) As Range
    Dim NewCell As Range, OldCell As Range
    Dim i As Long
    
    With Range("A:A")
    
        Set NewCell = .Find("?*", after:=.Cells(1, 1), LookIn:=xlValues, lookAt:=xlWhole, searchdirection:=xlPrevious)
        
        For i = NewCell.Row To 1 Step -1
            With .Cells(i, 1)
                If IsDate(.Value) Then
                    Set OldCell = .Cells
                    If (WeeksCount * 7) < (NewCell.Value - OldCell.Value) Then
                        Set OldCell = OldCell.Offset(1, 0)
                        Exit For
                    End If
                End If
            End With
        Next i
    End With
    
    Set rngPreviousWeeks = Range(OldCell, NewCell)
End Function

Note that you can pass the number of weeks as an argument for the function.

 

[sowerbyp]

Thanks Mike. Any ideas on how to cope with none continuous weeks as there are dates missing in my data?

The formula gets me back to the 12/06/17 (wk 25) however I'm missing 2 weeks of data so it needs to go back to wk 23 to give me 36 weeks worth of data. I also need to be back to the Monday of that week too?

Apologies for all the questions

If you want a formula based solution,

=INDEX(A:A, MATCH(9E+99,A:A), 1) is the last cell in column A that holds a date

=INDEX(A:A, MATCH(MAX(A:A)-36*7, A:A) + 1, 1) is the first cell in the holds a date after the last date minus 36 weeks, so

INDEX(A:A, MATCH(9E+99,A:A), 1) : INDEX(A:A, MATCH(MAX(A:A)-36*7, A:A) + 1, 1) is the range of those 36 weeks worth of cells.