Home 
Search 
Today's Posts 
#1




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




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




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!  (MsExlLearner)  "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




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




Quote:
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




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? "MsExlLearner" 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!  (MsExlLearner)  "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




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? "MsExlLearner" 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!  (MsExlLearner)  "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




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? "MsExlLearner" 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!  (MsExlLearner)  "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 
Display Modes  


Similar Threads  
Thread  Forum  
Maintain cell reference after value is moved  Excel Worksheet Functions  
Maintain cell reference after value is moved  Excel Worksheet Functions  
Moved automatically to the next specified cell  Excel Discussion (Misc queries)  
How do I keep a hyperlink valid if the link to cell is moved?  Excel Discussion (Misc queries)  
can the data be moved to another cell not using any macro?  Excel Worksheet Functions 