How to quickly paste almost same formula to set rows?

Let's say I have a long formula I don't want to rewrite on each cell.
I want to paste it to other cells but also the formula should change to reflect its new location.

Heres the formula:
=(B3="mild")*1+(B3="moderate")*2+(B3="mod-severe")*3+(B3="severe")*4+(B3="intolerable")*5

The above formula is in C3 referring to B3.

I want to paste it down starting in C4 but how will the fomula automatically change from B3 to B4, B5, B6.....without typing it manually?

Thanks.

The easy way is to grab the fill handle (lower right corner of source cell)
and drag down.

--
Don Guillett
SalesAid Software
[email protected]
"cardingtr" <[email protected]> wrote
in message news:[email protected]...
>
> Let's say I have a long formula I don't want to rewrite on each cell.
> I want to paste it to other cells but also the formula should change to
> reflect its new location.
>
> Heres the formula:
> =(B3="mild")*1+(B3="moderate")*2+(B3="mod-severe")*3+(B3="severe")*4+(B3="intolerable")*5
>
> The above formula is in C3 referring to B3.
>
> I want to paste it down starting in C4 but how will the fomula
> automatically change from B3 to B4, B5, B6.....without typing it
> manually?
>
> Thanks.
>
>
> --
> cardingtr
> ------------------------------------------------------------------------
> cardingtr's Profile:
> http://www.excelforum.com/member.php...o&userid=27027
> View this thread: http://www.excelforum.com/showthread...hreadid=514167
>

Hi,
another way is single click on C3, then while holding down the Shift
key single click on the lowest column C cell that you want to have your
formula, then press Ctrl + the d key to fill down your formula.
If Don's way did not work then the "Allow cell drag and drop" option is
probably unchecked. Tools>Options>Edit then click on "Allow cell drag
and drop" will turn it back on.
Another shortcut is to double click the fill handle. Excel detects the
values in column B and fills your formula down as far as the column B
values.

Ken Johnson

Or you can select the cells in the address box C3:C200
before using Ctrl+D with the formula in C3.


More on use of fill handle and similar mouse and keyboard shortcuts.
http://www.mvps.org/dmcritchie/excel/fillhand.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ken Johnson" <[email protected]> wrote ...
> Hi,
> another way is single click on C3, then while holding down the Shift
> key single click on the lowest column C cell that you want to have your
> formula, then press Ctrl + the d key to fill down your formula.
> If Don's way did not work then the "Allow cell drag and drop" option is
> probably unchecked. Tools>Options>Edit then click on "Allow cell drag
> and drop" will turn it back on.
> Another shortcut is to double click the fill handle. Excel detects the
> values in column B and fills your formula down as far as the column B
> values.
>
> Ken Johnson
>