• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DynamicReport

StarMusk10

New Member
Hello all,

I'm trying to make a dynamic report that will show only 5 quarters of data from a larger worksheet. I have attached a copy of a sample sheet for you to look at.

As I enter new quarters into the "Data" tab I would like the table in the "Report" tab to reflect only the most recent 5 quarters of data.

Any help would be greatly appreciated.

Thanks,
Todd
 

Attachments

Chihiro

Excel Ninja
For ease of reporting and analysis. I'd strongly recommend changing your "Data" sheet structure. You should be using flat data table rather than cross-tab structure with merged cells.
 

StarMusk10

New Member
Thank you for the quick reply. I hope this is what you were talking about?
For ease of reporting and analysis. I'd strongly recommend changing your "Data" sheet structure. You should be using flat data table rather than cross-tab structure with merged cells.
 

Attachments

StarMusk10

New Member
I came up with this. But the green color font is not dynamic. I guess OFFSET function will do the trick.
Thank you for the help, I can see where column F is changing to update the new entry in the data table but the remainder of the columns aren't shifting. How would I get this to happen? I would like to see the most recent data entered and the subsequent quarters prior to that. Thanks, Todd
 

StarMusk10

New Member
This is great! Thank you so much. I tried to figure this out with a Pivot Table but I just don't have enough experience with them. So what do I have to do to add to the data?
1. Convert the flat table to Excel Structured Table.
2. Add any additional data to bottom of table.
3. Refresh Pivot Table and adjust filter as needed.

Step 1 is only needed once.
That worked great, but it also added a "Total" column to the table. I have attached it for you to see. Thanks again for the help and I apologize for my ignorance.
 

Attachments

Chihiro

Excel Ninja
Click on '+' beside FY18' Q3. It will collapse the total column.

Edit: You can just remove "Group" from column label field as well. This is sort of construct I use to manipulate Pivot Table via VBA (dynamic filtering). But isn't required. This Group layer is what's adding Total column.
 

bosco_yip

Excel Ninja
Another option in formula solution

"Report" sheet setup in remove your repeated header row and remain one in row 1

then,

1] In C1, copied across to G1

=INDEX(Data!1:1,MATCH("zzz",Data!1:1)-10+COLUMNS($A:A)*2)

2] In C2, copied across to G2 and all copied down :

=INDEX(Data!$A$3:$Q$500,MATCH(LOOKUP(2,1/($B$1:$B2<>"Benchmark Group (Mean)"),$B$1:$B2),Data!$A$3:$A$500,0),MATCH(C$1,Data!$1:$1,0)+($B2="Benchmark Group (Mean)"))

Remark : formulas will auto update/change in followed you new data added in the source sheet

Regards
Bosco
 

Attachments

StarMusk10

New Member
Click on '+' beside FY18' Q3. It will collapse the total column.

Edit: You can just remove "Group" from column label field as well. This is sort of construct I use to manipulate Pivot Table via VBA (dynamic filtering). But isn't required. This Group layer is what's adding Total column.
Thank you. Now to figure out how to make this into a dashboard! LOL. Where can I go to learn about this type of Pivot Table? I have a few other reports that could benefit from a set-up like this.
 

StarMusk10

New Member
Another option in formula solution

"Report" sheet setup in remove your repeated header row and remain one in row 1

then,

1] In C1, copied across to G1

=INDEX(Data!1:1,MATCH("zzz",Data!1:1)-10+COLUMNS($A:A)*2)

2] In C2, copied across to G2 and all copied down :

=INDEX(Data!$A$3:$Q$500,MATCH(LOOKUP(2,1/($B$1:$B2<>"Benchmark Group (Mean)"),$B$1:$B2),Data!$A$3:$A$500,0),MATCH(C$1,Data!$1:$1,0)+($B2="Benchmark Group (Mean)"))

Regards
Bosco
Thank you, When I add more entries to the "Data" tab everything shifted but I was left with a "#REF!" I have attached the sheet for you to view. Thank you again, I would have never been able to figure this out!
 

Attachments

bosco_yip

Excel Ninja
Thank you, When I add more entries to the "Data" tab everything shifted but I was left with a "#REF!" I have attached the sheet for you to view. Thank you again, I would have never been able to figure this out!
1] The formula is fixed as per revised attachment

2] In C2 revised formula, copied across and down :

=INDEX(Data!$A$3:INDEX(Data!$500:$500,MATCH("zzz",Data!$1:$1)+1),MATCH(LOOKUP(2,1/($B$1:$B2<>"Benchmark Group (Mean)"),$B$1:$B2),Data!$A$3:$A$500,0),MATCH(C$1,Data!$1:$1,0)+($B2="Benchmark Group (Mean)"))

Regards
Bosco
 

Attachments

Last edited:

StarMusk10

New Member
The formula is fixed as per revised attachment

C2, copied across and down :

=INDEX(Data!$A$3:INDEX(Data!$500:$500,MATCH("zzz",Data!$1:$1)+1),MATCH(LOOKUP(2,1/($B$1:$B2<>"Benchmark Group (Mean)"),$B$1:$B2),Data!$A$3:$A$500,0),MATCH(C$1,Data!$1:$1,0)+($B2="Benchmark Group (Mean)"))

Regards
Bosco
Thank you very much for the help.
 

StarMusk10

New Member
Thanks Mr. Bosco.
Good afternoon, I have used your suggestions to build my dynamic dashboard. Now I would like to be able to select a sheet from the dropdown list and have the dashboard change accordingly. I have attached a copy of my workbook for you to look at. I have also signed up for some dashboard classes locally so I can stop feeling guilty about asking all these questions. LOL
 

Attachments

Good afternoon, I have used your suggestions to build my dynamic dashboard. Now I would like to be able to select a sheet from the dropdown list and have the dashboard change accordingly. I have attached a copy of my workbook for you to look at. I have also signed up for some dashboard classes locally so I can stop feeling guilty about asking all these questions. LOL
The Qtrs on the Dashboard are not Dynamic. I moved all the data into ICU sheet into a table, so the formula for the Dynamic Qtrs wont work. Please check the numbers. Maybe a macro will solve this issue
 

Attachments

StarMusk10

New Member
The Qtrs on the Dashboard are not Dynamic. I moved all the data into ICU sheet into a table, so the formula for the Dynamic Qtrs wont work. Please check the numbers. Maybe a macro will solve this issue
I ended up adding a table in the Quarter Tab with a list of the Quarters and using the formula from bosco_yip =INDEX(Data!$A:$A,MATCH("zzz",Data!$A:$A)+COLUMNS($A:A)-5). I think it works.
 

Attachments

Top