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.

Show Dashboard, Graph from 3 different worksheet

Discussion in 'Discuss Data Visualizations and Charting' started by MaunishP, Feb 6, 2019.

  1. MaunishP

    MaunishP Member

    Messages:
    89
    Hi Team,

    Would like to see dashboard, report and graph from 3 different worksheet information in one worksheet.

    I have 3 different files showing day on day information of work done in 24 hours.

    Would like to see holistically data on final sheet like :

    Number of training conducted week wise different location wise
    Trainers wise classes conducted week wise different location wise
    Need to know which training room has conducted which training classes week wise

    Would like to see information in graph ---> Which trainer was utilized more ? Which training room was occupied more ?

    Training room name & trainers name is available in BA & BB column in each worksheet.

    I have not inserted any data in detailed in blank rows.

    Please let me know if there are any queries.

    Regards,
    Maunish Patel

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    Your data layout isn't conducive to analysis. I'd recommend storing data in flat table, rather than cross tab, if further analysis/manipulation is needed. Cross tab, should only be used as final product.
  3. MaunishP

    MaunishP Member

    Messages:
    89
    @Chihiro : Thank you for your response.

    This format is suggested by team, hence cannot change format. If you can provide some suggestion regarding this format. Basically this sheet we fill up to book our training room and conduct training.

    From this current format data can we combine data together to see one information as dashboard ?

    Looking forward for your suggestions / recommendation and solution

    Regards,
    Maunish Patel
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    As the person building the report, you should take ownership and make recommendations. In my opinion, it shouldn't be democratic process.

    It looks like you are using 3 sheets as both data entry and visualization. Data entry, and data visualization should be separate component in design and should not be mixed in my opinion. Your 3 sheets should be part of final visualization and not starting point.

    Data entry/storage should always be in flat table. If necessary, create userform for submission, but store it in flat table. Otherwise, you are just creating more work for yourself.
  5. MaunishP

    MaunishP Member

    Messages:
    89
    @Chihiro

    I merged all worksheet into one to do pivot and graph.

    Now, i have 3 different questions :

    1) How to find text value name from cell ranging E5 to AZ5 [ This should be for all rows ]
    Example : If Row 2 has value Verizon from Column E5 to AZ5 how can we find what value [ text " Verizon "] is written there

    2) How to count total value name
    If Row 2 has value Verizon 8 times then it should count as 8

    3) How to not show value as "0" if i use reference from different worksheet.

    I have attached updated sheet. "Sheet 1" Also check pivot sheet if you can help slicing few more information

    Regards,
    Maunish Patel

    Attached Files:

  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    Flat table should have Time in single column.

    I'm bit busy now, I'll review if I have time tomorrow, if others haven't helped you by then.

Share This Page