Formula to pull all values of a column in Sheet A to Sheet B

Hello,

I have a master Smartsheet (sheet A) that contains a list of projects identified by NAME column and it also contains many other columns. Every project in the list will have a unique name.

I would like to use a column formula in Sheet B to pull all unique records of names from Sheet A in to Sheet B. My main goal is to automatically pull all new unique records from Sheet A in to Sheet B. In Sheet A and B, the column types are 'Text/Number'.

I have previously done something similar and it worked (only difference seems to be it was a Contact List in prior successful example), but I am hitting a roadblock now. I had used a helper column to log a Row ID.

=IFERROR(INDEX(DISTINCT({SheetA Project Name}), [Row ID]@row), "Add More Numbers")

Any suggestions?


Tags:

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Chris Walker

    I hope you're well and safe!

    You could use INDEX/MATCH and connect the sheets by using the Autonumber column in one sheet and adding the number manually to the other sheet, and that would always keep them "synced".

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    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 or/and as the accepted answer. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What is the reason for pulling this column into a separate sheet?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Chris Walker
    Chris Walker ✭✭✭✭

    Hi Paul,

    The information within sheet A is our master sheet, has a very large number of columns collected, and is managed by one team.

    The project name effectively acts as a unique primary key for how many of our teams operate - tracking information by project. Another smaller team needs to track information in a similar way, but we want to ensure we have the same projects on our boards, and also same spelling to ensure we can have quality mapping opportunities for any future reporting/visualizations. The thought is to use a similar approach to the one mentioned above to ensure that via a column formula all projects get pulled over, then we would use a few index/match formulas to pull a couple overlapping columns identified by the primary key (project name) - which would exist in sheet B column A from the solution above. And finally, this sheet B would have other new, open columns that Team B needs to track independently by project (but should not be privvy to team A.

    This is the background/intention. I appreciate your time reviewing/considering.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Chris Walker

    I hope you're well and safe!

    You could use INDEX/MATCH and connect the sheets by using the Autonumber column in one sheet and adding the number manually to the other sheet, and that would always keep them "synced".

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    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 or/and as the accepted answer. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Chris Walker
    Chris Walker ✭✭✭✭

    Thanks Andree! In our main (v large) sheet A, we don't have this numbered column at the moment. From your understanding, there is currently no way to take a similar approach but instead of referencing the numbers located within an explicit column, to reference the actual sheet Row IDs?

    As a side note, it is so strange, I have this index/distinct approach setup (and working) in another use case with no numbered column in our master sheet A. The only difference is the target column for the unique index is a contact column type, not a text/number like in this case - but I didn't think this should be the issue.

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

    @Chris Walker

    Happy to help!

    Unfortunately, that's correct. There's no way to reference the internal Row ID in a formula.

    The main thing is that you'd have to have something that we could use to match the different rows together.

    Make sense?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | 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!