Copying formulas with a sheet reference to a different file

[ssaunders]

(I have already solved this, but I’m putting this here for reference, b/c I couldn’t find a post like it)

I get a new report every month. I’m making an “auto calc” page. The vision is to have a generic table with formulas set so that I can copy and paste it from my personal file directly into the new report, have the formulas gather all the data across several sheets in one place, then copy and paste the values out. This saves me having to sort and filter each sheet individually, then copy and paste in pieces into another report.

The problem I am facing is that the sheet references don’t survive the copy and paste. i.e. SheetName.B:D either becomes

=VLOOKUP(P7,'file://../My Documents/Files/Numbers/filename.ods'#$'SheetName'.B$1:D$1048576,2,0)

or

=VLOOKUP(P8,OtherSheetName.B:D,2,0)

or it’ll explode with “#REF!”

How do I copy and paste a sheet reference into another file?

[ssaunders]

tl;dr - The name of the sheet won’t transfer, you have to plan your copy/paste based on relative positioning

Copying and pasting references with a sheet name turn out like this: 1. If the sheet name includes $, it will reference the sheet by name in the file it was copied from 2. If the sheet name doesn’t use $, it will reference the sheet by position in the spreadsheet. This is the only way I’ve been able to get it working.

Ex:
File 1: MarysSheet, JohnsSheet, BobsSheet File 1.ods

File 2: Sheet1, Sheet2, Sheet3 File 2.ods

If you want to move the following formula from BobsSheet in File 1 to Sheet3 File 2, and have it calculate using the new sheet’s info, the transition looks like this:

=COUNTA(JohnsSheet.A:A)     /// NO $, meaning "the sheet previous to this one"
--changes to--
=COUNTA(Sheet2.A:A)

If you want to move the following formula from BobsSheet in File 1 to Sheet2 File 2, and have it calculate using the new sheet’s info, the transition looks like this:

=COUNTA(JohnsSheet.A:A)     /// NO $
--changes to--
=COUNTA(Sheet1.A:A)           /// "the sheet previous" to Sheet2, the paste destination sheet

A word of warning: You will end up with a bunch of #REF!'s if you try to copy and paste the formula into a sheet with a relative position that doesn’t work–i.e. pasting from BobsSheet in File 1 to Sheet1 in File 2, which has no previous sheet. This rule applies to pasting inside the same file: moving the formula from BobsSheet in MarysSheet in File 1 will result in this:

=COUNTA(JohnsSheet.A:A)     /// NO $, meaning "the sheet previous to this one"
--changes to--
=COUNTA(#REF!.A:A)     /// No "previous sheet"

[drbeco]

In the original file, be sure that the sheet name referenced in the formula doesn’t have a $ signal.

Example of the original formula with $:

=VLOOKUP(P7,$SheetName.B$1:D$1048576,2,0)

With a $, when the formula is copied to a new file, it will be an absolute reference to the original file. Thus, it will appear as

'file://../My Documents/Files/Numbers/filename.ods'#$SheetName'.B$1

Removing the $ signal in the original formula will allow you to copy the formula without this “file” reference.

The solution is that simple. Edit the original formula to be:

=VLOOKUP(P7,SheetName.B$1:D$1048576,2,0)

and now you can copy it from file to file, and it will be a relative reference within the file (not absolute ref to the original file)