Automated Workflow Question

I have an idea I want to see if this makes sense.

I have a form I have made where 15 different areas are going to be entering information. I need to filter that information by area to create a hub/dashboard to show data for specific areas. One sheet for everyone to fill out makes the most sense, but on the back end, I am having trouble. I was thinking maybe I could use automation to move the rows to different sheets and have a sheet for each area, then feed that information to the hub from the individual sheets; or am I making it too hard that way?

Let me explain what I am doing and see if anyone has any better suggestions:

  • 15 areas have access to one form where they submit information
  • I need to take that information and find the following for each area individually:

Then I want to ultimately have all areas roll into one master that would show something like this:

Where I could then use that information to show on a chart on the dashboard.

My thinking was: one main sheet, automation to move rows to sheets for each area, calculate percentages of each area, roll info to one master dashboard to display information by area but also combine info to show overall information. I hope this makes sense.....