Preference sheet

Mike Wise

I am trying to make a sheet where a colleague will be able to go through the current job bids, select the ones they want to bid on by preference(1st, 2nd,3rd ect…). If they only a few jobs they want to bid on i have made this however, if there are 20+ jobs, how can i create sheet that can accommodate this. I don't want 20+ columns in the raw data sheet or on the forms. Is there anyway to get this completed?

Screenshot 2025-04-10 092210.png

Screenshot 2025-04-10 092142.png

jmyzk_cloudsmart_jp

Hi @Mike Wise

It’s a great idea to let your colleagues indicate their preferences for bidding on available jobs.
However, asking users to rank 20+ options can easily become overwhelming.
Most users typically care about just their top 3–5 choices, and forcing them to rank every option could lead to form fatigue, abandonment, or inaccurate submissions.

Here’s a user-friendly and scalable approach you might consider:

Form and Sheet Setup Suggestion:

• Allow users to prioritize only the jobs they are interested in (e.g., 1st, 2nd, and 3rd choices).
• Add a clear instruction at the top of the form:
  ➔ “You can select and rank up to 20 jobs. However, if you only want to submit your top choices (for example, up to your 3rd preference) and leave the rest to be assigned by the office, simply leave the later ranks blank.”
• This way, users can fill out as many rankings as they want, and you can assign unranked jobs later based on your staffing needs.

Implementation Options:

• Smartsheet Forms:
  You can create dropdown fields for "1st Choice", "2nd Choice", "3rd Choice", etc.
  Only the "1st Choice" field needs to be set as required. Others can be optional.
• Google Forms + Smartsheet Integration:
  If you prefer a more matrix-style input (where jobs are listed vertically and users pick their rankings horizontally), Google Forms is a good option.
  However, please note that in Google Forms' matrix format, you cannot make only specific rows or columns required.
  Therefore, clear instructions at the beginning of the form become crucial.
  You can automatically send Google Forms responses into Smartsheet using the official Smartsheet for Google Forms add-on (link here).

https://docs.google.com/forms/d/e/1FAIpQLSenJ-_PmP9n_PBL-u6uOTdtYuLWa8G9_dC9QHVSAOO2MTJZpQ/viewform (You can open the form from this link to test how it really looks and how it connects to Smarthseet)

By setting to "Shuffle row order", you can avoid upper jobs getting more preference.

https://app.smartsheet.com/b/publish?EQBCT=0e55ab3a940e435eb6cf23a9705b4499 (You can check your response using this link.)

Important Notes About Structure:

• Whether you use Google Forms or Smartsheet Forms, each priority (1st, 2nd, 3rd, etc.) will require a separate column in the Smartsheet destination sheet.
• While it is theoretically possible to consolidate all rankings into a single cell using advanced techniques like Google Apps Script or custom form designs,
  it becomes very complicated when trying to analyze or split the data later.
• Smartsheet does not have a native SPLIT function like spreadsheets do, so breaking apart a combined text field would require a combination of complex text formulas.
• Therefore, it is highly recommended to prioritize making the form easy and intuitive for users, even if it results in multiple columns on the backend.

Why This Approach Works:

• Users are not overwhelmed—they can fill in only what they care about.
• You still capture enough data to prioritize assignments.
• You maintain flexibility in allocating remaining jobs as needed.
• The system stays manageable without needing excessive custom scripting or complex text parsing.

Hope this helps streamline your setup and create a much smoother experience for your team!😀