Relative and Absolute Reference to Cells in a Formula

[jimt]

Hi all,

I can't find how to do this...

I am working in worksheet Sheet2 and have inserted this formula in cell C3


=IF(B3=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))



the formula works OK.



Now, if I copy and paste the above formula to Sheet2!C4

the formula automatically changes to:

=IF(B4=7,Sheet1!B50*Sheet1!B62,IF(B4=6,Sheet1!B49*Sheet1!B62,IF(B4<6,Sheet1!B48*Sheet1!B62,0)))

so all references to cells are incremented of 1 unit.



My problem is that I want only the reference to the B column cells on
Sheet2 to change automatically, so all other references to cells in the
formula should be absolute, not relative.




So my formula should automatically change to

=IF(B4=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))


when I copy it into B4



and to

=IF(B5=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))


when I copy it into B5, and so on.


Is there a way to tell excel to not incremented those cell references?


thanks

 

[jimt]

I repost as there was an error on the previous posting, the formula is
copied to adjacent cells on the C column, not on the B column.

thanks



the formula works OK.



Now, if I copy and paste the above formula to Sheet2!C4

the formula automatically changes to:

=IF(B4=7,Sheet1!B50*Sheet1!B62,IF(B4=6,Sheet1!B49*Sheet1!B62,IF(B4<6,Sheet1!B48*Sheet1!B62,0)))

so all references to cells are incremented of 1 unit.



My problem is that I want only the reference to the B column cells on
Sheet2 to change automatically, so all other references to cells in the
formula should be absolute, not relative.




So my formula should automatically change to

=IF(B4=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))

when I copy it into C4



and to

=IF(B5=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))


when I copy it into C5, and so on.


Is there a way to tell excel to not incremented those cell references?


thanks

 

[Bob Phillips]

=IF(B3=7,Sheet1!B$49*Sheet1!B$61,IF(B3=6,Sheet1!B$48*Sheet1!B$61,IF(B3<6,Sheet1!B$47*Sheet1!B$61,0)))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)