Master and metrix sheets

Kyle Black

I have a incident report form for a whole corporation. i was to create sub sheets that will pull the whole row based on site and division in to the sub sheets and have them update as information is added to the master.

Have no idea how to start this

Paul Newcome

Ok. It looks like you are primarily going to want to use COUNTIFS functions.

Here is some documentation on cross-sheet references that may come in handy:

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Most of your formulas are probably going to end up looking pretty similar to:

=COUNTIFS({Main Data Sheet Site Column}, @cell = "Site Name", {Main Data Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021), {Main Data Sheet Checkbox Column}, @cell = 1)

This will count how many rows match the specified site, are in the month of January and year of 2021, and have that specific checkbox checked.

Paul Newcome

I would suggest creating reports instead of subsheets.

Kyle Black

problem is that you can't do anything with reports on the dashboard like graph and such.

Paul Newcome

What kind of graphs are you needing to show?

Kyle Black

we are going to use these to make trends for each site but instead of having to create a form for each site we are doing a corporate level form and then dash board the incidents by site showing data trends for near misses, first aids and OSHA recordables. I currently have one site entering information so it is easy right now but i have 6 more sites to add.

Paul Newcome

You could create metrics sheets and use formulas with cross sheet references to look at the master sheet and also include range/criteria sets to account for different sites.

Kyle Black

right and i am still fresh to alot, i mean i have created alot of content but i have not done alot of the cross referencing so i was looking for help on how to do some of this with multiple sheets.

Paul Newcome

A lot of that is going to depend on the exact metrics you want to pull, how you want them displayed, and how your source data is laid out.

You can have all of the different sites' metrics on the same sheet if you are pulling the same data for each site and then just select specific portions of the data for the charts individually.

Are you able to provide some screenshots and samples of the metrics you are currently pulling for a single site?

Kyle Black

I will all have probably a two other reports on here for root cause and corrective actions. but that will be easy because of the report function. but this is the dash board set up for one site. I was going to do them individually but then i would have to track and manage 7 sheets so i am trying to consolidate and then metrix the site out.

Paul Newcome

Are you able to provide screenshots of your metrics as well as the formula(s) you are currently using?

Kyle Black

here is the metrix that i will have for each site this is being entered manually at the moment but i will need Fx for counts of date ranges and keyword in columns to populate

It will be populated from at least three sheets the incident, root cause, and OSHA log trackers which are huge column wise and hard to screen shot.

I then can run reports on the dashboard to use as summaries for the incidents and investigations.

Kyle Black

for the formulas it would had to include date rage, Site name = # for checked or non blank cells in specific column.

Paul Newcome

Ok. It looks like you are primarily going to want to use COUNTIFS functions.

Here is some documentation on cross-sheet references that may come in handy:

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Most of your formulas are probably going to end up looking pretty similar to:

=COUNTIFS({Main Data Sheet Site Column}, @cell = "Site Name", {Main Data Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021), {Main Data Sheet Checkbox Column}, @cell = 1)

This will count how many rows match the specified site, are in the month of January and year of 2021, and have that specific checkbox checked.

Kyle Black

dude you rock, can i get you on speed dial?.

Paul Newcome

Happy to help. 👍️

If you would like, you are more than welcome to look me up on LinkedIn where we can discuss "speed dial" in more detail. Otherwise you are welcome to "@ mention" me in any post here in the Community.