keeping formulas under control

  • Thread starter Thread starter Daveyboy
  • Start date Start date
D

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
 
First, the =sum is a hold over from Lotus which you do not need for xl.
=SUM(A2/Sheet1!B8*100)
Click to expand...
=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
 
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
Click to expand...

Gord Dibben XL2002
 
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
 
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
 
Back
Top