keeping formulas under control

[Daveyboy]

Hello,
Could anyone answer the following. If I have the following formula in a
cell,
=SUM(A2/Sheet1!B8*100)

to work out a simple percentage and I then click on that cell and pull it
down the sheet, in the next cells I will get...

=SUM(A3/Sheet1!B9*100)

=SUM(A4/Sheet1!B10*100)

and so forth. What I would like to know, is when you drag the cell down the
page is there a way to keep part of the formula static. In the above example
is it possible to keep B8 the same in each cell whilst still changing A2 to
A3 to A4 etc? I have to go to each cell otherwise and change to B8 in each
formula, which is very time consuming and not good on the fingers.

Thanks in advance....

Dave

 

[Don Guillett]

First, the =sum is a hold over from Lotus which you do not need for xl.

=SUM(A2/Sheet1!B8*100)

=A2/Sheet1!$B$8*100
or depending on what you want, for clarity
=(A2/Sheet1!B8)*100
=A2/(Sheet1!B8*100)
$b$8 is absolute
$b8 will make the b constant
b$8 will make the 8 constant

 

[Gord Dibben]

Daveyboy

=SUM(A2/Sheet1!$B$8*100)

The $ signs designate Absolute cell referencing.

Help>Index>"reference"(no quotes).

Look at the topic "about cell and range references".

Specifically "the difference between relative and absolute references"

Gord Dibben XL2002

Hello,
Could anyone answer the following. If I have the following formula in a
cell,
=SUM(A2/Sheet1!B8*100)

to work out a simple percentage and I then click on that cell and pull it
down the sheet, in the next cells I will get...

=SUM(A3/Sheet1!B9*100)

=SUM(A4/Sheet1!B10*100)

and so forth. What I would like to know, is when you drag the cell down the
page is there a way to keep part of the formula static. In the above example
is it possible to keep B8 the same in each cell whilst still changing A2 to
A3 to A4 etc? I have to go to each cell otherwise and change to B8 in each
formula, which is very time consuming and not good on the fingers.

Thanks in advance....

Dave

Gord Dibben XL2002

 

[L. Howard Kittle]

Hi Dave,

A small addition to Don and Gord's solutions. You can use the F4 key to
toggle through the four reference choices. Just click on the cell reference
in the formula bar and hit F4 four times slowly and see the choices.

HTH
Regards,
Howard

 

[Don Guillett]

good point

 

[Daveyboy]

Thanks for all your help. I have not had chance yet to try any of this as
the spreadsheet is at work. But it looks promising.
Thanks a million to you all.

Dave