[Solved] Formula help with budget sheet

[sajnoor]

Hi everyone,

After getting fed up of microsoft office and the support for odt i decided to use openoffice, however i need some help am trying to create a budget sheet,
i have 3 issues i need help and never been expert in excel or calc
1) Want to be able to select dates in row b so a calendar is available when clicked on row b
2) I have created selection list for payment method and expense category however there is large gaps between each field
3) I need to total the expense categories and need help with formula to do this

I have uploaded my draft template so you can see work i have done so far

[FJCC]

The data validity in columns C and F of the Expenses sheet was set to cell ranges like B3:IX15. It just needs to cover column B, like B3:B15. I adjusted rows 4 and 5 on the Expenses sheet in that way.
Your formula in G3 of Expense Category Totals was basically correct but it started looking in row 6 of the Expenses sheet. I adjusted that to start in row 4

Code: Select all

=IF(B3<>"";SUMIF($Expenses.$F$4:$F$8499;B3;$Expenses.$H$4:$H$8499);"")

I don't know how to make a calendar appear in column B. I suspect OpenOffice does not support that.

For unsolicited advice:
1. Use merged cells sparingly. Just make the columns wider unless there is a strong reason to merge the cells. Merged cells make it easy to mistake what the cell address is and they can make layout changes difficult.
2. Avoid spaces in Sheet names. Use an underscore instead: Expense_Categories.

[sajnoor]

FJCC Many thanks for your help with budget sheet and upload as i said bit clueluess in spreadsheets can do basic formulas, i used a template to create my own and obvisouly had a few errors with the formulas.

i have also noted your unsolicited advice for future and also renamed sheets with no spaces.

In regards to calendar see link below and please advise
viewtopic.php?f=39&t=5218

[sajnoor]

Here is budget sheet with specail thanks to fjcc, the only thing remaining is calendar i am uploading for any member to use and forums are all about communitiy

I needed to keep traces on my daily expenses and made this sheet for that purpose.

[FJCC]

The link you provided mentions a calendar control for a database. It is not possible, as far as I know, to have such a control interact with a spreadsheet. A database is a good tool for tracking expenses but would require a lot of learning.

There are shortcuts for entering dates in Calc. If I enter 3/ in a cell, I get the third day of the current month. If I enter 12/4, I get the 4th of December. I am in the USA, so we use dates of MM/DD/YY. The shortcut in your locale may be different.

[sajnoor]

Ok thanks for your reply would this work or is link below for database as well ?

viewtopic.php?f=20&t=89878

[sajnoor]

Update i installed calendar add on and now can access calendar by the following wat shift control and f2 or tools then add on then calendar is there a way can automate this such as selecting row b makes shift control and f2 therefore bringing up calendar ?

[sajnoor]

Hi all,

Thanks for all your help and support, i will try to help others on here if i am able to do so.

I have uploaded latest template i need someone to look at why the incomes_total is not adding up please, expenses side is all working fine apart from automation of calendar but i use control shft and f2 to bring up for now.

Thanks in advance

[RusselB]

Hi all,

Thanks for all your help and support, i will try to help others on here if i am able to do so.

I have uploaded latest template i need someone to look at why the incomes_total is not adding up please, expenses side is all working fine apart from automation of calendar but i use control shft and f2 to bring up for now.

Thanks in advance

The problem is the column letter you're using for the Income category actually matches the column for the Expenses category.

Changing

Code: Select all

=IF(B4<>"";SUMIF($Expenses_Incomes.$F$4:$F$8499;B4;$Expenses_Incomes.$H$4:$H$8499);"")

to

Code: Select all

=IF(B4<>"";SUMIF($Expenses_Incomes.$G$4:$G$8499;B4;$Expenses_Incomes.$H$4:$H$8499);"")

and make the other relevant changes in your spreadsheet, should resolve this problem.

[sajnoor]

Thanks for that have put in code although something is still wrong as not adding up correctly please see attached example

[FJCC]

On the Incomes_Total sheet, your formula in G3 is looking at B4 for the category to match. It should look at B3. Try this formula in G3

Code: Select all

=IF(B3<>"";SUMIF($Expenses_Incomes.$G$4:$G$8499;B3;$Expenses_Incomes.$H$4:$H$8499);"")

[sajnoor]

perfect you know your formulas any tutorials where i can learn the breakdown of formulas ?

thanks fjcc again any idea on calendar auto run ?

[robleyd]

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know
OpenOffice Spreadsheet Tutorial for Beginners with Examples
[Tutorial] Absolute, relative and mixed references
[Tutorial] VLOOKUP questions & answers

[sajnoor]

Thanks for all your help and advise could not have done without you guys.