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

Chris Walker

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?

Andrée Starå

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!

Paul Newcome

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

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å

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!

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å

@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?