How to always Reference Cell even if deleted?

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,510
Office Version
  1. 2021
Platform
  1. Windows
How can I always reference Cell(1,1) even if that cell is deleted?
For example I want cell(1,2) to always show what is in cell(1,1) if I delete cell(1,1) I still want cell(1,2) to show what's in cell(1,1). There will always be a cell(1,1) even if you delete the cell another cell takes it place.

I have tried defining cell(1,1) but if I then delete cell (1,1) I get a # Ref in cell(1,2).

This is just a example I may want cell(20,13) to refer to cell(1,1) so I cannot use a offset function.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Thank you for your help Aladin. I was having a brain freeze. I also discovered =OFFSET(B1,,-1) would work also.


INDIREC("A1") survives any deletion.

INDEX(A:A,1) cannot survive deletion of column A.

OFFSET(B1,,-1) does not survive column deletion or row deletion.

Both INDIRECT and OFFSET are volatile.
 
Upvote 0
How about
=INDEX(Sheet1!1:1048576,1,1)

It will survive cell, row and column deletion and insertion.

Although its precedent range is all of sheet1, it is not volatile. It will not recalculate if a cell off Sheet1 is changed, as INDIRECT would.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top