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!
 

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 use:
Excel Formula:
=INDEX(Sheet1!A:A,ROWS($A$1:$A1))

If the formula is in column A, then use a different column for the reference in the ROWS function.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
You could use:
Excel Formula:
=INDEX(Sheet1!A:A,ROWS($A$1:$A1))

If the formula is in column A, then use a different column for the reference in the ROWS function.
When I type it like this it doesnt work. This ROWS($A$1:$A1) references the sheet2 and not sheet1.
 

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
Yes, that is as it should be. It's only there to return the 1, 2, 3 etc as you copy the formula down. So what exactly do you mean by "doesn't work"?
 

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
Yes, that is as it should be. It's only there to return the 1, 2, 3 etc as you copy the formula down. So what exactly do you mean by "doesn't work"?
It returns 0 as value.
 

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

And what is in A1 on Sheet1? Nothing?
 

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

Then the most likely option is that you created a circular reference; what cell is that formula in?
 

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
The cell with the INDEX function is in another sheet, can you upload here Excel file how it should look, please? Thank you very much.
 

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
It is not possible to upload files here.

Which cell is the formula in, and what is the exact formula?
 

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
=INDEX('PHM-rozuctovanie pravidelne'!U:U;ROWS('PHM-rozuctovanie pravidelne'!$U$8:$U8))
This gives "0" even tho I have value in sheet 'PHM-rozuctovanie pravidelne' and cell U8.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,014
Messages
5,639,555
Members
417,098
Latest member
steverob

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