Forms

James Wilson

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?

Andrée Starå

Hi James,

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

More info: 

https://help.smartsheet.com/learning-track/smartsheet-intermediate/update-requests

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!

James Wilson

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

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.

James Wilson

https://community.smartsheet.com/discussion/comment/252879#Comment_252879

How would I do this - could you share an example screenshot/s?

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.

James Wilson

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

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?

James Wilson

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

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.

James Wilson

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

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?

James Wilson

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

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å

@James Wilson

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée