How to Report across Multiple Sheets

Seamas Finnerty

My organization has several departments. I have created a sheet, tracking requests for each department, key information includes the progress column (request received, reviewing documents, complete, etc.) and the date it was received.

My goal is to have a report or sheet to display on a dashboard that shows each department on one sheet displaying the progress of each request and also a count function showing "X department has 15 active requests, 4 are complete, 3 are reviewing documents etc and the date each request was received. Ideally this would update daily.

I've tried using report and a summary report but with no luck.

Thanks

SoS | Dan Palenchar

A row report should work just fine, make sure you reference all of the Sheets in questions or the entire workspace.

This works best if you use a template sheet with Identical column names and types, any discrepancy will result in report column that contain data from only some of the reference Sheets.

Chances are, your source Sheets are not as compatible as they could be or your report was not built properly, hard to say which without seeing the Sheets/Report in question.

Regarding the count function, you could do these in the Sheet Summary and use a Sheet Summary report to get that data on your dashboard, or create a separate metrics Sheet with crosss-sheet COUNTIFS formulas to get that data, then display it as a metric widget and/or chart widget and/or report widget.

SoS | Dan Palenchar

A row report should work just fine, make sure you reference all of the Sheets in questions or the entire workspace.

This works best if you use a template sheet with Identical column names and types, any discrepancy will result in report column that contain data from only some of the reference Sheets.

Chances are, your source Sheets are not as compatible as they could be or your report was not built properly, hard to say which without seeing the Sheets/Report in question.

Regarding the count function, you could do these in the Sheet Summary and use a Sheet Summary report to get that data on your dashboard, or create a separate metrics Sheet with crosss-sheet COUNTIFS formulas to get that data, then display it as a metric widget and/or chart widget and/or report widget.

jwilson970216

I would recommend just building a single request database sheet with a dropdown column to show which department the request belongs to. Then you can run reports filtering by department to show the pertinent requests for each dept. Then you could create a form to add rows if that functionality is of value. Forms can automate some of the functions each row may need and you could place it on the dashboard for other users to add requests without having direct access to the database.

Dan is correct, I prefer the metrics sheet solution because you can see more of your formulas at once in a sheet than you can in a sheet summary but they get you to the same place. Metrics sheets require more maintenance and do not automatically duplicate the way a sheet summary would.