Show Dashboard, Graph from 3 different worksheet

[MaunishP]

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

 

[Chihiro]

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.

 

[MaunishP]

@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

 

[Chihiro]

This format is suggested by team, hence cannot change format.

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.

 

[MaunishP]

@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

 

[Chihiro]

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.