How do Link a Column w/ drop down items between separate forms/sheets?

I'm new to SS. This is my first project. I have an audit process I am building for a customer. There are 5 separate forms needed for the audit process. I originally had all 5 forms feeding the same sheet but with over 100+ columns for each audit, I went crazy scrolling left to right looking for the information I needed and couldn't imagine the customer doing it.

Q1: So, I now have each form pointed to it's own sheet (5 forms-5 sheets). But I have some data that really needs to stay in sync across forms and should not have to be input more then once. For instance, the lead auditor creates a unique name for each audit and the data is put in a drop down list for his auditees to pick. That drop down list needs to be the first item in of the forms and that name is what links the other 4 forms together as one audit event. I don't want the audit lead to have to go into 5 separate sheets and the add the new audit name to all 5 forms for every new audit. How do I populate (copy) that drop down (column data) that appears in form1 and make sure it shows up automatically again in forms 2-5?

Q2: Also, a general architectural question. What are the rules around when multiple forms should feed the same sheet vs. when they should feed different sheets?

Q3: The customer had a request that all of his users interact with forms and not sheets so that request has been driving some automation that is a bit sticky. What are the rules around user interaction. It seems that users can interact with forms, sheets and reports. What are the pros/cons of each. I have a situation where section 1 and 3 of a form get filled out, then a person that is selected gets the form sent back to him/her to fill out section 2 and 3. Then the audit approver goes in and fills out section 3 for the 3rd time. Should this be done on a sheet or report instead? Section 3 of the form is a 'state' question. It goes, from 'open' to 'started' to 'complete' to 'close'.

Q4: Bottom line, should all 5 of these forms feed one sheet and then build reports to shield folks from having to navigate the 100+ column sheet that will have 1000's of rows before too long?

-Thanks, Dana

Find more posts tagged with

Forms

Manufacturing

Sheets

Formulas

Reports

Solution Center

Project Management

Cell linking

Comments

Andrée Starå

Hi Dana,

Q1: See answer to Q4.

Q2: It depends on the solution. In my client solutions, I always try to keep it in as few sheets, reports, and so on as possible.

Q3: It depends mostly on what access the different roles have. Reports are excellent, but then they need to have access to the sheet. They don't need to go into the sheet but they can.

Q4: Bottom line, should all 5 of these forms feed one sheet and then build reports to shield folks from having to navigate the 100+ column sheet that will have 1000's of rows before too long?

It sounds like that would be the simplest solution. To give a better answer, I would need more information.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I would suggest having the initial form sent, then use update requests to fill out the additional information.

You can specify which columns are sent and which ones can be edited, and all updates are tracked on the same row as opposed to having duplicate ID's for multiple rows that then have to be consolidated.

While this would require having all of those columns on one sheet, the update requests would limit what is sent to the next person so they have much less to look at.

You could then build some basic reports and publish them as read only to provide overall visibility without access to any sheets.

dana_hood

Andree,

I've attached a screen shot of the business process.. I need to decide on a 5 form to 1 sheet relationship or a 5 forms to 5 sheets solution....

Remember what the data will look like:

a) 100+ columns of data (either in 1 sheet or broken up into 5 sheets)

b) each audit will have 100 columns of data filled in and for 50 action items and 4 states for each action item (opened, started, completed and verified), 50x4 rows = 200 rows for a typical audit. We have about 20 audits around the world at 4 sites.

Goal:

1) I want the auditors to love the automation which should make their life nice.

2) I want reporting to easy (reports not on the business flow)

3) I want management of audit action items to be efficient

Given what the data will look like, and the business process and the goals.. Would you go with option 1 or 2 in green at the bottom of the screen shot?

-many thanks, Dana.. if talking is easier, my phone is 802-324-6318

Audit Business Flow.gif

dana_hood

Paul,

Thanks for responding. I like your idea on edits being written to the same row but I don't think that happens for the same question that gets changed multiple times.. For instance, I have a state machine question that I ask multiple times. It's something like, select the state of the project. It's a dropdown list with 4 items, OPENED, STARTED, COMPLETED and VERIFIED. The auditor picks the OPENED, an alert is sent to an asssignee and that person picks STARTED and then another alert after say 60 days where the assignee picks COMPLETED, the auditor gets pinged that it's closed and goes and reads evidence and if happy, selects VERIFIED and the item is closed..

I don't want to overright the records for each of these events but I would like other information captured at different times to be on the same row. When I fill out my form and go in and look at the data, it's putting it on new rows.. I have 100ish columns and I have to scroll way over to the right to see 4 new data elements. I think if the system senses a new timestamp associated with anything, it puts the info on a new row. Is this correct?

(I also copied the business process to this thread)

-Thanks, Dana

Andrée Starå

Hi Dana,

Thanks for the call!

Let me know if I can help with anything!

Best,

Andrée

Paul Newcome

Dana,

Thanks for the explanation. I am beginning to understand your process a little better now. Unfortunately I am unable to clearly see your workflow no matter how much I zoom in on it (monitors here don't have the greatest resolution).

Here is a rough outline of how I understand it though. Please correct me if I am wrong at any point...

Auditor opens a project and fills out columns 1 - 20. Assignee gets an alert then starts the project and fills out columns 21 - 40. Assignee then marks the project as completed. Auditor verifies completion and closes.

Other than the status, are there other columns that both the Auditor and the Assignee are entering information to?

You say you need to scroll all the way to the right to see 4 new data elements. When you need to see those 4, is it those same 4 every time at that particular step in the process? Depending on the size of things, we could always create a column on the left of the sheet that compiles blocks of columns as they are filled out so that the pertinent data is there at the beginning of the sheet.

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…