A chart displaying the most recent 7 filled entries. But I don't know how to make it.
I would like to create a tab in Excel where I can input weeks 1 through 52 and below that, input fields where I can enter data. Additionally, I would like it so that when I have, for example, inputted data for weeks 1 through 18, the chart only displays weeks 12 through 18, and when I input data for week 19 the following week, the chart automatically shows weeks 13 through 19. How can I achieve this?
Last edited by RogueStab; 04-29-2024 at 08:30 AM.
You can filter desired data and then graph will show only filtered range.
That way you are flexible to filter any amount of weeks and get such view on the chart.
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGW,
Thank you so much!
And I appreciate it a lot, I've attached a file.
zbor, I don't quite understand it, but I am some what of
It;s a shame that you have only Excel 2019 - that is correct, is it?
Hi AliGW,
That is indeed correct. It is due my work space.. I am limited to this.
If you had 365, then you could hide this under your graph and use it as the source data:
=TAKE(FILTER('Insert here'!A2:AZ3,'Insert here'!A3:AZ3<>""),,-7)
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Week 12 Week 13 Week 14 Week 15 Week 16 Week 17 Week 18
Sheet: Dashboard
That's a shame - I'll leave it to someone who's good with AGGREGATE, then.
Hi AliGW,
Is there any solution for Excel 2019?
There will be, as I hinted in post #8, but it won't be me providing it.
I had a go. These could be hidden under your graph:
=INDEX('Insert here'!A2:AZ2,AGGREGATE(15,6,COLUMN($1:$7)/((--RIGHT('Insert here'!$A$2:$AZ$2,2)<=WEEKNUM(TODAY()))*(--RIGHT('Insert here'!$A$2:$AZ$2,2)>WEEKNUM(TODAY())-7)),ROW(1:1)))
and:
=INDEX('Insert here'!A3:AZ3,AGGREGATE(15,6,COLUMN($1:$7)/((--RIGHT('Insert here'!$A$2:$AZ$2,2)<=WEEKNUM(TODAY()))*(--RIGHT('Insert here'!$A$2:$AZ$2,2)>WEEKNUM(TODAY())-7)),ROW(1:1)))
Have alook at this.
Unfortunately the formulas became a bit lengthy to account for situations that the data is (i) completely blank, (ii) completely full (52 weeks), (iii) less then 7 data points.
Hopt that's what you were looking for.
This approach is without helper data but with range names.
I've created two ranges. press Ctrl+F3 to find them.
One for week names:
WeekNum =OFFSET('Insert here'!$A$2, 0, MAX(0,COUNT('Insert here'!$A$3:$BA$3)-7),,7)
One for data:
StartChart =OFFSET('Insert here'!$A$2, 1, MAX(0,COUNT('Insert here'!$A$3:$BA$3)-7),,7)
Then you can use this in your chart.
Not: You must have all cells populate in insert Here row 3 before last week.
Blank cells will create wrong output.
When OFFSET can be avoided, it is best not used (as it's volatile). YOu should post the formula in your post, please.
This is slightly shorter - put this in D5 of your Dashboard sheet:
=INDEX('Insert here'!2:2,MATCH("Week "&WEEKNUM(TODAY())-7+COLUMNS($D:D),'Insert here'!$2:$2,0))
Copy across and down to cover D5:K6. Select that range then click on Insert | Charts | Column/Bar Charts, then you just move the chart so that it covers the cells with the formulae in.
Hope this helps.
Pete
Hi Pete,
Thank you so much for helping. I appreciate it a lot. But unfortunately it doesn't let me download the attached file of yours.
It gives this error:
Fatal error: Uncaught mysqli_sql_exception: No such file or directory
You have various other options! Pete's workbook downloads correctly for me.
Well, it didn't for me. If Pete could reattach it that would be wonderful. I appreciate everyones help and input!
Here it is.
Thank you ma'am!
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Thanks for doing that, Ali, while I was having some lunch.
Pete
No worries.
@RogueStab - please see post #21 with instructions bout signing off your thread. Thanks.
Hi zbor,
I've a question. How would this work if I were to make multiple charts with different data?
When you creating chart you can use Select Data Source either from fixed range either from Named range.
This is just example how to create dynamic data range.
You can add in the chart another dynamic range as you would add another fixed range.
Thank you!
You are the best! Have a blessed day!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks