Jul 23 2019 01:23 AM
Hello!
I am Treasurer of a PTA and trying to automate the end of year accounting process a bit.
We have a cash book sheet where I look up the bank account entries. There are columns for each category of income/expense (such as different events, purchases for school etc). As a bank line may need assigning to multiple categories I then have to type the amounts in each coloumn to add up to the total for the line. What I then need to do is create summary sheets for certain categories. For example, I need a summary sheet with all the purchases for school.
So I have a large table on one sheet, and I want to pick up only the entries that have a value in the "purchases for school" column, and then look up the description that has been entered.
I know I could do this at the end manually, but I'd love to be able to automate it.
I've spent ages on google but nothing has been quite what I need. I assume I'm not searching for the right thing!
Many thanks.
Jul 23 2019 02:56 AM
It would help if you can upload the structure of your worksheet with some sample data in it.
Jul 23 2019 03:04 AM
@Kodipady Attached! I've deleted a lot of the info and had to remove names etc.
So (for example) on the "school purchases" tab I want a list of all the school purchases - the amount would be in the far right column of data (although in this case because I've deleted data there are no school purchases, but any column will do), and then the description needed would come from the "comment" column. Does that make sense?
Jul 23 2019 10:49 PM
@moohead , thanks for the sample sheet. It helps!!
Your requirement to update School Purchase tab is feasible, but i am afraid it will be complex and difficult to maintain.
As an alternative option, do you have flexibility of changing the structure of Cash Book tab ? if yes, I recommend a simpler structure with following columns
Once you have this, you can create summaries using Pivot tables. this is very flexible reporting tool and easy to use.