Copy/Paste retaining formulas with multiple sheet & summary field references

Tina Rustvold

This just may not be possible, but no worse off if I ask. We have a 'table' of data constructed of cells with formulas using sheet references, and summary field references.

We are trying to consolidate our metrics into one central sheet for easy annual/quarterly updates as they are used across multiple dashboards. The problem of course is copying this large amount of sweat equity into a the consolidated sheet without recreating everything again. I have tried copying within the same tab, but all the references are lost, even through the summary fields are named the same. Do I have a lot of tweaking ahead of me or is there a solution I have missed? TIA!

AravindGP

Hi @Tina Rustvold

Unfortunately, yes. You still need to create the references in the new sheet. Once you create it, you can possibly use the same formulas by way of copy and paste as long as the names are the same in the new sheet. Something I have done in the past is to copy all 12 months' formulas in an excel and then copied the formulas from excel to the new sheet together in one go and it works.