Excel problems with copying a formula

Closed
Uttara Parikh - Mar 18, 2010 at 06:16 AM
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 - Apr 23, 2010 at 02:41 PM
My excel is behaving funny. When I copy a cell formula across the row or column, it copies the formula correctly, but does not give the corresponding value!!

Example: If Cell B1 has the value 20. Suppose I type =B1+1 inside cell B2, that gives me 21. Now, when i use Ctrl+C to copy cell B2 to cell B3, I should get the value 22. Instead, it still reads 21. However, the formula inside the cell correctly reads =B2+1. Its bizarre.

Autofill gives me the same problem. BUT, if i manually type in the formula into cell B3, it gives me the correct value 22. And that defeats the purpose of Excel, because I cannot manually type a formula into every single cell!!!!

Please HELP!

2 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Mar 18, 2010 at 11:37 AM
does the value get updated if you press recalc ? i think that is F9. Also look under options. If you are using 2007 then that would be under office button (that round thing at the top). Once in Excel option, look under formula
0
You have to put dollar signs ($) inbetween the cell reference B1 in the formula.

Eg. =$B$1+1

Then when you use autofill, it keeps the cell reference $B$1 instead of being the cell above.
-1
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Apr 23, 2010 at 02:41 PM
he wants it to be the cell above.
0