How to lock a reference on one sheet to another sheet

English
#1

I have a sheet, “Sheet1”, with several columns of data. This data is augmented regularly.

I have another sheet, “Sheet2” , that references the cells of “Sheet1”. I want “Sheet2” to maintain it’s references to the cells not the cell data of “Sheet1”

However, when I add a new row to “Sheet1” the references shift down one cell, maintaining their reference to the data that they were initially assigned to.

I want the reference on “Sheet2” to continue to reference cell “Sheet1”.A1 not "Sheet1.A2 after I add a new row to “Sheet1”.

Protecting the column and protecting the sheet do not accomplish this condition.

Is this possible in Calc?

#5

Define a named expression/range with absolute sheet reference and relative column and row references and use that in the cell formula instead of the reference.

For example, in Sheet1 cell B2 you want to reference the cell on Sheet2 B2 without being adjusted to its insertions and deletions:
With the cell cursor on Sheet2 cell A1 invoke Manage Names (Ctrl+F3), Add, Name: DataCell, Range or formula expression: $Sheet1.A1, Add, OK.
In Sheet2 cell A1 enter the formula =DataCell
That’s it.
If you enter the same =DataCell in other cells, that same relative column/row position is used, e.g. in B2 it will point to $Sheet1.B2, in C3 to $Sheet1.C3 and so on.

1 Like
#6

Yes, but what happens if you then insert a row above the referencing cell in Sheet2? It depends on the copy/move semantics you want.

  1. Villeroy’s INDIRECT: You can copy/move/insert before the referencing cell all you want in Sheet2 and still get the same referenced cell in Sheet1.
  2. erAck’s Named Range: If you copy/move/insert before the referencing cell in Sheet2, the referenced cell in Sheet1 is now different.

Again, it depends on what you need. In this case, I feel like erAck’s method is a house of cards for normal data aggregation/reporting, but in some sort of spreadsheet-based simulation or something, maybe it would be just the ticket.

#7

INDIRECT is working perfectly. As I add or delete rows to sheet1, sheet2 changes correctly. I will try to figure out erAck’s method. The problem with INDIRECT is I had to manually create every row. Tedious.

I tried erAck’s method for locking the output of Sheet2 to the cells, not data, in Sheet1, so that Sheet1 data could be added to or deleted from and allowing the updates to show in Sheet2, in the cells they are in, after the update.

ErAcks method works to transfer data from one sheet to the next. But when Sheet1 is updated Sheet2 follows the data on Sheet1, not the cell.

With INDIRECT Sheet2.A2 remains focused on the data in Sheet1.A2 after adding or deleting from Sheet1.A2. This is the desired behavior for me

With Named Range Sheet2.A2 changes to Sheet1.A3 after adding a new record to Sheet1.A2. This is the same behavior as directly referencing Sheet1.A2 on Sheet2.A2.
The reference will follow the data not the cell being referenced. This includes using =$Sheet1.$A$2.

Depends on what you are trying to do.

Thanks for the help!

#8

A name is nothing but an alias for a reference. It behaves exactly like the reference itself with the exception of external links. An external link to SheetY.A1 always refers to that cell even if the reference has moved in the source file. The alias name evaluates to the actual position when the link is updated.

#10

No, it does not. A relative reference part of a named reference is not adjusted if the cell it points to is moved.

1 Like
#11

Ah, yes. I see what you mean.
Left_Neighbour pointing from B1 to A1 does exactly the same as the relative reference A1. The reference remains “stable” as long as you use it in the same target cell relative to the referenced cell. Named references that are relative or mixed ones can be confusing.

#17

Mixed absolute/relative is convenient if you want to reference the same column in several cells within a row and have the row reference always relative to the formula position, like $Sheet2.$C1

#2 
=INDIRECT("Sheet1.A1")
#3

Thank You!

#4

INDIRECT() is almost always the wrong answer, except if a reference is to be constructed of a variable string. While it works there are usually better ways, avoiding the indirection and execution on each and every change of document content.

1 Like
#9

Maybe easy with =INDEX(Sheet1.A:A;1)
or =INDEX(Sheet1.A:A;ROW()) so you can copy down to get value from 'Sheet1¡ on the same row where the formula is.

1 Like
#12

I will look at that today. There shold be a way to do it without meticulously coding each cell with INDIRECT.
Thx!

#13

=INDEX(INDIRECT("$Sheet1."&CELL("address"),1))
See @karolus comment.

#14

Useless use of INDEX…

=INDIRECT("Sheet1."&CELL("address"))  #does the same
1 Like
#15

How is : =INDIRECT(“Sheet1.”&CELL(“address”))
Different from : =INDIRECT(“SHEET1.A1”)
?

Thx

#16

try both from Sheet2.A1 and copy down and to the right…
the second changes never from its origin, the first adapt the reference relativ to formula-cell

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.