Excel Chart with Rolling 6 Months, 12 Months, or User-Defined End Date using Dynamic Named Ranges

[@practicalspreadsheetsolutions]

Hello, if you need an alternative to using dynamic named ranges for making the dynamic chart in Microsoft 365 and Excel for the web, then take a look at this video kzbin.info/www/bejne/f3KanJ99f8eqhKs&si=zD0d0DncCFEwopCz

[@slmlmprcht]

This has helped me a lot thank you but for some reason mine is showing the future instead of the past. Any ideas what may have gone wrong?

[@practicalspreadsheetsolutions]

Hello, thank you for your comment! If you used the OFFSET function, then I think that you may have used a positive number in the fourth argument of this function (height) and need to use a negative number there. Positive height means the range forwards and negative height means the range when going backwards. Hope this helps :)

[@j.rjunior5584]

I've done something very similar to what you've done in the video, but except I used a pivot chart to graph my data, but I want to do it without the pivot chart. The way I want to do it is, I want a start year as my first criteria, and a end year as my second criteria for my bar chart, and I think I just figured it out LoL.

[@practicalspreadsheetsolutions]

Hello J.R Junior, thank you for your comment. I guess you need two drop-down lists then and a formula with index and match functions.

[@guppi1]

Nice trick. But the index match formula little bit hard to follow

[@practicalspreadsheetsolutions]

Thank you for your comment, I'll try to explain better next time :)

[@michaeldingee743]

Using these methods If you have Office 365 and are in Beta Version you can use =TAKE(GROUPBY(CHOOSECOLS(Data,14),Data[Orders],SUM,,0,1),-AY1) or =TAKE(SORT(UNIQUE(Data[StartOfMonth]),,-1),-AY1) Helper cells =SUMIFS(Data[Orders],Data[StartOfMonth],Days!AV7#)

[@jermiecanete2025]

I cannot do it on my excel web 😭 this is exactly what I want but cant do it. I have a different name manager view and the formula says invalid 😭

[@practicalspreadsheetsolutions]

Hello, thank you for your question. The web version functions a bit differently than the desktop version. The trick with dynamic named ranges in the web version is, that you have to include the sheet name and an exclamation sign in the formula before every cell reference (cell references still have to be fixed). Then you will not get the response that the reference is invalid. But you will run into the next problem with the chart. In the web version you cannot add chart series one by one. You can only add the whole source. If the whole source is just a named range, then it will get changed to fixed cell references and the chart will always display the same number of points. But there is a workaround (I was actually planning a video on this). Because you are in the web version, the offset function will spill. So add the formula somewhere on the sheet instead of the name manager. Type your headers on top of this spill range. Then click on one of the spill range cells, insert a chart, and you will get a dynamic chart, that will display the number of categories as in the spill range. The trick here is, that the whole spill range has to come from one formula, so if you have nonadjacent columns, then the offset function has to be set to return multiple columns (last argument of the function) and it has to be wrapped in the choosecols function to get the columns you need. Hope this helps.

[@jermiecanete2025]

Omg thanks so much for ur effort! Ill do that, I really appreciate it​@@practicalspreadsheetsolutions

[@practicalspreadsheetsolutions]

Hello, the new video with an alternative to making the chart with dynamic named ranges in Microsoft 365 and Excel for the web is here kzbin.info/www/bejne/f3KanJ99f8eqhKs Hope this helps

[@ericssontest2332]

…