excel formula i'm pulling my hair out

[murksaxet]

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.

[Neil_Dell]

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.

"murksaxet" wrote:

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

[Ron Coderre]

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


"murksaxet" wrote:

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

[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

[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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"murksaxet" <[email protected]> wrote in message
news:[email protected]...
> 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.