How to keep columns synced or grab only a cell of a new row

Nic0

Hello community,

I have a sheet with all the signed projects we currently have (primary column is the project number).

In another sheet I need to have all these project numbers from this "master sheet" to collect information about timelines from other sheets. 

What I'm trying to do is to synchronise the "project number" column in such a way that when a new project number is added to the master sheet, I want that number to automatically appear in the second sheet. 

I've already tried INDEX MATCH, but as there is no row for that project number in the second sheet, I have nothing to "match". 

I found a lot of requests for automatic copying of cells (instead of the whole row) but I think this is not yet developed.

Cell linking would also help, but since I can only link cells that are not empty, it won't work.

Pre-filling cells and then deleting the content is also not possible as the master sheet is filled by an automation that automatically creates the rows and does not just fill the next "empty" row. 

Is there any smart idea to automatically capture a newly entered project number?

Sorry, my explanation sounds quite complicated after reading it again

Best wishes and thanks in advance

Andrée Starå

Hi @Nic0

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Another option would be similar but use the INDEX feature to collect the information.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

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, 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å

Hi @Nic0

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Another option would be similar but use the INDEX feature to collect the information.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Nic0

Hi Andrée,

thank you so much for your fast reply.

This was a great idea and I tried the Autonumber column instantly.

Unfortunately it did not work for me since there is already a auto number column in the master sheet were the data is pulled from and only one Autonumber column per sheet is allowed. I'm sure your answer will work for the majority of users that come to this thread and they will be super happy to find the solution! :-)

I now changed the workflow in a way that I don't use the "interim" sheet anymore and directly copy the row after signature into my destination sheet via automation.

This means I now have ~40 columns that I don't need but since Smartsheet has no problem with different columns in the destination sheet I have just hidden the unnecessary columns and pull all the timelines etc. witch Index(Match from the particular sheets. It's working like a charm now.

Best wishes

Nico

Andrée Starå

@Nic0

Happy to help!

You'd use a standard column in the destination sheet and pre-populate the rows with the numbers.

Make sense?

✅Remember! 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!

Nic0

@Andrée Starå

Thanks for your reply!

Makes totally sense but in this special case I have 3 sheets.

• The "master master" sheet with ~50 columns (including a Autonumber column) from which I only need the content of the primary column (eg. project number)
• The "master" sheet where the whole row is copied in (including all the columns) to grab the project ID via INDEX(MATCH
• The "destination" sheet where all the information from other sheets is pulled to

Since I already have a autonumber column in the "master master" sheet, it's not possible to have an additional autonumber column in the "master" sheet and it's also not possible to exclude the autonumber column from being copied. Due to the number of rows containing "not signed" studies in the "master master" sheet, the autonumber column in the master sheet has gaps of dozens of numbers and can't be used for the pre-population.

Now the rows are directly copied into the destination sheet without the interim step and I've hidden the 50 columns I don't need.

Super happy now, thanks a lot! :-)

Andrée Starå

@Nic0

Excellent!

You're more than welcome!

✅Remember! 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!