[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Sunman
Newbie

Joined: 23 May 2005
Posts: 2
Location: Vancouver, BC

 Posted: Sun Nov 04, 2007 4:09 pm    Post subject: Creating a Budget Calendar Hi All. Forgive me if this has been answered. I tried searching with various keywords but I couldn't find exactly what I'm looking for. I'm trying to create a Calendar in Calc. I've got the basic calendar to create perpetually, based on what month it is when you open it (the idea is that I will print out a new calendar each month). A bit of a background on this: Sheet1 has a calendar for the current month. One box is 5 cells high and 2 cells wide (let's call it A1 to B6). In A2 is the date, formatted to show the Day. Row 2 is blank. The bill name (if any) starts in A3 and the amount of the bill is in B3. If there is a second bill, it displays in A4 and B4 and so on for a maximum of 4 bills per day. In Sheet2 I have the list of bills. Row 1 is for Day 1. Row 2 for Day 2 and so on. A1 is the bill for Day1 of the month and B1 is the amount of the bill. C1 and D1 is for the second bill (if any) and so on.. Got it so far? What I am trying to figure out is a formula for each day to display the bill on the right day. In this example, assuming the first of the month is on the first Sunday, the formula in A3 would basically show Sheet2.A1. But what if, like in November, the first day of the month is on Thursday? Ultimately, I would like to just put =Sheet2.A(DAY(B1)) -- I need to grab the day number from the date and pass it in a variable. The only way I've come up with to do this so far is one loooong IF statement. IF(DAY(B1)=1;Sheet2.A1;IF(DAY(B1)=2;Sheet2.A2;IF(DAY(B1)=3;Sheet2.A3 ... IF(DAY(B1)=31;Sheet2.B1))))))))))))))))))))))))))))))) Please help. Is there a better way to do this? I looked at a couple of Calendar programs (like Mozilla's) but I need to also run some calculations on the bills (income, outgo, difference, figure out how much to put in savings, etc) so I think I need to do this in Calc. I'm sorry if this is confusing. I'm confused myself.
davidh182

Joined: 01 Apr 2004
Posts: 413

Posted: Mon Nov 05, 2007 2:59 am    Post subject:

Not sure I followed it completely, but might this help?
 Code: =INDIRECT("Sheet2.A"&DAY(B1))
jwr

Joined: 06 Sep 2006
Posts: 367
Location: Germany

 Posted: Mon Nov 05, 2007 5:32 am    Post subject: calender concept in Calc A German user created a nice calender in Calc. The calender generates 1 sheet for each month. Maybe the concept is useful to others and to be modified for a budget calender. A download is provided at the German website: http://www.datenschleuder.eu/get_dfbe25f383d42f63d391f8120cc27b23.html The calender has been published under GNU LGPL Licence. As soon as the new forum's website has been created we may provide an downloadable attachment for the calender's CALC-file at the forum's site, but unfortunately this forum does not provide any attachments. Regards -Hans-_________________Windows 2K, OOo-2.3.0, MS-IE-6.0, Firefox-1.0.4, MS Office 97, Say NO to MS OpenXML: http://www.noooxml.org/petition
Sunman
Newbie

Joined: 23 May 2005
Posts: 2
Location: Vancouver, BC

 Posted: Mon Nov 05, 2007 5:39 am    Post subject: Yes! That's it perfectly. Thank you David. I knew there had to be a simple way to do it.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum