I want to reference a cell no matter what happens to it.

Simple example:

From A1 down:
A1=B1
A2=B2
A3=B3

From B1 down:
5
10
15

If I cut and insert B3(15) into B2, the A column would then read as:

A1=B1
A2=B3
A3=B2

How do I keep the A column referencing the original cell?

=indirect("b1")
will always point to B1 -- no matter if you delete or insert any
rows/columns/cells.



Darren wrote:

I want to reference a cell no matter what happens to it.

Simple example:

From A1 down:
A1=B1
A2=B2
A3=B3

From B1 down:
5
10
15

If I cut and insert B3(15) into B2, the A column would then read as:

A1=B1
A2=B3
A3=B2

How do I keep the A column referencing the original cell?

--

Dave Peterson

Copy and paste the below formula in A1 cell and drag it to the remaining
cells of Column A.

=INDIRECT("B"&ROW())

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Darren" wrote:

I want to reference a cell no matter what happens to it.

Simple example:

From A1 down:
A1=B1
A2=B2
A3=B3

From B1 down:
5
10
15

If I cut and insert B3(15) into B2, the A column would then read as:

A1=B1
A2=B3
A3=B2

How do I keep the A column referencing the original cell?

On Sat, 22 May 2010 04:21:01 -0700, Darren wrote:

I want to reference a cell no matter what happens to it.

Simple example:

From A1 down:
A1=B1
A2=B2
A3=B3

From B1 down:
5
10
15

If I cut and insert B3(15) into B2, the A column would then read as:

A1=B1
A2=B3
A3=B2

How do I keep the A column referencing the original cell?

Instead of Paste, use Paste Special » Values.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

if you are familar with VBA code, you can also try range.value property it always refers to constanst cell
example below


Dim a As Variant
a = ActiveWorkbook.Sheets("sheet1").Range("A1").Value
ActiveWorkbook.Sheets("sheet2").Range("A1").Value = a

sheet2!A1 always refers to sheet1!A1

all the best

Thankyou so much for the replies. Your formula works great in the simple
example I presented you with. can we please now take it a step further.

Can the same formula be adapted to reference a cell on another worksheet? I
have tried but, as you can see by me posting, have had no success.

For ease lets call the worksheets Sheet1 and Sheet2.
I want A1 on sheet1 to read A1 on Sheet2 using the INDIRECT command, through
B1, C1, etc. Can this be done?

"Ms-Exl-Learner" wrote:

Copy and paste the below formula in A1 cell and drag it to the remaining
cells of Column A.

=INDIRECT("B"&ROW())

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Darren" wrote:

I want to reference a cell no matter what happens to it.

Simple example:

From A1 down:
A1=B1
A2=B2
A3=B3

From B1 down:
5
10
15

If I cut and insert B3(15) into B2, the A column would then read as:

A1=B1
A2=B3
A3=B2

How do I keep the A column referencing the original cell?

Please ignore the previous post and refer to this edited version.

"Darren" wrote:

Thankyou so much for the replies. Your formula works great in the simple
example I presented you with. can we please now take it a step further.

Can the same formula be adapted to reference a cell on another worksheet? I
have tried but, as you can see by me posting, have had no success.

For ease lets call the worksheets Sheet1 and Sheet2.
I want A7 on sheet1 to read A1 on Sheet2 using the INDIRECT command, through
A8, A9, etc. Can this be done?

"Ms-Exl-Learner" wrote:

Copy and paste the below formula in A1 cell and drag it to the remaining
cells of Column A.

=INDIRECT("B"&ROW())

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Darren" wrote:

I want to reference a cell no matter what happens to it.

Simple example:

From A1 down:
A1=B1
A2=B2
A3=B3

From B1 down:
5
10
15

If I cut and insert B3(15) into B2, the A column would then read as:

A1=B1
A2=B3
A3=B2

How do I keep the A column referencing the original cell?

Hehe, figured it out. =INDIRECT("Sheet2!A"&ROW(A1)) in Sheet1 A7, then copied
down.
Thankyou all for what you do here. Without your help a lot of people would
be lost.

"Darren" wrote:

Please ignore the previous post and refer to this edited version.

"Darren" wrote:

Thankyou so much for the replies. Your formula works great in the simple
example I presented you with. can we please now take it a step further.

Can the same formula be adapted to reference a cell on another worksheet? I
have tried but, as you can see by me posting, have had no success.

For ease lets call the worksheets Sheet1 and Sheet2.
I want A7 on sheet1 to read A1 on Sheet2 using the INDIRECT command, through
A8, A9, etc. Can this be done?

"Ms-Exl-Learner" wrote:

Copy and paste the below formula in A1 cell and drag it to the remaining
cells of Column A.

=INDIRECT("B"&ROW())

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Darren" wrote:

I want to reference a cell no matter what happens to it.

Simple example:

From A1 down:
A1=B1
A2=B2
A3=B3

From B1 down:
5
10
15

If I cut and insert B3(15) into B2, the A column would then read as:

A1=B1
A2=B3
A3=B2

How do I keep the A column referencing the original cell?