Problem copying formula to range of cells

[Ellen]

Is there a setting in excel 2002 to turn off the copying of formulas? I'm
working on a support call where I have no problems with the worksheet doing
this, but he does (same document). When he tries to copy the formula, it
copies the value from the cell that has the formula to all the rest of the
cells. I also had him try a new excel worksheet and it doesn't work there
either. Here's what we have tried:

Move or copy a formula (from excel help):

When you move a formula, the cell references within the formula do not
change. When you copy a formula, the cell references may change based on the
type of reference used.

1. Select the cell that contains the formula.
2. Verify that the cell references used in the formula will produce the
result you want. Switch to the type of reference you need.
3. On the Edit menu, click Copy.
4. Select the cell you want to copy it to.
5. To copy the formula and any formatting, on the Edit menu, click Paste.
6. To copy the formula only, on the Edit menu, click Paste Special, and
then click Formulas.

I have also run the detect and repair back to the default settings without
any luck.

I did find one tip that works for him, but would like to fix the problem.
If he selects the cells he wants to copy the forumla to, types the formula
and then presses CTRL+Enter and it will paste the formula correctly.

I didn't find anything useful in the knowledge base on this either.

 

[Gord Dibben]

Ellen

As you copy, do the cell references change but value does not?

Do values change if you hit F9?

If so, check Tools>Options>Calculation and check "Automatic"

Gord Dibben Excel MVP

 

[Ellen]

Thank you. He was very happy to get this working again. I figured it had to
be a setting somewhere but wasn't sure which one.

Ellen

 

[msethi]

Hi Gord

Your information on my problem of not being able to paste formulas, was
really helpful.
Is there any site where we can learn more about the Excel VBA programming.

Thanks
msethi

 

[Gord Dibben]

msethi

Don't know how you got involved in this thread of Ellen's, but for VBA
programming basics and links to sites see David McRitchie's "getting started"
site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord

 

[Bob DePass]

Gord -
I don't know how my calculate got reset to manual, but it drove me crazy all
day trying to figure out why the cell references changed, but the results
didn't. Then I found retyping the logical connectors caused the correct
values to display. I really thought I lost it this time.
Thanks for the advice.
Bob