Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 22nd 10, 12:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 137
Default Referencing a cell even if its contents are moved moved/replaced

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?

  #2   Report Post  
Old May 22nd 10, 12:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Referencing a cell even if its contents are moved moved/replaced

=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
  #3   Report Post  
Old May 22nd 10, 12:47 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 132
Default Referencing a cell even if its contents are moved moved/replaced

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?

  #4   Report Post  
Old May 22nd 10, 03:01 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 524
Default Referencing a cell even if its contents are moved moved/replaced

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...
  #5   Report Post  
Old May 22nd 10, 07:07 PM
Senior Member
 
First recorded activity by ExcelBanter: Sep 2008
Location: Hyderabad
Posts: 237
Thumbs up

Quote:
Originally Posted by Darren View Post
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?
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
__________________
Thanks
Bala


  #6   Report Post  
Old May 22nd 10, 08:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 137
Default Referencing a cell even if its contents are moved moved/replac

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?

  #7   Report Post  
Old May 22nd 10, 08:31 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 137
Default Referencing a cell even if its contents are moved moved/replac

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?

  #8   Report Post  
Old May 22nd 10, 08:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 137
Default Referencing a cell even if its contents are moved moved/replac

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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintain cell reference after value is moved tsfleet Excel Worksheet Functions 2 January 14th 10 07:00 PM
Maintain cell reference after value is moved tsfleet Excel Worksheet Functions 1 January 14th 10 06:15 PM
Moved automatically to the next specified cell Fred's Excel Discussion (Misc queries) 5 March 13th 09 05:58 AM
How do I keep a hyperlink valid if the link to cell is moved? kendeboer01 Excel Discussion (Misc queries) 1 May 5th 08 11:45 PM
can the data be moved to another cell not using any macro? Mojtaba Ghassemi Excel Worksheet Functions 1 March 6th 08 08:53 AM


All times are GMT +1. The time now is 07:54 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017