Formula to populate just the first row of data in another sheet

CaseyC ✭✭
edited 08/24/22 in Formulas and Functions

I'm trying to write a formula to populate data from one sheet to another but I only want the top row of data to populate and then update when the 1st row changes. I have a Submission Form sheet that gather data from a form, when a new form is submitted it will be at the top of the sheet. I want that top row of data to auto populate in a 2nd sheet called Master Task List.

The Master Task List is essentially a task list template and each time data is changed (i.e. when someone submits a form and the data in the Master Task List is updated with the new submission) there's an automation that will move the rows to each division's tracking sheet.

My problem is I can't get a dynamic formula that will update when a new form is submitted. I've gotten formulas to populate the data from the Submission Form sheet to the Master Task List sheet but all the ones I've tried seem to "tie" to the row that was 1st when the formula was created. So when I submit the form and the new data is now the 1st row on the Submission Form sheet the Master Task List doesn't update to the top row of data. I've tried several variations of Index, Index/Collect, VLookup, Index/Match (but the problem with Match is I have nothing to Match with in the Master Task List) and I think that I've specified in the formulas to look at row 1 but it's not.

76th Street Housing was submitted via the form and is now the 1st line of data on the Submission Form but the Master Task List is still displaying the previous line of data, 12 Street



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!