1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

DynamicReport

Discussion in 'Ask an Excel Question' started by StarMusk10, Nov 26, 2018.

  1. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    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

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    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 likes this.
  3. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    Thank you for the quick reply. I hope this is what you were talking about?

    Attached Files:

  4. sureshsonti

    sureshsonti Member

    Messages:
    102
    I came up with this. But the green color font is not dynamic. I guess OFFSET function will do the trick.

    Attached Files:

    StarMusk10 likes this.
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    Here's sample flat table set up.

    Attached Files:

    Thomas Kuriakose and StarMusk10 like this.
  6. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    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?
  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    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.
    Thomas Kuriakose and StarMusk10 like this.
  8. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    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
  9. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    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.

    Attached Files:

  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    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.
    StarMusk10 likes this.
  11. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    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

    Attached Files:

    Thomas Kuriakose and StarMusk10 like this.
  12. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    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.
  13. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    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!

    Attached Files:

  14. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    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

    Attached Files:

    Last edited: Nov 27, 2018
    Thomas Kuriakose and StarMusk10 like this.
  15. sureshsonti

    sureshsonti Member

    Messages:
    102
    Mr. Bosco,
    Just curious how the formula will look for this format which is highlighted in green color.

    Thanks

    Attached Files:

  16. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    Thank you very much for the help.
  17. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    In B1 (highlighted green), copied across to F1:

    =INDEX(Data!$A:$A,MATCH("zzz",Data!$A:$A)+COLUMNS($A:A)-5)

    Regards
    Bosco

    Attached Files:

  18. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    Thank you all for the help! I have learned a ton.
  19. sureshsonti

    sureshsonti Member

    Messages:
    102
    Thanks Mr. Bosco.
    StarMusk10 likes this.
  20. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    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

    Attached Files:

    bosco_yip likes this.
  21. sureshsonti

    sureshsonti Member

    Messages:
    102
    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

    Attached Files:

    StarMusk10 likes this.
  22. StarMusk10

    StarMusk10 New Member

    Messages:
    11
    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.

    Attached Files:

    sureshsonti likes this.
  23. sureshsonti

    sureshsonti Member

    Messages:
    102

    Thats a good idea.

Share This Page