Copying a formula from 1 sheet to another

Adam Fleming

I'm trying to copy a formula from one sheet to another that contains references from other sheets. It's worked in the past but right now I'm getting an invalid reference error.

Paul Newcome

If those cross sheet references were previously created on the new sheet, then there is a chance that it is still stored, but if you have not previously created that specifically titled cross sheet reference on the new sheet (or it has not been used in a formula on the new sheet for a while) then it will need to be recreated.

Once the reference has been created on the new sheet, then future copy/pasting with that reference will work, but it does need to be established on the new sheet first.

Paul Newcome

Further explanation on my last comment.

If I have a formula that I manually entered on the new sheet that goes something like this...

=COUNTIFS({Other Sheet Range 1}, "specific text")

and created the cross sheet reference, then I can copy/paste a different formula to the sheet that uses {Other Sheet Range 1}, and it will work.

But if I have never created {Other Sheet Range 1} on this new sheet, then the copy/paste will need to have the reference created.

Paul Newcome

You will need to recreate the cross sheet references themselves.

Adam Fleming

Paul, are you saying that I need to recreate the references each time when I go to a new sheet? If so, I never had to do that before, in fact, I have many cross sheet references that still work. I can copy those equations from sheet to sheet (looking up to a source sheet) and have no issues.

Paul Newcome

If those cross sheet references were previously created on the new sheet, then there is a chance that it is still stored, but if you have not previously created that specifically titled cross sheet reference on the new sheet (or it has not been used in a formula on the new sheet for a while) then it will need to be recreated.

Once the reference has been created on the new sheet, then future copy/pasting with that reference will work, but it does need to be established on the new sheet first.

Paul Newcome

Further explanation on my last comment.

If I have a formula that I manually entered on the new sheet that goes something like this...

=COUNTIFS({Other Sheet Range 1}, "specific text")

and created the cross sheet reference, then I can copy/paste a different formula to the sheet that uses {Other Sheet Range 1}, and it will work.

But if I have never created {Other Sheet Range 1} on this new sheet, then the copy/paste will need to have the reference created.

Adam Fleming

Ah, that helps Paul. So the way we're setup is we have a template and a source. The template is used to make about 100 individual sheets that house information pulling from the source sheet. Up until today, the equations used in the template were only using 2 cross sheet references. When adding information to each sheet, the equations always worked because the cross sheet reference was already held in each sheet. Now that we are trying to create a new cross sheet reference that doesn't exist in the template or the 100 sheets created from the template, we can't copy the new equation and reference between all the sheets because it doesn't already exist. Does that sounds accurate?

Paul Newcome

That is correct.

Adam Fleming

Thank you sir!

Paul Newcome

Happy to help. 👍️