Pasted formula doesn't work unless "re-initialized"

[John Richards]

I'll apologize in advance if this has already been answered but I couldn't
come up with a search phrase to find an answer.

I have an existing worksheet which I have been using successfully for all of
2006 but now I want to add rows for 2007. Whether I copy and paste, or drag
a formula down from the last existing row, the new cells display the value
that was in the original cell rather than the new value that the formula in
the new cell should rerturn. When I click on the cell to display the
formula in the formula bar, the formula looks fine but if I select any
portion of the formula, even the equal sign, and then hit return (what I am
calling "re-initializing"), the formula stays the same but the value in the
new cell changes to the correct value.

I have tried duplicating the problem on a new worksheet but haven't been
able to do it.

Any ideas?

Thanks
John

 

[Nick Hodge]

John

Check Tools>Options>Calculation>Automatic is checked

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[John Richards]

Nick,

That was it - thanks! I have no idea how it got changed to manual but
everything (almost) works now the way I expect. The "almost" refers to
copying an existing row of values and formulas and then using "paste
special" and checking "formulas", the formulas paste correctly but the
values are also pasted. I can live with this but was wondering if you might
have an answer to this one also.

Thanks again

John

 

[Nick Hodge]

John

It is set by the last workbook opened, so that's how it got set that way,
you must have opened another workbook with the calc set to manual.

That's the way paste special>formulas works, if there isn't a formula it
just pastes the values

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[John Richards]

John

It is set by the last workbook opened, so that's how it got set that way,
you must have opened another workbook with the calc set to manual.

Hmm . . . guess I'll have to do some reading about "options".

That's the way paste special
formulas works, if there isn't a formula it just pastes the values

Doesn't make sense to me but it's only a minor inconvenience.

Thanks a lot for the help
John

 

[Gord Dibben]

John

Nick's posting contained a typo.

He should have said "first" workbook rather than "last" workbook opened.

Yes......this calculation mode is a feature/bug that has been around for a long
time through versions of Excel and not fixed in 2007 version.


Gord Dibben MS Excel MVP

 

[Nick Hodge]

Gord/John

Ooops thanks for picking that up Gord...always to much of a hurry!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Gord Dibben]

Yay!

My typo for and against is now about 2 for 87<g>


Gord