New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Journal to ledger entries by month, category

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Journal to ledger entries by month, category

Postby tinman44 » Sat Dec 10, 2011 11:21 am

Using Arrays, how can I post totals from a general journal to a category ledger by month and category. Have tried sumif, month, year,debit/credit, comes up 0. Am attaching a simple workbook. Thanks for your help.
  • 0

You do not have the required permissions to view the files attached to this post.
tinman44
Rookie
 
Posts: 4
Joined: Dec 8, 2011
Reputation: 0

Previous post-Journal to Ledger, solved

Postby tinman44 » Sat Dec 10, 2011 1:10 pm

Attached find my solution. Probably a better way but with a little tweaking this should work just fine. :D
  • 0

You do not have the required permissions to view the files attached to this post.
tinman44
Rookie
 
Posts: 4
Joined: Dec 8, 2011
Reputation: 0

Re: Journal to ledger entries by month, category

Postby Don » Sat Dec 10, 2011 1:19 pm

Hi!

There are many complex ways to do this and often times you may just think to use a formula to solve the problem. But, that is making this issue way too complex. There is actually a pretty simple way to do this using a PivotTable.

So, here are the steps to follow to get this information in the PivotTable:

  • Enter a column next to the Date column and Call it "Month"
  • In that column, enter this function in the first cell and copy it down =MONTH(B3) - make sure the cell is formatted as "General" and you will get a numerical representation of the month (1-12)
  • Select all of the data from the Bank header down and go to the Insert tab, click PivotTable, and hit OK to input it onto a new worksheet.
  • Go to the new tab and put a check mark next to Month, Category, and Debit.
  • At the bottom of the pane on the right side, put Month into the Row Labels box; put Category into the Column Labels box; and, put Debit into the Values box.

This should give you the information that you were looking for in an easily manipulative format. PivotTables cab be confusing at first, but once you play around with them a bit, you will see how much easier they can make your life in Excel.

Hope this helps :)
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: Previous post-Journal to Ledger, solved

Postby Don » Sat Dec 10, 2011 1:22 pm

lol I was just writing the solution when you posted this. Check back in your original thread and please post future updates there. I will take a look at this solution.
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: Journal to ledger entries by month, category

Postby Don » Sat Dec 10, 2011 1:26 pm

Yea, you can use arrays like in your solution, but those, as you've found out, can be very tricky and are hard to maintain. Try out the PivotTable solution that I gave you and see what you think about that. The best thing about PivotTables is that they are pretty easy to maintain and very easy to change/update since you aren't dealing with array formulas and such.
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: Journal to ledger entries by month, category

Postby tinman44 » Sat Dec 10, 2011 2:42 pm

Thanks Don. You're right pivot tables are a bit confusing. Will use your instructions.
  • 0

tinman44
Rookie
 
Posts: 4
Joined: Dec 8, 2011
Reputation: 0


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 81 guests