If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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! -------------------- (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
|
|||
|
|||
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
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|