excel formula i'm pulling my hair out

[Guest]

ok, here it is: in A1 is the number: 120

A B C D
5 9 =(B5*A6)+B5 =C5*A1
6 10% 10 =(B6*A6)+B6 =C6*A1
7 12 =(B7*A6)+B7 =C7*A1

Ok here is my delima (please note the formula itself is not important as
I'm simplifying it for this explanation.

Ok, I copy this set of 3 rows and move down in order to do another set using
a different % like 20%, when I copy I lose the A1 in the formula, excel does
not keep my costant for A1 in the formula it moves it (depending on how far
down I copy) to be A5, A6, A7 etc. I have done everything I can think, I want
the 120 to be universal for the whole sheet so I can change that number. Is
there a way to copy and not have it change that A1 or do I have to go in and
fix that formula everytime.

 

[Guest]

All you need to do is Put a $ in front of A and 1 so it looks like this $A$1
Then this will stay constant even when copying and pasting.

 

[Guest]

The short answer: Use dollar signs to "lock-in" parts of the cell reference

=$A$1
ALWAYS refers to cell A1, no matter where you copy it to.

The longer answer:
Look for this in Excel Help:"about cell and range references"
and check the section on "The difference between relative and absolute
references"

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Ragdyer]

What you're talking about here is relative and absolute references.

$A$1
when copied down or across will not change.
The $ holds the column and row reference *during copying*.

$A$1 will "lock" column & row
A$1 will "lock" the row.
$A1 will "lock" the column.

Look this up in the Help files.

 

[VBA Noob]

You need to make A1 Absolute value.

It should read *$A$1

More info on the attached link


http://www.cpearson.com/excel/relative.htm

VBA Noob