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

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?

Best Answer


  • Tom.Richards
    Tom.Richards ✭✭
    edited 01/17/20

    @Paul Newcome Thanks 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
    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.

  • 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})


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try using something along the lines of...

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

  • That worked great, thank you!

  • Paul Newcome
    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.