Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    $ and holding the cell placement in a formula issue

    Using the formula below and having an issue. I'm using the $ to "hold" the values of cells C5 and D98. This works great until I drag the formula down to the next cell. When I do the value changes to =SUM($C6:$C6,$C6*$D99). I thought the $ would hold the cell placement. I only need the value in D98 to remain a constant. Any idea what I am doing wrong? Thanks..

    =SUM($C5:$C5,$C5*$D98)

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    The $D98 only keeps the Column static. Use $D$98 to keep both the row and column static.
    The other option is to name cell D98 {creating a static reference} and then use the name in your formulas.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Celle addressing

    Quote Originally Posted by gbanks View Post
    Using the formula below and having an issue. I'm using the $ to "hold" the values of cells C5 and D98. This works great until I drag the formula down to the next cell. When I do the value changes to =SUM($C6:$C6,$C6*$D99). I thought the $ would hold the cell placement. I only need the value in D98 to remain a constant. Any idea what I am doing wrong? Thanks..

    =SUM($C5:$C5,$C5*$D98)
    Hi there
    You are having an issue with Absolute vs Relative cell addressing.
    The format with $ symbol is known as Absolute.
    This is a critical concept to master.

    Edit a formula.
    Place cursor within a cell reference in edit bar, then repeatedly press F4 key.
    You will notice that $ symbols will be placed within the cell reference with each press of F4.
    $A1, A$1, $A$1 etc

    Cheers
    Geof

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    As others have said, the position of the $ determines whether it's the row or column that has an Absolute reference.

    If you are entering a formula, you can use the F4 key to cycle between the options. It took me years to discover this very useful feature thus: C5 -> $C$5 -> C$5 -> $C5 -> C5 etc

Posting Permissions

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