OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Creating a Budget Calendar

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Sunman
Newbie
Newbie


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

PostPosted: Sun Nov 04, 2007 4:09 pm    Post subject: Creating a Budget Calendar Reply with quote

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. Smile
Back to top
View user's profile Send private message
davidh182
OOo Advocate
OOo Advocate


Joined: 01 Apr 2004
Posts: 413

PostPosted: Mon Nov 05, 2007 2:59 am    Post subject: Reply with quote

Not sure I followed it completely, but might this help?
Code:

=INDIRECT("Sheet2.A"&DAY(B1))
Back to top
View user's profile Send private message Send e-mail
jwr
OOo Advocate
OOo Advocate


Joined: 06 Sep 2006
Posts: 367
Location: Germany

PostPosted: Mon Nov 05, 2007 5:32 am    Post subject: calender concept in Calc Reply with quote

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
Back to top
View user's profile Send private message
Sunman
Newbie
Newbie


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

PostPosted: Mon Nov 05, 2007 5:39 am    Post subject: Reply with quote

Yes! That's it perfectly. Thank you David. I knew there had to be a simple way to do it.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group