Assign a volunteer number to a new form submission if that volunteer doesnt already have a number on

Multi part question

Assigning a sequential number to volunteer form submissions
Automatically pulling a number from another sheet if the form submission was from a returning volunteer

The underlying worksheet "2025 Intake Worksheet" captures data from volunteers via a form "2025 Intake Form" . The form includes a y/n field on "have you volunteered previously"

I also have a worksheet of volunteer data from last years volunteers in the "Operational Worksheet" and every volunteer has a V24_####volunteer ID.

I would like to assign a sequential volunteer number with the prefix V25_#### to any new volunteers and at the very least leave blank the Vol ID field if the volunteer already has a number.

I can format the column in the 2025 Intake Worksheet as a number with the prefix and counter but then how do you get the workflow to assign a sequential number?

Then, I would like this new volunteer to be added to the "Operational Worksheet"

Then, I would like to get the returning volunteers new data on the Operational Worksheet. BUT I would like to use their existing vol ID, add any new information, and keep any information they previously provided. In essence, I would like to merge the rows.

If the Vol ID field is blank on the 2025 Intake Worksheet (meaning they stated they are returning volunteers), and the Vol ID exists on the Operational Database how can I best merge these two rows of data?

Lookup/match/paste or a manual intervention with a separate report of duplicates based on name and mobile for example?

I have no experience in helper columns and limited formula experience.

Note that we will be using the ROW UPDATE function to update rows for returning volunteers, but it is likely that we will still have returning volunteers submitting again through the Intake form.

Thank you for your patience.

Find more posts tagged with

Accepted answers

All comments

James Keuning

Is there a reason you want to have a sheet for each year? I am not saying that I have a solution for you if you use one sheet, but I don't want to propose a solution based on the multiple-sheet system that you have, and then end with, "but you should use one sheet," and then you ask for that solution.

The problem I foresee is in 2026 you are going to have two previous sheets to check. If you are going to merge 2025 and 2024 when 2026 starts, I am curious why you don't just run sheet.

The benefit of one sheet is that you can check the sheet you are in for a duplicate mobile number. Rather than using a cross-sheet reference to look for a duplicate.

How do you feel about having gaps in your numbers? If you want to have no gaps, assigning the ID is going to be harder. But if you are ok with gaps, you can assign a 2025 number to a person, but override it if the person has an existing record. So you will have a contiguous running ID column behind the scenes, but the users see the refined ID (ActualID).

So it will be like:

RunningID (Autonumber)	ActualID	PersonName
2025_0001	2025_0001	Mike
2025_0002	2025_0002	June
2025_0003	2024_0056	Joan
2025_0004	2025_0004	Pat

So 2025_0003 is used but will never be seen because Joan is 2024_0056.

All of that gets you your ID. The next part is where you need to either enter the information for new people, or you need to inherit the information from the previous record. So I would have a set of cells that hold the information in the current record, and then a set of formula fields that look for a previous entry, and if found return the previous values, and if no previous entry, bring in the values from the current record. I can give you some more guidance on that if the one-sheet system works (and again, if you want to use multiple sheets, you can, so I will give guidance on that too).

LAW

Appreciate the quick response and yes, I am struggling with the right way to setup sheets in this case.

I will ask about having gaps in the vol numbers.

Question: But, if I have that yes/no column on "have you volunteered before" can I merely use a workflow to assign a volunteer number JUST to those rows that have a blank in that column? How do I do that?

As for using two sheets versus one - we could change it to have the Intake Form be attached to the Master and check for duplicates…that sounds like an easier solution. The rationale given for the 2025 Intake was to have a clean copy of all the data received before we went and manipulated it. Given that we are new users, the likelihood of error and fumble fingers is greater than one would wish.

As far as the overall year-on-year challenge, you are right. We thought the easiest way to do that would be to have an annual Operational Worksheet using all the information we need to manage the event that year, then at the end of the event, we combine the Operational Database for that year to the Master Database. The Master Database then becomes the starting point for the next year.

I'd like to understand the best way to set that up in this first year so subsequent years are a no brainer.

I really appreciate your thoughtful response. I do my best to search for answers to questions before I post but so often the context is missing, especially for the more macro setup questions (as opposed to formulas.)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.