Relative and Absolute Reference to Cells in a Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jimt - 15 Aug 2007 13:48 GMT

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

Reply to this Message

jimt - 15 Aug 2007 13:55 GMT

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

Reply to this Message

Bob Phillips - 15 Aug 2007 13:58 GMT

=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)))

Signature

HTH

Bob

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

> Hi all,
>
[quoted text clipped - 33 lines]
>
> thanks

Reply to this Message