Cell linking and updating a column

Danielle Arteaga

I have a master sheet that contains a column called "Product Family." This master sheet is updated weekly, and some of the Product Family rows change - added or deleted.

The cells in the Product Family column on the master sheet are linked to another worksheet (called "Curation Audit") - also in a "Product Family" column.

I need changes to the Master Sheet's Product Family column to update the Curation Audit by removing any deleted rows and adding any new rows.

Is it possible to automate this process? Right now, I have to delete all the cell links, delete the column then add the column back, and insert the cell links again. This then breaks all of my formulas, which have to have all the #REF errors in them fixed. At 1500 rows of data, this is not a sustainable process.

Paul Newcome

Ok. So you are going to want a column on each sheet that replicates the row number either manually entered or through a formula. I suggest a formula on the master sheet so that autofill can take over when rows are added/deleted, but I recommend manual entry (dragfill really helps with this) on the Curation Audit sheet. You are going to want to "pre-fill" the Curation Audit sheet to cover your 3,000 rows.

Then you can use basic INDEX formulas to pull your data over.

=IFERROR(INDEX({Master Sheet Column to Pull}, [Row #]@row), "")

The IFERROR ensures that if there are only 2,000 rows on the master sheet you won't have 1,000 rows on the Curation Audit sheet full of errors.

Paul Newcome

What is the purpose of the Curation Audit sheet? Do you have additional calculations being run on that sheet, or is it simply for viewing/editing?

Danielle Arteaga

I have additional calculations being run on that sheet, which then support reports and dashboards. I cannot combine it with the master sheet.

Paul Newcome

What is the maximum number of rows anticipated at any given time?

Danielle Arteaga

3000

Paul Newcome

Ok. So you are going to want a column on each sheet that replicates the row number either manually entered or through a formula. I suggest a formula on the master sheet so that autofill can take over when rows are added/deleted, but I recommend manual entry (dragfill really helps with this) on the Curation Audit sheet. You are going to want to "pre-fill" the Curation Audit sheet to cover your 3,000 rows.

Then you can use basic INDEX formulas to pull your data over.

=IFERROR(INDEX({Master Sheet Column to Pull}, [Row #]@row), "")

The IFERROR ensures that if there are only 2,000 rows on the master sheet you won't have 1,000 rows on the Curation Audit sheet full of errors.

Jeff Hoelzel

If it were me, I'd number my rows (build out) in both sheets, use a vLookup, hide & filter.

Danielle Arteaga

@Paul Newcome - BRILLIANT. Works perfectly. MANY thanks!

Paul Newcome

@Danielle Arteaga Happy to help. 👍️

@Jeff Hoelzel The reason I didn't suggest "pre-filling" the master sheet is because it would require a fair amount of manual work when a row needs to be deleted to pull the lower data up. Using the formula on the master sheet and not worrying about "pre-filling" row numbers means that the sheet will still be clean and functional when new rows are added or outdated rows are removed.

Jeff Hoelzel

@Paul Newcome Understood. Cleaner. Thanks for that.

Paul Newcome

@Jeff Hoelzel Sure thing. If it was just me working in the sheet I might have gone the route of pre-filling even the master, but trying to get multiple people on the same page of only deleting data instead of an entire row and getting them to use filters without complaining and all of the "extra work" just becomes painful. Haha.