Excel layout for dashboard

Hi all,

I am in the very early stages of building a dashboard as a front end to display data from 4 team tabs. The dashboard will have dynamic graphs (pulling data depending on selections using format control) you can see this on the dashboard tab

The team tabs is where data will be input by colleagues and will consist of a drop down in future

My question is.. does the data layout on team samuel (same team tab across each team) seem ok for it to feed into a dashboard or do I need another tab to calculate data for each tab? data being:

in January, how many holidays did people take (column B for activity), or how many hours were spent on client calls (column B for activity)?

Any advice and guidance appreciated.

Thank you!

Summary data in F5:F13 should be no problem because it summarizes one month at a time. I think each of those, for a particular team, could be done using a SUMIFS or COUNTIFS formula.

However, to chart a series of months (chart located at G5) will require some intermediate calculations to sum the number of projects by month. That summary data is not on your Team sheets.

I am not sure what you are trying to show in your other charts so can't help there. Your graphs refer to data in an external workbook so it's hard to tell what you have in mind there.

Also your dates in row 3 of the Team worksheets are messed up. In H3, for example, you intend to have 1 JAN 2021 but instead you have 1 NOV 2021. This also causes all your weekdays to be wrong (well, the first month is right, but only by coincidence that 1 JAN and 1 NOV are both Mondays). All of the dates are constant values. I recommend that you put 1 JAN 2021 in H3 then in I3 use =H3+1 then copy the formula to the right. Also, January has 31 days and you only show 30. Row H should be a formula like =TEXT(H3,"Mmm")

Hi 6StringJazzer

Thank you I have made amendments and will come back with another post once I start trying some things. Thank you