Forms

Hello,

Suppose I have a sheet that is populated with numerous rows of records that outline key information with a unique code.

Each row pertains to an individual and that individual will need to send in information that is in a form.

Rather than the form adding a new row, could the form tie together the info in the form and the already populated row using the unique code or similar?

Tags:

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi James,

    It sounds like the Update Request feature would work better for the process.

    More info: 


    Would that work?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • No, as I'd want to use a form as the update request.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to leverage a column that contains something unique to the original row and the update row then use either an INDEX/MATCH or VLOOKUP to pull the data.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide screenshots with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed? Even a "mocked up" version that has manually entered data to show your column names and desired outcome would work.

  • Image 1 and 2 is the sheet - so the columns in Green would likely/ideally be filled in manually.

    Image 3 is part of the online form - so student X would complete with their requirements

    The unique information could be the name of the student or we could use ID number even.

    When the online form is completed it would be really helpful for that to populate the already present row or merge the two

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The ID would most likely be more accurate than the name simply because it is less likely to have two students with the same ID than two students with the same name.


    Would you be able to manually fill in some of the data so that I can visualize exactly what you are trying to do? Is the form populating the same sheet?

  • Form will populate the same sheet unless you think there is an alternative solution


    Green columns are likely and Student and Exam Date to be predetermined, clear cells are what the form populates


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Honestly I personally think it would be much easier if the form populated a different sheet. Then we could use some straightforward INDEX/MATCH formulas with cross sheet references to pull the data.

    The formulas would end up looking something along the lines of...

    =INDEX({Form Sheet Band Members Column}, MATCH(Student@row, {Form Sheet Student Column}, 0))


    You would only need to change that first range to pull from each of the different columns as you move across the columns on your target sheet.

  • Thank you, is there any more detailed info about INDEX/MATCH formulas anywhere or an example sheet?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sure!

    The INDEX function is used to pull data based on a specified row number and an optional column number.

    =INDEX(range_to_pull_from, row_number, optional_column_number)


    The MATCH function generates a number based on where within a range a specified text was found. If you are looking at a single column, then it will essentially give you the row number.

    =MATCH("specific text", range_to_search, match_type)

    I personally use a 0 (zero) for the match type in the final field because it will look for an exact match which provides the most consistently accurate results.

    We can use the row number generated by the MATCH function when searching the Student Column in the form sheet for the data in the Student column of the source sheet to automate the row number portion of the INDEX function.


    For example...

    In the below screenshot we are using a MATCH function to search the Student column for the data that is in the [Search for:]1 cell.

    =MATCH([Search for:]@row, Student:Student, 0)


    As you can see, the MATCH function generated the number 3 since we are looking in a single column and the data we are searching for is found on row 3 of the range.


    So we can take that MATCH function and drop it into the second portion of the INDEX function to tell it which row to pull from.

    Since the range for the INDEX function to pull from is only a single column, there is no need to specify a column number, so it can be left out.


    The below screenshot was put together on the same sheet, but it will end up being the same syntax with {Range} replacing [Column Name]:[Column Name].

    =INDEX([Form Sheet Range To Pull From]:[Form Sheet Range To Pull From], MATCH([Target Sheet Student]@row, [Form Sheet Student]:[Form Sheet Student], 0))

    In the above, we use the INDEX to pull from the [Form Sheet Range To Pull From] column based on the row number generated by the MATCH by searching for the [Target Sheet Student]@row in the [Form Sheet Student] range.


    Does that help clear things up? Is there anything you'd like more detail on?

  • Yes, the only thing that isn't working is Range on the other sheet. What do I put for example if my column name is "Lighting"?


    =INDEX({SHEET NAME Lighting}, MATCH etc....


    Is there a way of getting INDEX to look at the sheet's entire column without having to insert reference?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To create a cross sheet reference, you start typing the formula...

    =INDEX(

    Then you click on the blue link that says "Reference Another Sheet".

    You will select the sheet you want to reference from the list on the left, then to select an entire column, you would click on the column header.

    At the top of the reference selection window near the left will be the range name. This will always automatically populate as "SHEET NAME Range #". You can edit this to help keep your references clear. I generally leave the sheet name and then (when referencing a column) type in the column name in place of "Range #".

    Once you have selected your range (and changed the reference name if you wanted), you would click on the blue "Insert Reference" button in the bottom right hand corner.

    It will then take you back to the sheet you are working in, and you should see something along the lines of

    =INDEX({Range Name}


    Then you would just finish typing out your formula inserting new references as needed.


    When I typed out this formula:

    =INDEX({Form Sheet Band Members Column}, MATCH(Student@row, {Form Sheet Student Column}, 0))

    I used range names to indicate exactly what you should be referencing. The range names when you build your formula can be different. I only specified which sheet and column to reference so you would know where each cross sheet reference should be pointing.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @James Wilson

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best, 

    Andrée 

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!