Excel - Charting Last Records 3

[Hillary]

I have an Excel file that is appended to everyday. I would like my graph to always graph the last 20 records without having to manually change the range. Is there a way to do this?

Thanks,



Hillary

 

[SkipVought]

Hi,

You can use the OFFSET function to dynamically define each series and category range...

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-20,0,20,1)

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Hillary]

I want to fully understand the formula. In the formula what do the last four numbers represent? (-20,0,20,1)

Thanks,

Hillary

 

[SkipVought]

you could make this a bit more flexible by designating a cell to hold the number or rows to plot.

then enter expression like this (my cell was E1)

=OFFSET(Sheet3!$A$1,COUNTA(Sheet3!$A:$A)-Sheet3!$E$1,0,Sheet3!$E$1,1)

BTW, whatever you NAMED each of these ranges (I used rName and rAmount), use that name in the SourceData/Series - Category labels and values such as this...

=Sheet3!rName

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jdhilljr]

Hillary,

These are very good suggestions and will work the only thing I don't like about them is it is not easy to access the prior data. Try this link and see if this might work for you. You can scroll through the data as you like.

http://www.procollectortips.com/graph.xls

Jim

 

[SkipVought]

Did you read the Help for OFFSET?

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-20,0,20,1)

COUNTA(Sheet1!$A:$A)-20 - counts the number of values less 20. this is a row offset from Sheet1!$A$1
0 - no column offset
20 - the row range
1 - the column range

jdhilljr mentioned that you can't see previous values. Well if you understand how OFFSET works, you will see that you could reference a cell for the row offset.

So I could name a cell called Off to store the row offset and another called Rws to store the number of rows I want to plot and then my offset expression would be...

=OFFSET(Sheet1!$A$1,Off,0,Rws,1)

By varying the value in Off (like with a spinner or scrollbar) I can make the chart plot slide with the Off reference.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jdhilljr]

Skip,

I had to give you a star for that one. I think I can incorporate the OFFSET into a lot of things I do. If it will do a column COUNTA along with a row count it will save me a lot of headaches.

Thanks,
Jim

 

[SkipVought]

jd,

Actually, I have just recently started using the OFFSET function to define dynamic ranges for source data for Pivot tables and column/row ranges for chart series.

Pretty powerful function!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MightyPup]

I use these extensively. I use the set the number of months to graph with a cerll reference. But I can also specify by days by combining todays date minus how many days I want. For instance, my graphs will automatically update as the month changes, or I can tell it to way X number of days before updating. Thank God for offset and defined ranges!

 

[MightyPup]

I use these extensively. I use the set the number of months to graph with a cerll reference. But I can also specify by days by combining todays date minus how many days I want. For instance, my graphs will automatically update as the month changes, or I can tell it to wait X number of days before updating. Thank God for offset and defined ranges!

 

[SkipVought]

MightyPup,

I have used Named Ranges for years, but I always used the worksheet_Change event and a macro that used names in the top row. I still use this technique with table having many columns.

But just recently discovered the power of the OFFSET function HUAH!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com