Handling a Program Proposal with Multiple Presenters

Question

Currently, I have a Program Proposal form that is completed by an individual with personal contact information (name, address, city, state, postal code, email, phone number, etc.) and proposed program information.

The Program Development sheet then has 1 row for each program proposal (and each program proposal has a single presenter).

If the program manager decides to officially offer the program in a future term, she triggers an automation in the Program Development sheet to copy parts of the row to a Course Section Build sheet, which includes a column that assigns the section a CRN number ( a unique identifier such as 10343 or 63202). Each row in this sheet is a unique section, each with a unique CRN. The registration office uses the information in this sheet to build the registration site for our website.

Then, if the presenter is to be paid, the program manager triggers a different automation in the Program Development sheet to copy parts of the row to the Payment Request Approvals sheet. Each row in this sheet is a unique payment request. The CRN from Course Section Build sheet is entered into the payment request to let finance know which course section this request is associated with.

Summary:

Program Development Sheet: Each row = 1 program proposal = 1 presenter (e.g., "Caves in Vermont" presented by John Doe). Note: The same program might be offered multiple times, within one term or across future terms.

Course Section Build Sheet: Each row  = 1 section on the schedule in a specific term with unique CRN (Section 1 "Caves in Vermont", presented by John Doe, in spring 2026 term, on certain start/end dates, CRN 34344). Note: The program may be on the schedule in a specific term more than once and/or the program may be on the schedule in multiple terms. However, each section has a unique CRN.

Payment Request Approval Sheet: Each row = 1 payment request tagged with a CRN ($500 payment request for John Doe for presenting the section 34344 in spring 2026). Note: John Doe might have more than 1 payment request if he is presenting the program more than once in a term or across future terms, but each would have a unique CRN.

This works great. However, we realize we may have a program proposal that has 2+ presenters, and the form currently only allows 1 person to provide their contact information. We looked at three possible solutions:

Adding Fields to the Form

* It would seem easy to just add fields in the form to accommodate, 2, 3, or whatever we decide might be the most number of presenters.
* However, now 1 row in the Program Development sheet could = 1 presenter, 2 presenters, etc. 
* When parts of the row are copied to the Payment Request Approvals sheet, we'll only have one row in the sheet —- but technically, we'd need a separate row for each presenter since each presenter is paid separately (and the amount they are paid may be different).
* The program manager enters the payment amount and manages W-9 information for the one presenter in the one row. If this more than one presenter in a row, it would be impossible to manage some of this information knowing what goes with which presenter.

Duplicating the Row in the Program Development sheet

* Once one of the presenters submits the form (and indicates perhaps that there will be more than on presenter), the program manager can duplicate the row in the sheet.
* However, now each row is not a different program proposal., A program proposal could be 1 row, 2 rows, or even more.
* The program manager has to "manage" information about the presentation now across all of the rows, since information about the presentation (e.g., start/end dates) are copied to the Course Section Build sheet.
* We would be able to copy parts of each row then to the Payment Request Approvals sheet (3 rows copies to 3 payment request rows (each with same CRN), easy to know there are 3 separate payment requests for the same program).

Duplicating the Row in the Payment Request Approvals sheet

* We considered once a parts of the one row in the Program Development sheet is copied to the Payment Request Approvals sheet, we could just duplicate it so there is a row for each individual presenter. 
* When I say "parts of the row" is copied, I mean that the entire row is not copied. Only a couple of columns, and the other columns use INDEX-MATCH to the Program Development sheet to populate the other columns. We did this to avoid copying the entire row (which has nearly 300 columns when only 8 to 10 are actually needed).
* Presenter names and contact information (needed to process payments) are populated via INDEX-MATCH formulas. So, unless we added an "Override" column, the current set up wouldn't allow us to edit name/contact information for the other two presenters.
* We built this ecosystem to automate many parts of the process, and this solution would "break" how we've set up automations.

I thought about a parent/child kind of solution, but I can't think of how to incorporate this without re-introducing a lot of manual data entry and duplicate data entry and management of the same data across multiple rows.

I am not sure if anyone has set up something like this —- the multiple presenters for a single program —- but I thought I'd reach out and see if anyone had a solution that I haven't considered (or even a better way to implement a solution I did consider).

aschneiderheinze1025 · Answer

It's technically not copying the row at all. When the program manager checks the box to copy the row, a hidden column generates a unique key code. Then, in a "staging" sheet, Smartsheet collects all the unique key codes from the sheet. For each of the key codes (each in their own row), other columns in the row have cell-reference formulas that return the value from the original sheet. As a result, I have a row for each unique key with values from the other sheet that match that unique key.

Then, every morning at 5:00 am, the row from the staging sheet is copied to the Course Section Build sheet. I have columns in the Course Section Build sheet with the same column names. With a "copy row" only the values, not the formulas, are copied. Since there isn't a "trigger" in this sheet since columns are populate via formulas, I had to rely on an automation that runs at a certain time each day.

So, through this process, it appears to the program manager the row was copied. Using this "staging" sheet prevents copying 300 columns from the source to the destination sheet.

RSethi · Answer

Sorry, this to totally a different question but reading through your notes i was curios how do you

"she triggers an automation in the Program Development sheet to copy parts of the row to a Course Section Build sheet"

How do you copy parts of a row, I am assuming you mean certain columns only from the main sheet to another using automation? I thought when we use automation (copy to another sheet) it copies the entire row to the other sheet.

aschneiderheinze1025 · Answer

This idea is interesting, but how would the information get into the child rows. The proposal form is currently set up under the assumption there is one presenter per program. The form asks for the contact information and other personal information and presentation information.

If there are three presenters, do they all submit the same form (meaning they all submit the same presentation information)?

Would there be some kind of conditional logic in the form based on a field that the additional presenters check or somehow indicate they are the additional presenters and thus the presentation proposal information fields are no longer required?

Would there be a separate form just for the additional presenters (which only asks for the contact information and personal information)? And one presenter submits the main form to collect their contact and personal information as well as presentation proposal information? How would these additional presenters become children of the main row other than the program manager manually making them children rows?

We already have over 50 form submissions from previous terms that don't have a parent/child set up. All of the other sheets that pull information from the Program Development sheet aren't looking at parent/child rows, just a row with a matching key (unique identifier). Would this require some significant changes to other sheets to look at children rows (along with modifying all of the previously submitted rows in the Program Development sheet to make each proposal have a parent and child row?