Hi there, Gurus,
A question concerning the efficacy of inter-sheet references.
By inter-sheet I mean different pages in the same file.
If I have a cell on sheet 1 that’s referenced many times by different cells on sheet 2, is it “better” to reference the cell once only in sheet 2 and then have all the cells reference their “local” cell or just keep pointing them from sheet 2 to sheet 1.
What I think is the consideration is that the local cell - say - A1 in sheet 2 has the formula =sheet1cell A1 and then all the sheet 2 enquiries are local as =A1. Whereas, if they all point to sheet 1, then they must have the longer formula.
It’s not really just a case of “will it save a few process cycles”? But also, “which is the cleaner, more structured approach”?
Yeah, I’m aware of that but I just wondered whether the impact of looking up another sheet potentially hundreds of times - every time a cell value is changed was an overhead compared with the local cell.
Your point raises another question though - Is the named cell treated internally as a “local” cell reference on sheet 2 or is it just a named route through the sheet1/cellID formula?
Performance
For “computing cycles” it will not be noticeable. If at all measurable, the doubling (copy to “sheet-local” value) puts a heavier load on the computer.
The original will be calculated exactly once with each “state change” (each time you edit data which that cell depends on), and also the “local” cell will be calculated exactly once each time the referenced cell changes. AFAIK references are direct, interpreted upon entry, so length does not matter.
Structure
It all boils down to how you read it. What info do you need?
A few thoughts:
- Keeping the referenced number in one place reduces redundancy. You have it in exactly one place. You will not by mistake replace the reference by a different number
- Referencing it for each sheet means that you see the actual reference value while you work with your other data/formulas.
Also, the advice from @PKG to name the cell is a very good idea. This will make your formulas easier to read and understand.
Yeah, that’s a good point - I often have a “mirror” cell while I’m working out a new procedure so I can “see” the source but then remove it once I’ve established the integrity of what I’m attempting. I just hadn’t figured out whether it was better to constantly refer to the source cell as opposed to the local pipeline.
Yep, done that 
Just set up an experiment: create a book from two sheets, enter something into Sheet1.$A$1. On sheet 2 in A1, place =$Sheet1.A1 and fill in column C with the formula =Sheet1.$A$1 (for clarity, take a large number of rows, for example, a million). Save the workbook as LinkToSheet.ods. Now replace the formula with =$A$1 and save as LinkToCell.ods. Do these two books match your question? Okay, now just compare the sizes of the saved data:
table:formula="of:=[Sheet1.$A$1]"
table:formula="of:=[.$A$1]"
Spot on - my entire document is about 10% of the difference between your two examples. Conclusion - Worry about something important.
