"Save As New" Not Preserving Cross-References

Rachel Peabody

Hello,

I'm building a sheet in which I'm using lots of cross-references in the sheet summary. I plan to use it as an active metric field to display summary information from data in other sheets. I will need to copy the sheet about 30 times. I want to do this without breaking the cross-reference since it would be VERY time consuming to re-establish each link 30 times. I thought the "Save As New" function was capable of of this, but my references are breaking when I save the sheet as new.

The checkbox for "cell links and cross-references" in the Data & Formatting category is checked when I save the sheet as a new one.

Rachel Peabody

@Paul Newcome @Andrée Starå

I believe I have fixed my issue. While there was no data in the sheet's rows, the sheet summary cross-references would not copy. I copied some completely unrelated rows of data into the sheet, and it now copies the sheet summary fields as expected. The sheet summary fields do not reference any data within its own sheet, so I'm not sure why this worked. But it seems consistent.

Paul Newcome

I have noticed this happening to me as well with formulas in the Sheet Summary section. If you put the formula(s) in a column on the sheet itself, it should maintain the references.

Andrée Starå

@Rachel Peabody

Hi Rachel,

Strange!

It should work!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Paul Newcome When doesn't it work for you? I recently developed a solution for a client where we use it extensively, and it works.

Paul Newcome

@Andrée Starå It was a few months ago. They may have updated it since then. I just remember using "Save as New" and having to update references in my Sheet Summary fields.

Rachel Peabody

@Andrée Starå

Here are some screenshots of what the sheet looks like.

The formulas I have written allow every sheet summary field to be updated when the Vessel ID field is changed by cross referencing them to master log sheets where I input all our analytical data. My goal is to use this to make an active metric sheet for each vessel in our cellar, then display that information for all vessels together on a dashboard. thus making a summary view of our cellar that updates automatically as data is being gathered. However, I need to be able to copy this template for each vessel in order to make that happen.

Currently, when I select "Save as New" from the File menu, the copy does not retain its cross-references, as demonstrated by the "Reference Another Sheet" prompt instead of "Edit Reference" when the argument is selected.

Rachel Peabody

@Paul Newcome @Andrée Starå

I believe I have fixed my issue. While there was no data in the sheet's rows, the sheet summary cross-references would not copy. I copied some completely unrelated rows of data into the sheet, and it now copies the sheet summary fields as expected. The sheet summary fields do not reference any data within its own sheet, so I'm not sure why this worked. But it seems consistent.

Paul Newcome

Glad you were able to get it working.

Don't forget to flag your comment as "helpful" so that it is marked as the "Best Answer". That way it is moved to the top and will be easier to find if anyone else is searching for a solution to a similar issue.

Andrée Starå

@Rachel Peabody

Excellent! 

Glad you got it working!