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
That is pointing at row 1, not row 8. ROWS returns the number of rows in a range, and that is a one row range. If you want row 8, use: ROWS($A$1:$A8) and note that this part of the formula should not point at the other sheet.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
This works perfectly: =INDEX('PHM-rozuctovanie pravidelne'!U:U; 8)
I just can't autopopulate the formula.
 

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
This might be it:
=INDEX('PHM-rozuctovanie pravidelne'!U:U;ROWS('PHM-rozuctovanie pravidelne'!$U$1:$U8))
 

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
As I just said:

=INDEX('PHM-rozuctovanie pravidelne'!U:U; ROWS($A$1:$A8))

You do not want the ROWS part to refer to the other sheet where you may be deleting rows.
 

Talicius

New Member
Joined
Aug 8, 2020
Messages
16
Office Version
  1. 2016
As I just said:

=INDEX('PHM-rozuctovanie pravidelne'!U:U; ROWS($A$1:$A8))

You do not want the ROWS part to refer to the other sheet where you may be deleting rows.

Sorry I thought it didn't work. But now it works it was the $A$1:$A8 and not $A$8:$A8 that made the error.
Seems like this should work. :)
 

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