Trouble copying formulas

Discuss the spreadsheet application
Post Reply
Honorable_newbie
Posts: 4
Joined: Sun Jan 13, 2019 1:50 am

Trouble copying formulas

Post by Honorable_newbie »

Using the same spreadsheet I use every month but for some reason, today I am having so many formula problems.

!. Normally I drag the formula down the column and it will copy the formula. Today it copies the number down the column instead of copying the formula and recalculating each cell.

2. Are there "settings" I can check to ensure that formulas will work consistently withing the spreadsheet. Apparently adding columns is messing something up.
OpenOffice 4.1.1. with MacOS X Yosemite
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: trouble copying formulas

Post by RusselB »

Welcome to the Forums.

First suggestion, use Ctrl + F8 to turn on the Value Highlighting.
If the entries are numbers, rather than a formula calculation, they will turn blue (if you have custom colours, the custom colours will be, temporarily, disabled_
If you have a formula that is copying, and giving unexpected results, then the cells will turn green.
With the results being as you describe, and if the cells turn green, then check your formula for absolute cell references. These have a $ in front of the column letter and/or row number for the cell address.
If you are getting a blue colour, then, somewhere, your formula has turned into a value. To correct that you have to either find a cell that still has the formula in it, or re-write the formula.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Honorable_newbie
Posts: 4
Joined: Sun Jan 13, 2019 1:50 am

Re: trouble copying formulas

Post by Honorable_newbie »

I tried Ctrl + F8 but nothing changed colors. ??? The formula in the cells is correct but it is not calculating...apparently just copying the number. Is there some other settings I should check?
OpenOffice 4.1.1. with MacOS X Yosemite
Honorable_newbie
Posts: 4
Joined: Sun Jan 13, 2019 1:50 am

Re: trouble copying formulas

Post by Honorable_newbie »

When I type in the formula "=H5*0.36" it adds in I6 so it reads "=H5*0.36I6" ??? not sure why extra characters are being added to the formula?
OpenOffice 4.1.1. with MacOS X Yosemite
Honorable_newbie
Posts: 4
Joined: Sun Jan 13, 2019 1:50 am

Re: trouble copying formulas

Post by Honorable_newbie »

I have formatted the cells as number and also tried currency (since it is money). Doesnt' help. ???
OpenOffice 4.1.1. with MacOS X Yosemite
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: trouble copying formulas

Post by RusselB »

The formatting won't make a difference to Calc. All that does is change the appearance of the information.
As to why the characters are being added, I have no idea, but that does sound familiar.
I'll see if I can find the other topic(s) that I'm thinking of.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply