Dynamic table with different starting point

S

sowerbyp

New Member
Joined
Mar 1, 2019
Messages
5
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?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1400"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]22/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200026506[/TD]
[TD]PLASTER HOODS (reels 50)[/TD]
[TD][/TD]
[TD]2400[/TD]
[TD]2400[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]28/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404223[/TD]
[TD]CELLULOSE ETHER TYLOSE MH 60005 P6[/TD]
[TD][/TD]
[TD]40306.29[/TD]
[TD]40275[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]28/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404224[/TD]
[TD]CELLULOSE ETHER (U/COAT PLASTER) TYLOSE MH 60001 P4[/TD]
[TD][/TD]
[TD]7400.284[/TD]
[TD]7401[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]01/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404242[/TD]
[TD]CARDBOARD 1100 X 1260 BD/43 [/TD]
[TD][/TD]
[TD]11270.232[/TD]
[TD]11271[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]03/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404257[/TD]
[TD]PLASTER PALLETS 1200 X 1000[/TD]
[TD][/TD]
[TD]605[/TD]
[TD]605[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]08/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404300[/TD]
[TD]GROUND BLAST FURNACE SLAG (CEMSAVE)[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]11/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404304[/TD]
[TD]HYDRATED LIME BULK TANKER[/TD]
[TD][/TD]
[TD]9888.587[/TD]
[TD]9880[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]15/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404313[/TD]
[TD]DURAFINISH PAPER BAG (reels)[/TD]
[TD][/TD]
[TD]11408[/TD]
[TD]11408[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]28/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404325[/TD]
[TD]KERATIN (TYPE A) [/TD]
[TD][/TD]
[TD]1006.73[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]28/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404334[/TD]
[TD]PERLITE ORE[/TD]
[TD][/TD]
[TD]106307.22[/TD]
[TD]108000[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]28/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404389[/TD]
[TD]SILIPON FOAMING AGENT RN6031[/TD]
[TD][/TD]
[TD]773.287[/TD]
[TD]773[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]28/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5200404409[/TD]
[TD]THISTLE UNIVERSAL ONE COAT BAG (25KG) reels[/TD]
[TD][/TD]
[TD]24200[/TD]
[TD]24200[/TD]
[TD][/TD]
[TD="align: right"]2019.09[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I think this function will do what you want. (assuming that the values in column A are Excel serial dates)
Code: 
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
 
Upvote 0
thanks Mike. How do I use this function? sorry I'm a bit of a newbie :)

mikerickson said:
I think this function will do what you want. (assuming that the values in column A are Excel serial dates)
Code: 
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
Click to expand...
 
Upvote 0
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.

Code: 
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.
 
Upvote 0
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.
 
Upvote 0
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 :-)



mikerickson said:
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.

Code: 
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.
Click to expand...
 
Upvote 0
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 :-)


mikerickson said:
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Help auto filtering dynamic data table
Replies
1
Views
199
russelldt
R
N
  • Question Question
Year to Date last Year in a Pivot Table
Replies
1
Views
338
awoohaw
awoohaw
Z
  • Question Question
Sum Sales Values by Dates over different periods
Replies
6
Views
520
RobP
R
N
  • Question Question
2-step Dynamic Ranges and Charts
Replies
0
Views
1K
Nick Loughton
N
E
  • Question Question
Dynamic small function
Replies
1
Views
504
KRice
K

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top