Excel reference to always the same cell

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
Hi, I have sheet1 and sheet2.

Let say I have some data in sheet1, in cell A1, A2, A3... for example. And I want to reference cell A1 in sheet2, then A2, then A3.

But the thing is if you e.g. detele the cell from sheet1, or you add some rows or columns the reference changes accordingly (to A2, or B1 or whatever...).

I want to reference cell A1 no matter what happens with the actual cell, always reference to A1.

Do you have some function for that?

Thank you very much!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,301
Office Version
  1. 2010
Platform
  1. Windows
this is a handy one that has a lot of uses. the $ sign locks the part of the cell name it is in front of,
so =A$1 would lock the row to row 1 but allow the column to change
=$A1 locks the column
or = $A$1 locks the cell so it never changes
 

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
No, that actually doesn't work!
Try to delete the cell and you get #REF error.
Delete the cell and all the other cell change their reffeence.
 

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
The reference must always be e.g. A1 no matter what happens with A1, no matter where you A1 move.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,301
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

indeed. you cant point a heap of cells to something and then delete it. that is illogical
if you meant to lock the cell and protect the worksheet, then that would work
 

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
I need to work with sheet1 as my data sheet - add and delete rows. But as you do this, you change the reference in sheet2, that musn't happen.
And I need sheet2 to always reference A1,A2,A3 etc... no matter what happens with the original cells, their location or values.
There is function INDIRECT but it doesn't work very well for me.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,728
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You could use:

=INDIRECT("Sheet1!A1")

but that is a volatile function, which will slow your workbook down if you use it a lot, and the fact that you appear to be mirroring one sheet to another sounds like bad design to me.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,728
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could also use:

=INDEX(Sheet1!A:A,1)

as long as you won't delete column A on sheet 1.
 

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
That seems like could work, but when I want to populate my sheet with this function and I start with:

=INDEX(Sheet1!A:A,1) the next reference is also =INDEX(Sheet1!A:A,1) and the next =INDEX(Sheet1!A:A,1).
How I make it =INDEX(Sheet1!A:A,1), then =INDEX(Sheet1!A:A,2), then =INDEX(Sheet1!A:A,3) etc...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,016
Messages
5,639,559
Members
417,099
Latest member
duhafnusa4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top