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

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

--
Don Guillett
SalesAid Software

"cardingtr" wrote
in message ...

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. ToolsOptionsEdit 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" 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. ToolsOptionsEdit 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