$ and holding the cell placement in a formula issue

Topic

New Reply

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)

Reply | Quote

Replies

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote