Best Practices Help - Form initiated workflow with 3 regional spin-off sheets

Tom.Richards

New user looking for some advice.

We need to: Capture requests from a form(s) and save the original data. We know comments and attachments will be made by other users, and we don't want them to mess up what was originally there, or the original data in other rows put in by other users. We will have 3 regions that will probably need their own sheets so the other users are not overwhelmed when they make updates. We also need some kind of easy to tell us totals, statuses, etc. across all regions.

An idea we had was to use one central form, then run a Workflow to Copy the new row to a separate sheet, according to Region data. We send a confirmation email which gives them the link to the regional sheet so they can make changes as needed.

Questions: Up until now they have made updates on the original, central form. How can we keep people from using that moving forward? They may have it bookmarked and be confused if it is View Only.

Are there ways to tally up numbers of active rows (status) in the regional sheets into one area? Is that what the Dashboard can do?

Paul Newcome

To maintain historical data, you could set up additional Copy Row automations to additional sheets.

Copy Row to Region A Archive Sheet

Copy Row to Region A Active Sheet

Copy Row to Region B Archive Sheet

Copy Row to Region B Active Sheet

Copy Row to Region C Archive Sheet

Copy Row to Region C Active Sheet

You would have two sheets for each region. One that stores the original entry that won't be edited, then a second sheet for the region that can be edited.

To create the metrics, a dashboard is a great way to DISPLAY information, but you would need to create the tallies on a sheet so that you can build the dashboard off of those metrics.

The exact setup would depend on the metrics being collected along with how exactly you wanted them displayed. My suggestion would be to set up a metrics sheet separate from the regional sheets where you would use cell linking and/or cross sheet references in formulas to pull your tallies.

Paul Newcome

To maintain historical data, you could set up additional Copy Row automations to additional sheets.

Copy Row to Region A Archive Sheet

Copy Row to Region A Active Sheet

Copy Row to Region B Archive Sheet

Copy Row to Region B Active Sheet

Copy Row to Region C Archive Sheet

Copy Row to Region C Active Sheet

You would have two sheets for each region. One that stores the original entry that won't be edited, then a second sheet for the region that can be edited.

To create the metrics, a dashboard is a great way to DISPLAY information, but you would need to create the tallies on a sheet so that you can build the dashboard off of those metrics.

The exact setup would depend on the metrics being collected along with how exactly you wanted them displayed. My suggestion would be to set up a metrics sheet separate from the regional sheets where you would use cell linking and/or cross sheet references in formulas to pull your tallies.

Tom.Richards

@Paul NewcomeThanks for your help. You just opened another trap door for me with the Tally sheets. No wonder my Dashboard tests were not working at all. Our data comes in looking like the image, where we'll be counting up the number of open/closed jobs and who has them. I'll have to dig in further, but hoping to learn that, too!

Paul Newcome

Happy to help! ️

If you would like additional assistance with the metrics sheet(s) feel free to let us know.

Also... Please don't forget to mark the comment that most appropriately answered your original question as "helpful". This lets others with a similar issue know that a possible solution can be found here.

Tom.Richards

As many people do, I'm having troubles with formulas using data on separate sheets. The example in the image above is the reference. I want to count the number of items in the Status column that are "Closed". This looks like a Countif formula, but I'm only getting errors.

The examples talk of brackets and no brackets, so it's confusing. This doesn't work.

=COUNTIF(Status:Status, "Closed"{Automation Request Intake . Range 1})

Thanks

Paul Newcome

Try using something along the lines of...

=COUNTIFS({Other Sheet Status Column}, "Closed")

Tom.Richards

That worked great, thank you!

Paul Newcome

Happy to help!️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others looking for a similar solution know that one may be found here.