Set up a bar chart to always show only the last 20 rows? Alternatively OFFSET?

Hello,

create range names that return the last 20 rows of your data dynamically, then plug the range names into the charts.

Jon Peltier has a detailed step by step tutorial here

https://peltiertech.com/Excel/Charts...tml#DynoLast12

Pipe up if you need more help.

So, I tried that method:

https://peltiertech.com/Excel/Charts/DynamicLast12.html

I think maybe I was able to create the named ranges correctly.

But, I can't seem to be able to create the chart...

Attaching updated workbook.


Maybe it will be easier when I understand it completely and one can use copy and paste, but I can't help wonder if there's an easier way to accomplish this? At least if I don't want a dynamic lookback period, but say fixed at 30 days? The reason being that I need to do this on a lot of charts...

Thanks in advance for all help.

Elijah

Hello,

you have successfully created the three range names and they point to the last 30 rows of your data. You don't seem to have a range name for the chart axis, though. Which column do you want to show in the axis?

I typically create the first range name on the column for the chart labels, which in most cases is to the left of the numbers, then use a simple offset from the labels column to the data series columns.

Then create a chart of your choice and replace the Axis labels range and the Series range definitions with the range names.

I guess you want the Date as the chart axis, so you can create a name "chtLabels" with the formula

=OFFSET(chtCats,0,-15)

When you edit the chart source to put in the range names, you need to enter the range names preceded by a sheet name. So, type
=FilteredData!chtLabels for the chart labels and =FilteredData!chtCats for the series in column Q.

For some reason, the range names chtValA and chtValB throw errors when I try to put them into the chart. I renamed them to chtSeriesA and chtSeriesB and now it works. See attached.

Hey, teylyn,

Great! Thanks a lot!

Originally Posted by teylyn

Hello,

you have successfully created the three range names and they point to the last 30 rows of your data. You don't seem to have a range name for the chart axis, though. Which column do you want to show in the axis?

I don't really need an axis, although date is okay.

When I created this chart originally, I just selected the data in the column and it returned 1, 2, 3, 4, ... etc on the axis. That's okay for me.

However, it shouldn't be the LAST 30 rows of data, but the FIRST 30 rows of data. Meaning row 7 to 36.

Maybe my English could be better as I used both the word "Last" and also "Top 30" rows. But I meant top rows...

My data sheet is updated at the top so I'm basically interested in the newest data which starts from the top. So it should be the other way around than it is now.

Can you help?

Originally Posted by teylyn

Which column do you want to show in the axis?

I'm basically interested in creating charts for almost all column listed there, but I want one column per chart only. So basically 10 charts or so. But it will be in another workbook. This one is just for testing.

Originally Posted by teylyn

For some reason, the range names chtValA and chtValB throw errors when I try to put them into the chart. I renamed them to chtSeriesA and chtSeriesB and now it works. See attached.

That was where I got errors also, I think. Did you only change the range name? Formula was okay?

Thanks again.

Elijah

Hi,

I only changed the range names, not the formulas. Adding new data at the top is unusual and might create problems with formulas. But if you want to do it that way, the range name formula will be very easy. Understand how Offset works

=Offset(startrange,row offset, column offset, height, width)

If you want to plot from row 2 to row 32, the you can use

=Offset(A1,1,0,30,1)

In words: Starting from A1, go 1 row down, stay in the same column, include 30 rows and 1 column wide.

Hi,

Thank you!


This OFFSET formula you're typing above would also have to be associated with a named range, right? And I could still use a cell value instead of 30?

I'm very familiar with using OFFSET on my statistical formulas such as average, maximum, minimum, etc. I have just not used them for charts.

Anyway, I just tried to do this before heading to work, but no luck actually creating the chart...

I created a named range "Close_PL" which would refer to the column with header "Close_PL". I used formula OFFSET(FilteredData!$S$7;1;0;30;1). I assume 30 could be swapped with cell O1 to make it 'dynamic'.

I get an error for circle references when trying to add the series to the chart you made.

Uten navn.png

Thanks in advance for all help!

That's strange. A single offset formula should not return a circular reference warning, ever. And I've never seen a circular reference warning when a range name is applied to a chart. The only error that can come up is that there is an invalid range, but not a circular reference in the named range.

Anyway I copied and pasted your Offset formula into a named range "Close_PL". No warning.

I added a series to the chart with that named range. No error, no warning.

see attached.