Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referencing Cells (2000)

    Let's say in cell A1 you enter =$B$1 and then enter 10 into B1. You then cut and paste B1 to C1, if you go back to A1 the formula is automatically changed to =$C$1. Is there a way to make A1 remain =$B$1 no matter what you do with the contents of B1 (be it cut and paste, clear or delete)?

    Thanks
    Stats

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referencing Cells (2000)

    The formula =INDIRECT("B1") will do that (the quotes around B1 are obligatory here).

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Cells (2000)

    It is using Cut that is causing the formula to change.

    If you either Fill B1 across into C1, drag B1 across to C1 whilst holding down Ctrl, or drag B1 across to C1 whilst holding down the right mouse button and choose one of the Copy options from the menu, the formula in A1 will not change. You can then do whatever you want to B1.

    If you use the third method and choose Move from the menu you are effectively using Cut, and the formula changes.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Cells (2000)

    Thanks for your help and congratulations on your appointment to Moderator!!!!
    One more quick question please, is there an easy way to copy and paste this formula so that
    =indirect("A1") becomes =indirect("A2") and so on down the col. When I try it now each formula remains "A1".

    Thanks
    Stats

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Cells (2000)

    try =Indirect("A" & Row())

    you may want to +/- from the row if you are not on the same row!

    HTH

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •