Populate a table with a dynamic range

[Guest]

Jeff wrote:

I would like to be able to create a dynamic range to pull the last seven days
of data from spreadsheet and place it in a different table in the same
spreadsheet.

I don't want a sum of the range or average or anything. I just want a range
I can put in a table that is updated daily when I update the oringinal table.

I am familiar with dynamic ranges,
=OFFSET(Master!$I$1,COUNTA(Master!$I:$I)-7,0,7,1)

I use them in several charts, just not sure how to transfer that same
functionality to a table.

 

[Arvi Laanemets]

Hi

There are several solutions depending on your original data layout.

When your original data are sorted by date, then you can define the named
range as 'sliding window' on your table. Something like (on fly):

=OFFSET(Master!$I$1,MATCH(TODAY()-7,Master!$I:$I,0),,COUNTA(Master!$I:$I)-MATCH(TODAY()-7,Master!$I:$I,0),5)
(I'm assuming here, that datea are in column I, and that the table is in
columns I:M, and the name of range will be Last7)

In another table, to retrieve data you use formula like
=IF(ISERROR(INDEX(Last7,RowN,ColN)),"",INDEX(Last7,RowN,ColN))
, where RowN is the number of row in 'sliding window', and ColN is the
number of column, you want the data displayed from.

When your data aren't sorted by date, then you can add a hidden column as
leftmost in your table, with some formula there, which counts rows for last
7 days, and returns an empty string for rest of rows. In other table, you
use VLOOKUP to return values from original table, where hidden column
contains values 1, 2, 3, etc. No named ranges are needed with this solution.

 

[Guest]

Not sure I understand this

is the data being retrieved in a single cell or in 7 different cells?

I can grab individual data cells
=OFFSET(Master!$C$1,COUNTA(Master!$C:$C)-2,0,1,1)

this will give me the data from the second to the last entry in column C

I was thinking there was a way to define the last seven days
=OFFSET(Master!$C$1,COUNTA(Master!$C:$C)-7,0,7,1) like I have here but
instead of displaying it in a chart display it in a table.

perhaps I am being thick but I don't understand how what you are doing will
take the data I am grabbing and populate another table.

Thanks

Jeff

 

[Arvi Laanemets]

Hi

OK, let's have an example. In a new workbook, create a sheet Data with a
table headers in row 1:
Date, Value1, Value2

Enter some rows into this table - with dates from 1st of this month until
today in Date column, and any values in other columns. You can leave some
dates out, or you can have several rows with same date, but keep them
ordered. And at least 1st row in table must have date older than 7 days from
current one.

Define a named range Data
=OFFSET(Data!$A$1,MATCH(TODAY()-7,Data!$A:$A,1),,COUNTA(Data!$A:$A)-MATCH(TODAY()-7,Data!$A:$A,1),3)

On another sheet, enter same column headings into 1st row. Into A2 enter the
formula
=IF(ISERROR(INDEX(Data,ROW()-1,COLUMN())),"",INDEX(Data,ROW()-1,COLUMN()))

Copy the formula to range A2:C2, and format cell A2 as date in any valid
date format you like. Copy A2:C2 down for any amount of rows you think
reasonable. There you are!