Index + Match + Cell Reference + Forms Question

ScionoftheNight ✭✭✭✭
edited 12/09/19 in Smartsheet Basics


I have two sheets for 2 separate processes in our production workflow. For each of the sheets I have forms for people to fill out. I would like to have it where the 2nd sheet is able to pull data from the first sheet. The 2nd sheet needs to see some of the same data as the 1st, but I would like to make it that people don't need to enter the same information twice in a form. Data from the 1st sheet is copied to the 2nd sheet if the Sales Order (primary column) is the same. I can't use VLOOKUP since I need to do this for many columns. I tried using INDEX and MATCH, but as soon as I add another row in the 1st sheet, it messes up the linked columns in the 2nd one. 

My formula:

=INDEX({Single Cell Seeding Range 8}, MATCH([Sales Order]@row, {Single Cell Seeding Range 2}))

How can I adjust this formula so that when I add new rows, it doesn't break the formula? Thank you for the help, it as always appreciated!



  • L_123
    L_123 ✭✭✭✭✭✭

    match is finicky if you don't use the 0

    =INDEX({Single Cell Seeding Range 8}, MATCH([Sales Order]@row, {Single Cell Seeding Range 2},0))

    Beyond that your formulas seem to be correct. It is your references that are off. When you want to reference an entire column on another sheet, start the reference builder and select the column header. This will always refer to the entire column and should fix your error. If there is still an error you should double check your column reference didn't get deleted by remaking them.

    Side note: always name your column references when you make them. Your future self will thank me. it makes it so much easier to troubleshoot/edit

  • ScionoftheNight
    ScionoftheNight ✭✭✭✭

    Woah I didn't even know you could name column references lol. Thanks!