How to Return ALL values from Sheet1 column to Sheet2



Is there a formula to copy all values in Sheet1 column Task to Sheet 2 column Task2?

Sheet1 is populated via form, so rows are consistently added.

What I can't do:

1) Adding a Helper column with numbers 1,2,3,etc to Sheet1 is a no go. So I can't Vlookup from Sheet2.

2) Index(Distinct will only return unique values. I need ALL values from Sheet1 (many are duplicates).

Thanks for any help with this.


  • Austin Smith
    Austin Smith ✭✭✭✭✭

    Can you post a screenshot to help give context?

    Without more data, and ruling out VLOOKUP and INDEX, and having duplicated sets of data, I'd say no, there's not a way to grab only one column's data and transpose it.

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

    Hi @BrianDN

    I hope you're well and safe!

    Premium app, DataMesh.

    Is that an option?

    I hope that helps!

    Be safe, and have a fantastic week!


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

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


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

    W: | | P: +46 (0) - 72 - 510 99 35

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

  • BrianDN
    BrianDN ✭✭

    Hi Austin & Andree,

    Thanks for getting back to me.

    No, DataMesh is not available to me.


    Sheet1 Column Task (Fed by form so rows will update)

    Needs to equal Sheet2 Column Task2

    Distinct would return:

    And the problem with a numbered helper column in Sheet1, is that when a new entry is submitted in the form, the new row added to the top of the sheet will NOT have an associated number in the Helper column. Notice that the new entry (Cherry) has a blank in the Helper column instead if the desired "1".

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!