create a dymamic range for printing

[Guest]

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 tab in the same
spreadsheet.


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 spread sheet.

Thanks

Jeff

 

[Don Guillett]

Insert>name>define>name it as desired or use Print_Range>in the refers to
box type in your formula. If on the sheet desired, Excel will fill in the
sheet name for you. test by using f5 and type in the name to goto it.

Don Guillett
SalesAid Software
(e-mail address removed)

 

[Guest]

not sure that would work for what I want,

and I don't think I asked my question well.

I have a dynamic range that expands as I add data to it. I have figured out
how to add the variable range to a chart so only the last 7 days are shown,
I would like to be able to pull out the last seven days and display it in a
table. I could probalby name a range for each day but I am hoping for
something a little elegant.

Thanks

 

[Dave Peterson]

I inserted another tab in the workbook.

Then I selected 7 vertical cells (A1:A7 or J100:J106 or whatever).

Then I pasted that formula into the formula bar.
But instead of using enter, I hit shift-ctrl-enter -- since I wanted an array of
values brought back.

 

[Guest]

so as I add data that will automatically add the new data and drop off the
old data?

 

[Dave Peterson]

What happened when you tried it?

so as I add data that will automatically add the new data and drop off the
old data?

 

[Don Guillett]

I just tested this defined name formula and it worked just fine
=OFFSET(Sheet2!$I$1,COUNTA(Sheet2!$I:$I)-7,0,7)

Sub printdefinedname() 'print last seven
Range("lastseven").PrintPreview
End Sub

Sub copydefinedname()'copy last seven to another sheet
Range("lastseven").Copy Sheets("sheet9").Range("i1")
End Sub