Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Bluffton, South Carolina, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hold one cell constant in formula (2003)

    I want to hold one cell constant in a formula that I drag down a column. Ex: b1*a1, then b2*a1, then b3*a1 where a1 remains in each iteration of the formula.
    I know there is a method to do this involving placing a $ in the formula but I cannot make it work.

    Can anyone help?

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hold one cell constant in formula (2003)

    To make a part of a formula not change you can change the first formula to:
    <code>=b1*$a$1</code>

    It is called an absolute cell reference, for a discussion on this see Relative And Absolute Addressing

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Hold one cell constant in formula (2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> sboyd

    In addition to Tony's excellent solution, you can also use names for ranges, a single cell range, or a multiple cell range.

    For example, you can name A1 TheConstantIWantToUse and then do something like:

    =B1*TheConstantIWantToUse
    =B2*TheConstantIWantToUse
    =B3*TheConstantIWantToUse

    and so on. Just remember that A1, B2, G2520 are simply names of each of these cells. They just happen to be made of the column and row designations where the cell intersects. There is a lot of flexibility in this area. Check "named ranges" in the Online help, and you can get some examples and more info from:

    Charles H. Pearson web site
    A Google search

    Hope this helps.

    Cordially

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Star Lounger
    Join Date
    Oct 2002
    Location
    Bluffton, South Carolina, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hold one cell constant in formula (2003)

    Brilliant Tony. Thanks for the short answer and the fuller tutorial. Much appreciated.
    Susan

Posting Permissions

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