Bulk cell-linking

sahilhq

Hi,

I am currently using the copy row automation for some of my sheets and to give you an example, I copy rows from a "child" register to a "parent" and then when I go on to make amendments in my parent register, the child register doesn't get updated. I would like the child register to be updated as well and I think this could be done through cell linking. However, since there are hundrerds of rows and 30 odd columns in the sheet, cell linking each individual cell is quite difficult and tedious and there is also room for error in the future as several people would input into these sheets and they would not always remember to cell link once they add items to the sheet. Is there way to bulk-cell link between sheets or automate it any which way or is there any other workaround to my problem?

Paul Newcome

Ok. You would need to pick one. For this example I will just call it "Unique Column". This will need to be available on both sheets. An INDEX/MATCH looks like this...

=INDEX({other sheet column to pull from}, MATCH([Unique Column]@row, {Other Sheet Unique Column}, 0))

Basically the way this works is the MATCH function locates the data in the unique column and generates a row number where it was found. That row number is then used to tell the INDEX function which row to pull from within the first range established.

Paul Newcome

The formula would go into Column 2 in your above screenshots.

To populate the remaining column, you would use the same formula but change the range in the first cross sheet reference to point at the new column.

Paul Newcome

As long as the selection sizes match, you can link multiple cells in one go. For example:

If you select a 5x7 grid on your target sheet, you can then select a 5x7 grid on the source sheet to link to, but it will not work if one grid is 5x7 and the other grid is 7x5.

If you have some kind of unique identifier per row that you can use to match up across both sheets, you can use cross sheet references in INDEX/MATCH formulas (very similar to a VLOOKUP but better in my opinion) to pull the data and autofill could potentially (depending on your setup) go ahead and pull the formula(s) into newly copied rows so that you have even less maintenance.

sahilhq

@Paul Newcome Thanks for the reply, once again. I think you solution could work because the parent and all child sheets match in terms of no. of columns. Basically there is one parent and 4 child sheets. All the sheets have 20 columns, however the number of rows don't match (the parent sheet has a lot more rows and the child sheets only has information that would be of relevance from the parent sheet in it). I have a column in all my sheets called "Copy to master tender sheet", so as soon as a new entry is added in the child sheet, we check the box and it moves to the master/parent sheet. But once changes are made directly in the parent sheet, the child sheet doesn't get automatically updated if that makes sense.

Can the checkbox "copy to master tender sheet" column be our unique identifier for all rows being checked going forward? The INDEX/MATCH formulas sounds interesting. Could you please advise what the formula look like? I have attached excel export of one of my SS. I have deleted all the tasks listed in the spreadsheets for confidential reasons. The checkbox column is on the far right hand side.

export of SS.xlsx

Paul Newcome

A checkbox column would not work. We would need something that is unique to each row that we can match on.

sahilhq

@Paul Newcome For example?? I am happy to tweak the register accordingly.

Paul Newcome

Is there any column that will have data that is unique to each row on the sheet? If not, is there a combination of columns that when pulled together would be unique to each row?

sahilhq

@Paul Newcome Apologies Paul, I don't entirely understand what you mean. Could you please elaborate or give me an example of this scenario?

Paul Newcome

Is there a column that contains data that will not be repeated on any other row?

12345

54321

67890

09876

The data above is unique to each row. The data in row 1 will never be found in a row other than row 1. Same for row 2, 3, and 4.

sahilhq

@Paul Newcome Yes, most of the 20 columns in my sheet contain data that will not be repeated on any other row. Can you advise how do we proceed now that we have unique sets of data, i.e. the INDEX / MATCH formulas we need to use?

Paul Newcome

Ok. You would need to pick one. For this example I will just call it "Unique Column". This will need to be available on both sheets. An INDEX/MATCH looks like this...

=INDEX({other sheet column to pull from}, MATCH([Unique Column]@row, {Other Sheet Unique Column}, 0))

Basically the way this works is the MATCH function locates the data in the unique column and generates a row number where it was found. That row number is then used to tell the INDEX function which row to pull from within the first range established.

sahilhq

@Paul Newcome Thanks for this but I think will need further assistance. So what I did was that I created two sample sheets and called it Test Sheet 1 and Test Sheet 2. Both sheets has a unique column with the same row data and column 2 with unique sets of data as well(see below screenshots). Do I need to insert this INDEX/MATCH formula in the unique column or would it need to be inputted into a different column? Also, as discussed I have 20 columns in my other sheet that needs to be index/matched. Does that mean I need to use the same formula for every column or is it okay if I just insert in one column and somehow the data in the other columns will be matched?

Sorry I am asking you too many questions!

Paul Newcome

The formula would go into Column 2 in your above screenshots.

To populate the remaining column, you would use the same formula but change the range in the first cross sheet reference to point at the new column.

sahilhq

@Paul Newcome That worked! Thanks. All I need to make sure is that I at least have one column in both sheets that has a unique column.

On a side note, Paul - thanks for helping me with these formula related queries over the last few weeks. You always reply prompty and provide the best solutions which I really appreciate.

Paul Newcome

Happy to help! 👍️

And yes. The key is ensuring you have that column that has reference data that is unique to each row. It can be done if you have multiple columns as well but gets a little more complex.

ID.................Other

1234................A

1234................B

4321................A

4321................B

While the data above does not have a single unique column, there is only one row that could contain 1234 in the ID column and A in the Other column.

The most straightforward way of handling this would be to add a helper column where you join these two values together into a single cell, but it can be possible without having to do that (depending on a few different factors).

sahilhq

@Paul Newcome Yes, I am just going to get a helper column in both of my sheets that will have unique sets of data (maybe I could use a unique ID column). The current columns are set up in such a way that the data can change to not being uniqye at anytime so I would be better off creating the helper column.

A quick follow up question - say once I have completed using the formula you suggested for my existing data and in the near future there is a new row entry using the copy row automation from one sheet to other. Will I need to drag the formulas down to the added row or will Smartsheet automatically manage to pick it up? I wish there is a way to achieve the later!

sahilhq

@Paul Newcome Hi Paul! I hope you had a nice weekend. Over the last couple of days, I tried to work on the suggested solution however I didn't manage to make a great deal of progress with it. I started off by creating helper columns with Unique ID's in my master sheet and all the 4 child sheets. I used the auto number column for each sheet so this way once rows were copied to the master sheet, the unique number from the child sheet would stay, hence I had the same reference data unique to the row in both sheets.

But whenever I added a new row in any of the child sheets and using the copy row automation moved it to the master sheet, the index/match formula within the child sheet didn't automatically get generated. I always had to drag down the formula and realised that this wouldn't be the most efficient solution considering the fact that many users will be updating these sheets and I am sure most if not all users will copy rows to the master sheet without realising there are supposed to underlying formulas to ensure that changes in the master register can automatically be captured in the child sheet after the transfer of the row.

Can you please look into this and guide me further? Thanks!