Hey. So right now we're gonna create an auto update chart, and in this case I'm gonna take this sales data. So we have like start date, end date sales just some random numbers. I've put here some monthly sales. Maybe your sales are weekly or biweekly, but for, you'll see you can change this as much as you want.

And the idea is essentially we wanna have this chart.  based on the information here, and we're gonna be filling it out on the bottom right. So if your workflow matches this, right, you have a, a ledger of sales data every month and you just want to see the last 12 months, right? You just wanna see the trailing 12 month sales on one chart.

You can do this on separate sheets, you can do this on the same sheet. So essentially, I put this all in one, but you can actually put this on three separate sheets and. Refer to the other sheets. So that's how I would do it. I would say, Hey, input the sales data here. Then on another tab I would have this work chart or work table.

And then on a third sort of dashboard or summary, I would add this last 12 month sales. That's how I would do it if that's the only thing I was getting, getting data on. But how we're gonna do this right here, I'll show you how this works. So I'm gonna add in, say another, let's say we had a really good memory, we went back to.

Original great months and we, I'm gonna just hold down that, that's not right. Pull down that. So we'll get the next month and I'm gonna enter that. And so now if you notice here, it already entered that number here, so that changed. So let's do that again. I will, let's just do this. I will down this and see now we changed.

So if we entered the numbers like one, two, wait, this is even the wrong year. Oh wait, it is the right year. It's, sorry, it went over December. Okay, so 1, 2, 20, 22. Then we type in one, no, two, sorry, January, February 1st. Then we're like February. What is the end date? February 28th. February 28th. And let's put in 5,006.

So that changed in this format. Just needs to be formatted to number, actually, you can just click up here, format this entire column, but there we have our really nice month. Maybe it wasn't that nice of a month. Let's go back to two and see that. Automatically updates. So how does it do that? First, well, the idea is we create this trailing 12 month table and we are gonna get this automatically with a couple of interesting, like index.

And we're gonna use count all and just create this stable one that looks at this, this table and says, what's the last 12 entries? That's essentially the idea here. So how are we gonna do that? Well, first we need to know how many rows there are. There's 21 rows. What's interesting about Count All is that even.

You had say this table wasn't manually inserted, but maybe it's a accumulation or a collection of a bunch of other things and we go equals some of, you know, a two day, three of some other sheet. Right. And if it is zero, it's going to, it's going to show up. If there is a number here, it's absolutely gonna show up.

If it's like, Hey, if is blank and we'll say B 22, then do nothing. If it's blank and do something, do the sum. If it's not, see now this doesn't have anything here. Even though there's a formula here, as long as it is essentially null, meaning there's nothing there, not even a space, not even a. You can't use something like conditional formatting here.

You have to just use literally nothing instead of zero. But that's interesting is that now this count all is counting all here, but it's saying there's nothing here, so we're not gonna count it. And you can change that. And then maybe you put in the date and you go 3, 1 20, 22. And then when I did B 22, so then now we get, let's change.

that to the, once we have some information here. Wow, that's a big month. But , let's just do a two. There we go. Or no, not even a two C . One. C2. That's what I meant. So see now we have, this doesn't show up until you have something. Columns, same idea. If you have, like, if it's maybe instead of if it's blank, maybe if it's zero, don't do anything.

You can also do that. But that gets us account of all of the columns. Then we want the last row, and I'm using an index I'm saying index A to C. And the, it takes three arguments. It takes the reference of like, what do you want to look. Then it's asks what is the row you wanna look at? And then what is the column you wanna look at?

We know the column is the third column. We'll want to get the sales data. But the row is changeable, fungible. It, we, it's programmatic, right? It needs to be based on some formula, which is this count all. And I'm just saying F1 here to say as this changes, this changes. And if you notice, if we delete this now, it says 21 and it gets the last row, which is 26.

If we want the next to last row, we just do minus one. We do index of count, all minus one. That's it. And so essentially we can get the last 12 by doing minus one, minus two, minus three, minus four, all the way to 12. And so we create this table we create a table that gives us not just the sales, we want the start date as well to get the month.

And now this 12 items, or actually 24 items.  gives us, now we take this chart of this instead of doing a chart off of here, we know we only want the month on the sales. So what I've done is I grabbed the start date and then I've changed the formatting here. So all I did is go up to format. Actually I'll do it to this one here.

I'll show you how I did that. Format number, go to more formats, more date and time, and then I choose the, the month and the year. And it shows me month and year, I can do it. Maybe two digit year might be better.

And so now we can do either full year 2021 or April 21. And now we get, I'm gonna just copy and paste this formatting. Oh, I don't want to do that actually. I just want the copy, the format. So I want to go Copy, right click paye. Special paye. Now they're all the same. So if you notice in here, this is index of A to C count all C to C minus 11 is the, essentially the el, what is that?

The 11th? No, minus 11 is the 12th one. So the 12th from the bottom is this one. So that's March, and you get March, April, may, June, all the way through. We can even, let's say we don't have 2021. Let's say we only have, what's 12 months here?

Let's just delete a couple of these

and we see it automatically updates, right? December, 2020 until November, 2021. It has our sales here. And then this chart. All I did for this chart, actually I can show you is I highlighted start date in sales. Chart and we're gonna pick a bar chart here. Yep. Bar chart shows the dates. Pretty simple. I made a couple of changes, like I took off the borders just so that it looked a little cleaner.

You can change the colors here. Anything you want to do you can change the name of it, like you could say last trailing. 12 months. And now this is a, let's delete that one. And this is an updating chart. Every time you update your sales, you'll update this chart.

So to review, what we did was we took a simple data chart or table start date, end date sales. Figured out the last RO row by using count all. We then indexed that we got that last row by using the index, and then we got all the, the 12 bottom rows by doing index and using the row count all for the row, minus one, minus two, minus three.

We created a table here. This is index and count all, and I just went and hand typed all of these negative minus 11 minus. The column is the third column here. The this column is the first column, so I hand typed all these and then I just took a chart of all of these and I got a automatically updating chart as our trailing 12 months increases, month to month.

Hope this is helpful and please feel free to email me any questions. Bye bye.