1

I want to reference another cell in my worksheet in such a way that when I copy the sheet, I am still pointing to the cell on the original sheet. If the reference is to a cell on a different sheet, the sheet name is preserved when the sheet is copied but if the reference is within the same sheet, Excel changes the sheet name to the destination sheet when the sheet is copied.

I've tried defining a cell name, making its scope the whole workbook, and referencing the cell by name but, when I copy the sheet, Excel "helpfully" creates a duplicate name with scope of just the destination sheet.

I know I can use the "Indirect" function to reference a cell with a reference that will not change when the sheet is copied but I don't want to do that because it will fail if any structural change is made on the sheet containing the cell.

The application requires that successive copies be made of each sheet to track snapshots of the data over time but, for one aspect, I always want to refer back to a cell on the original sheet. The template for the workbook contains just the original sheet, waiting for initial data, which will then be copied to a new sheet for the next iteration of data. Can anyone help me with a suitable cell referencing technique to use that doesn't involve a re-design of the application.

1

I’m not sure I fully understand your requirements/constraints, but I’ll suggest this, anyway.  Define a VBA function

Function Original(Ref)
    Application.Volatile
    With Application.Caller.Parent
        Original = .Parent.Sheets(1).Range(Ref.Address)
    End With
End Function

Replace the 1 in Sheets(1) with the number of the sheet you want to reference, or its name, e.g., Sheet("PeterK_Master").  Now, it you want a copyable reference to Sheet1!Q42, just say Original(Q42).  This will automatically update if you insert columns to the left of Q or rows above 42 (that is what you wanted, right?).  And, if you copy/drag it around, it will automatically update like any other relative (same-sheet) reference; and, of course, you can stop that by using $s.

  • 1
    Thank you so much Scott, you seem to have understood perfectly. Copying and pasting cells never presented a problem but when making a copy of the sheet, Excel insisted on changing the sheet name part of a cell reference if it was referencing the same sheet you were copying (but not if you were pointing to a different sheet). Your solution overcomes this brilliantly. Also, if I make the function name the same as the master sheet name, it becomes pretty clear to anyone else what's going on - e.g. master!Q42 simply becomes master(Q42). – Peter K Oct 2 '14 at 12:02
  • Good idea; clarity is important. – Scott Oct 2 '14 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.