[Solved] Spreadsheet set up
[Solved] Spreadsheet set up
Hello, I am trying to set up a spread sheet to cover all 12 months for my rentals. Therefore, I need 12 columns with the 13th being the sum of income. I need to have one row to input income for each month. Then I will need 15 rows to input different expenses through the year. I would like to total each column of expenses only and then total the income row and all the expense rows. Each month would be totaled in columns, while income and expenses would be totaled in the rows. Row totals would be added while column totals would be added so as to fill in the lower right hand cell. M16 grand total. I have been driving myself nuts trying to understand the directions, but it hasn't turned out well. I hope my need is clear. If anyone can help me with this problem I would be soooo grateful.
Last edited by Huge1815 on Sun Mar 12, 2017 12:04 am, edited 1 time in total.
Open Office 4.1.2 Windows 10
-
MrProgrammer
- Moderator
- Posts: 5472
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Spreed sheet set up
Hi, and welcome to the forum.
The proposed layout will make a lot of work for you. Put all of your data on one sheet. Use three columns, Date, Category, and Amount. Use a Pivot Table (formerly called Data Pilot) to group and summarize the data with Date as a Column Field, Category as a Row Field, and Amount as a Data Field. It will take less than a minute to create the Pivot Table and you won't have to write any formulas. The Pivot Table will create your totals. Put the Pivot Table report on a separate sheet if you like. Then you can use Data → Group and Outline to group dates by month.
[Tutorial] Ten concepts that every Calc user should know
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
The proposed layout will make a lot of work for you. Put all of your data on one sheet. Use three columns, Date, Category, and Amount. Use a Pivot Table (formerly called Data Pilot) to group and summarize the data with Date as a Column Field, Category as a Row Field, and Amount as a Data Field. It will take less than a minute to create the Pivot Table and you won't have to write any formulas. The Pivot Table will create your totals. Put the Pivot Table report on a separate sheet if you like. Then you can use Data → Group and Outline to group dates by month.
[Tutorial] Ten concepts that every Calc user should know
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Last edited by MrProgrammer on Sun Mar 12, 2017 1:52 am, edited 1 time in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7.8, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.7.8, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Spreed sheet set up
Here's a spreadsheet I did up quickly, based on your descriptions.
This is just an example.
I see MrProgrammer has already responded to your question, and he does give good advice.
This is just an example.
I see MrProgrammer has already responded to your question, and he does give good advice.
- Attachments
-
- Rental Example.ods
- (11.44 KiB) Downloaded 100 times
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.