| View previous topic :: View next topic |
| Author |
Message |
Truckman
Newbie
Joined: 12 Aug 2010
Posts: 4
|
 Posted: Thu Aug 12, 2010 5:38 pm Post subject: [SOLVED] Copy/Pasted Formula doesn't compute |
 |
|
I recently switched over to OpenOffice from Excel. I have a simple check register in .xls that I've been using for some time (from Vertex42 I believe). I opened the file in OpenOffice, and copied the last line and pasted it to extend the page down (about 25 rows pasted). In Excel after I did this, when I would make an entry into either the Deposit or Withdrawl columns, the Balance column would auto-compute. I can see that the formula has been pasted in the Balance column, as it should be. But when I make entries into the Deposit or Withdrawl columns, the Balance cell stays blank.
Obviously, I'm uber-noob to OpenOffice. This is my first attempt at anything after making the transition.
Here is the formula:
| Code: | =IF(AND(ISBLANK(F722),ISBLANK(G722)),"",OFFSET(H722,-1,0,1,1)+G722-F722)
|
Last edited by Truckman on Sun Aug 29, 2010 5:50 am; edited 1 time in total |
|
| Back to top |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1875
Location: Sydney, Australia
|
| Posted: Thu Aug 12, 2010 6:42 pm Post subject: |
|
|
| Code: | | =IF(AND(ISBLANK(F722);ISBLANK(G722));"";OFFSET(H722;-1;0;1;1)+G722-F722) |
ie the commas should be semicolons.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
Truckman
Newbie
Joined: 12 Aug 2010
Posts: 4
|
| Posted: Tue Aug 17, 2010 4:09 pm Post subject: |
|
|
Sorry it took so long to get back. That did not work. The semicolons automatically changed themselves back to commas.
When I opened the original Excel sheet, I was able to make a few entries and it computed them properly. It is only when I tried to copy the formula and paste it down the page at once that it stopped working.
Now a very strange thing just happened as I was typing this post. I hit the "Undo" arrow all the way back to before I copy/pasted the above new formula with semicolons and now the formula computes for that one line only. If I make an entry in the next line, it does not compute. As silly as this sounds, is there a switch or something I need to do to "enable" the formula?
ETA: Can someone explain what exactly that formula does? It's from a check/bank register spreadsheet. That formula is in the "balance" column (H). The Withdrawl is column F and the Deposits are column G. I'm trying to make heads or tails of it using the Formula Wiki. It seems as if it's checking for any entries in the F and G cells of that particular row, then if there are not any it is entering a blank (the " "). I can't quite understand the Offset function's purpose in the formula though.
For a check register, does it need to be that complicated? |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1875
Location: Sydney, Australia
|
| Posted: Tue Aug 17, 2010 5:48 pm Post subject: |
|
|
| Truckman wrote: | | The semicolons automatically changed themselves back to commas. |
Sounds like you are using a version that uses comma delimiter instead of semicolon.
| Truckman wrote: | It is only when I tried to copy the formula and paste it down the page at once that it stopped working.
Now a very strange thing just happened as I was typing this post. I hit the "Undo" arrow all the way back to before I copy/pasted the above new formula with semicolons and now the formula computes for that one line only. If I make an entry in the next line, it does not compute. As silly as this sounds, is there a switch or something I need to do to "enable" the formula? |
Check Tools|Cell Contents|AutoCalculate should have a tick to its left.
| Truckman wrote: | | I can't quite understand the Offset function's purpose in the formula though. |
| Code: | | OFFSET(H722;-1;0;1;1) |
Returns the value that is in the cell that is one row above H722 (the -1 means go up the sheet 1 row) and in the same column as H722 (0 means don't go anywhere across the sheet). The two 1s after the 0 could have been left off. The OFFSET function can return more then one cell's value but then it has to be entered as an array function using Ctrl+Shift+Enter. | Code: | | OFFSET(H722;-1;0;1;1)+G722-F722 |
therefore adds the value in H721 to the difference between G722 and F722.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
Truckman
Newbie
Joined: 12 Aug 2010
Posts: 4
|
| Posted: Wed Aug 18, 2010 4:03 pm Post subject: |
|
|
| ken johnson wrote: | | Truckman wrote: | | The semicolons automatically changed themselves back to commas. |
Sounds like you are using a version that uses comma delimiter instead of semicolon. |
I'm using Version 3.2.0 in Ubuntu 10.04.
| ken johnson wrote: | | Check Tools|Cell Contents|AutoCalculate should have a tick to its left. |
AutoCalculate is enabled
| ken johnson wrote: | | Truckman wrote: | | I can't quite understand the Offset function's purpose in the formula though. |
| Code: | | OFFSET(H722;-1;0;1;1) |
Returns the value that is in the cell that is one row above H722 (the -1 means go up the sheet 1 row) and in the same column as H722 (0 means don't go anywhere across the sheet). The two 1s after the 0 could have been left off. The OFFSET function can return more then one cell's value but then it has to be entered as an array function using Ctrl+Shift+Enter. | Code: | | OFFSET(H722;-1;0;1;1)+G722-F722 |
therefore adds the value in H721 to the difference between G722 and F722.
Ken Johnson |
Thanks. That helps me understand what the formula does.
I'm at a loss. I can't figure out any reason why the formula on one line and above will work, but the line below it on down will not. |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1875
Location: Sydney, Australia
|
|
| Back to top |
|
|
Truckman
Newbie
Joined: 12 Aug 2010
Posts: 4
|
| Posted: Sun Aug 29, 2010 5:50 am Post subject: |
|
|
| Just finally got around to messing with it. That seems to have fixed it. Thanks again for hte help! |
|
| Back to top |
|
|
|
