Forum Home
Press F1
Thread ID: 103949	2009-10-11 22:00:00	Any OpenOffice.org Users out there?	Yorick (8120)	Press F1

Post ID	Timestamp	Content	User
819528	2009-11-13 23:53:00	Hi I'd like to see a solution (which can be turned on (default) or off as desired) in calc to the followinf scenario with relative formulae - after all, if you delete rows the formulae get "tidied up": I've set up a simple spreadsheet for my monthly budget. In columns D, E and F I'm going to put Withdrawals, Deposits and Balance. Assuming that the entry for the first day of each month will be in row 5, in F6 to get the balance thus far I can enter the formula =F5-D6+E6 and then fill this down. However if an unexpected deposit or balance comes in I need to insert an extra row and enter the new data – assume I do this on row 12. At the moment when I do that the the cell F12 is now devoid of a formula so I have to go back to F11 and fill that formula down for the rest of the month, which seems a waste of time (imagine I had done it at row 12 of 2000 rows!!). Is there any way of getting the relative formula to appear automatically in the newly inserted row and thus be reflected in all the rows beneath it without filling it down manually? Thanks I think I know how to solve this in excel, using macros, but no idea how to in OO. Rather than drag all your cells down, does double clicking the bottom right corner work in OO like it does in excel?	the_bogan (9949)
819529	2009-11-15 10:26:00	Paste special didn't fix it for me, but what else it does is randomly it will change the total of a column to: ### That's the cue saying: "Column not wide enough for cell content" right click on the top of the column, not the cell, the box with the column letter in it. then click "Optimal column width" I usually set mine for 0.2, but whatever suits. same principle works for row height as well Each time I wrestle with it because I have forgotten how I got to the fix but if I poke around enough, eventually I rectify it.... till next time. Some times I paste from trademe.... other times I manual type the data. Could there be a way to force the entire column to always have the correct format?I start from a template I made and the figures get added over a period of time and with numerous restarts of the program. I guess if I use the prog day in - day out, I'd pick up on the why's and whynots. Still, if any reader can throw some light onto this situation it would be fantastic. Great programs! Fantastic work. There is an extension that you can use to force formatting. It is a bit of a sledgehammer but it is useful all the same. get it here (extensions.services.openoffice.org). I haven't tried it with Calc but used it a lot in writer. I'd be interested to hear how it goes.	Yorick (8120)
819530	2009-11-15 10:36:00	Another little trick I'd like is to be able to go "Save As" and have it suggest the first line of text as the document title. I've actually had that request before from people used to using Office 95, 97 etc, which do that by default. However it's not good file management practice which is why I don't like it. However you can create a macro to do that if you absolutely need it. Personally I'd prefer it to default to creators name then numerical date form (yyyymmdd) then a sequential number. Much easier to search and archive.	Yorick (8120)
819531	2009-11-15 10:50:00	I wonder how many people actually keep using it though? Hard to say, because stats are hard to come by, but then a similar sort of instance occurs with MS in their bulk licensing . For instance in schools it used to work that MinEdu had to pay for MSO licenses for every machine in the school even if you weren't using it or even couldn't use it . So that would inflate their numbers Plenty of people download Norton or get it given but how many actually keep using it? (Too many but I digress) but then as I said in an earlier post OOo users can copy the software and hand it on and many do . . . . can't do that with Norton or at least people don't tend to .	Yorick (8120)
819532	2009-11-15 12:40:00	"Optimal column width" Thanks, that fixed it. Am absolutely wrapped. Will try the extension out soon.:thumbs:	Tbird650 (6754)
819533	2009-11-15 21:26:00	Originally Posted by tuiruru Hi I'd like to see a solution (which can be turned on (default) or off as desired) in calc to the followinf scenario with relative formulae - after all, if you delete rows the formulae get "tidied up": I've set up a simple spreadsheet for my monthly budget. In columns D, E and F I'm going to put Withdrawals, Deposits and Balance. Assuming that the entry for the first day of each month will be in row 5, in F6 to get the balance thus far I can enter the formula =F5-D6+E6 and then fill this down. However if an unexpected deposit or balance comes in I need to insert an extra row and enter the new data – assume I do this on row 12. At the moment when I do that the the cell F12 is now devoid of a formula so I have to go back to F11 and fill that formula down for the rest of the month, which seems a waste of time (imagine I had done it at row 12 of 2000 rows!!). Is there any way of getting the relative formula to appear automatically in the newly inserted row and thus be reflected in all the rows beneath it without filling it down manually? This is the work-around that I would use: right click on the row header so that a row with all the formulas is selected and copy, insert row in the normal manner and then right click on the row header and select paste Then overwrite any pasted data in the data cells To avoid cockups if I knew this would happen regularly, I would maintain a row with formulas but no data so I could copy it easily	Yorick (8120)
819534	2009-11-16 01:08:00	This is the work-around that I would use: right click on the row header so that a row with all the formulas is selected and copy, insert row in the normal manner and then right click on the row header and select paste Then overwrite any pasted data in the data cells To avoid cockups if I knew this would happen regularly, I would maintain a row with formulas but no data so I could copy it easily There is another method which just occurred to me which is probably easier, right click on a row with all the formulas you want and click copy, then right click and paste special. In the dialogue uncheck "Paste All" and leave just Formulas and formats checked. Click the the "Shift Cells - down" Radio button and then OK. That will add another row with formulas and formatting intact	Yorick (8120)
819535	2009-11-16 04:42:00	Sorry if this is a bit long winded but I thought the people that made suggestions deserved some feedback. Thanks for the replies I've had about my formulae scenario. I actually posted this in the CALC section of the OOo forum and it's the only post that I've done there that hasn't got a resolution. A guy called Villeroy suggested that he had written a Python script that could do the job but warned that Python seemed to be “broken” (he's apparently from Germany) (Yorick do you know why it's broken?)in OOo 3and it didn't work and he hasn't replied to my further queries. Maybe my reference to 2000 rows clouded the issue a bit – it was merely to point out that if you're doing the same thing over and over on a computer then there is probably a better way of doing it and thus highlighting 1988 rows below row 12 was a bit of a waste. So, Yorick, your couple of suggestions don't really work for me (unless I am misunderstanding them). Certainly, in the second one, in my spreadsheet, it did copy the formula but did not take care of the relative address ie if I created a new row 5 the actual formula from row 4 was copied. The best work around to date (which I will post on my thread on the OOo forum) comes from the little “trick” in Excel (which I wasn't aware of) that The Bogan alluded to (so thanks to you mate). Here it is -remember the formula I am working with is in, say, C4, =C3-A4+B4 (ie BALANCE=PREVIOUS BALANCE-DEBIT+CREDIT) and that would be filled down for say 20 rows. So, I insert a new row above row 11 and enter a new debit in column A. Provided I then enter a zero in column B I can then go to any cell in column C above the new row, double click on the little box in the bottom right hand corner, and it will copy the formula down until it hits a row with a blank data cell in it. I thought that having to put the zero in was a bit of a pain but it makes sense cos it stops the formula being copied into the data that might be the following month's transactions. I hope that's understandable – it's not a perfect solution but on the way to it?	tuiruru (12277)
819536	2009-11-16 04:47:00	I've actually had that request before from people used to using Office 95, 97 etc, which do that by default. However it's not good file management practice which is why I don't like it. However you can create a macro to do that if you absolutely need it. Personally I'd prefer it to default to creators name then numerical date form (yyyymmdd) then a sequential number. Much easier to search and archive. Yeah, I know, but for us non power users it would still be a nice option! After all, you can't let M$ Office get one over on you!	tuiruru (12277)
819537	2009-11-16 13:56:00	Sorry if this is a bit long winded but I thought the people that made suggestions deserved some feedback . Thanks for the replies I've had about my formulae scenario . I actually posted this in the CALC section of the OOo forum and it's the only post that I've done there that hasn't got a resolution . A guy called Villeroy suggested that he had written a Python script that could do the job but warned that Python seemed to be “broken” (he's apparently from Germany) (Yorick do you know why it's broken?)in OOo 3and it didn't work and he hasn't replied to my further queries . Maybe my reference to 2000 rows clouded the issue a bit – it was merely to point out that if you're doing the same thing over and over on a computer then there is probably a better way of doing it and thus highlighting 1988 rows below row 12 was a bit of a waste . So, Yorick, your couple of suggestions don't really work for me (unless I am misunderstanding them) . Certainly, in the second one, in my spreadsheet, it did copy the formula but did not take care of the relative address ie if I created a new row 5 the actual formula from row 4 was copied . The best work around to date (which I will post on my thread on the OOo forum) comes from the little “trick” in Excel (which I wasn't aware of) that The Bogan alluded to (so thanks to you mate) . Here it is -remember the formula I am working with is in, say, C4, =C3-A4+B4 (ie BALANCE=PREVIOUS BALANCE-DEBIT+CREDIT) and that would be filled down for say 20 rows . So, I insert a new row above row 11 and enter a new debit in column A . Provided I then enter a zero in column B I can then go to any cell in column C above the new row, double click on the little box in the bottom right hand corner, and it will copy the formula down until it hits a row with a blank data cell in it . I thought that having to put the zero in was a bit of a pain but it makes sense cos it stops the formula being copied into the data that might be the following month's transactions . Heh, I'm obviously a bit thick, I see the problem now . Is there any reason why it is necessary to maintain a balance following each entry rather than having a single current balance reference . If for instance you ran a running total at the bottom of your Debit and credit columns then Current Balance would be Opening balance(absolute reference) plus Credit total minus Debit total . This would mean that you could reference a single cell for current balance . A bit like the attachment . This means also there is only two relative references and you're not having to paste formulas and it's easy to keep track of your current balance if you get to the fabled 2000 rows . :) I hope that's understandable – it's not a perfect solution but on the way to it? Likewise I hope I got it right .	Yorick (8120)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17