Prevent change to refs in second worksheet when drag cells in firs

L

LarryInBrookline

I have a complex calendar in Excel. Each week, admins Ctrl-drag cells among
different locations in the primary calendar worksheet. I have a second
worksheet that needs to list those cell values in a fixed configuration, e.g.
the value in Sheet1!A1 needs to always appear in the cell of Sheet2!G10.
Sheet2!G10 contains the formula: “Sheet1!$A$1â€. If the user drags Sheet1!A1
to Sheet1!A3, the formula in Sheet2!G10 gets automatically updated to
“Sheet1!$A$3â€. I have tried all combos of locking and protecting to prevent
this updating. Is there a way to do this?
 
J

Jim Rech

Try referencing the other sheet this way: =INDIRECT("Sheet1!A1")

--
Jim
message |I have a complex calendar in Excel. Each week, admins Ctrl-drag cells among
| different locations in the primary calendar worksheet. I have a second
| worksheet that needs to list those cell values in a fixed configuration,
e.g.
| the value in Sheet1!A1 needs to always appear in the cell of Sheet2!G10.
| Sheet2!G10 contains the formula: "Sheet1!$A$1". If the user drags
Sheet1!A1
| to Sheet1!A3, the formula in Sheet2!G10 gets automatically updated to
| "Sheet1!$A$3". I have tried all combos of locking and protecting to
prevent
| this updating. Is there a way to do this?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Relative and Absolute Reference to Cells in a Formula 2
Loosing formula when changing row #’s 2
Formula for drag and copy 0
How to Auto duplicate cell contents? 1
Formula for drag and copy and paste 0
cells to behave like check boxes 4
format changes when copying from one cell to another 2
Worksheet functions using one out of every five cells 0

Top