Home

reporting specific data to a separate tab

%3CLINGO-SUB%20id%3D%22lingo-sub-769099%22%20slang%3D%22en-US%22%3Ereporting%20specific%20data%20to%20a%20separate%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769099%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20Treasurer%20of%20a%20PTA%20and%20trying%20to%20automate%20the%20end%20of%20year%20accounting%20process%20a%20bit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20a%20cash%20book%20sheet%20where%20I%20look%20up%20the%20bank%20account%20entries.%20There%20are%20columns%20for%20each%20category%20of%20income%2Fexpense%20(such%20as%20different%20events%2C%20purchases%20for%20school%20etc).%20As%20a%20bank%20line%20may%20need%20assigning%20to%20multiple%20categories%20I%20then%20have%20to%20type%20the%20amounts%20in%20each%20coloumn%20to%20add%20up%20to%20the%20total%20for%20the%20line.%20What%20I%20then%20need%20to%20do%20is%20create%20summary%20sheets%20for%20certain%20categories.%20For%20example%2C%20I%20need%20a%20summary%20sheet%20with%20all%20the%20purchases%20for%20school.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20have%20a%20large%20table%20on%20one%20sheet%2C%20and%20I%20want%20to%20pick%20up%20only%20the%20entries%20that%20have%20a%20value%20in%20the%20%22purchases%20for%20school%22%20column%2C%20and%20then%20look%20up%20the%20description%20that%20has%20been%20entered.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20could%20do%20this%20at%20the%20end%20manually%2C%20but%20I'd%20love%20to%20be%20able%20to%20automate%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20spent%20ages%20on%20google%20but%20nothing%20has%20been%20quite%20what%20I%20need.%20I%20assume%20I'm%20not%20searching%20for%20the%20right%20thing!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-769099%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-769242%22%20slang%3D%22en-US%22%3ERe%3A%20reporting%20specific%20data%20to%20a%20separate%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769242%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380583%22%20target%3D%22_blank%22%3E%40moohead%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20help%20if%20you%20can%20upload%26nbsp%3B%20the%20structure%20of%20your%20worksheet%20with%20some%20sample%20data%20in%20it.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-769245%22%20slang%3D%22en-US%22%3ERe%3A%20reporting%20specific%20data%20to%20a%20separate%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769245%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3BAttached!%20I've%20deleted%20a%20lot%20of%20the%20info%20and%20had%20to%20remove%20names%20etc.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20(for%20example)%20on%20the%20%22school%20purchases%22%20tab%20I%20want%20a%20list%20of%20all%20the%20school%20purchases%20-%20the%20amount%20would%20be%20in%20the%20far%20right%20column%20of%20data%20(although%20in%20this%20case%20because%20I've%20deleted%20data%20there%20are%20no%20school%20purchases%2C%20but%20any%20column%20will%20do)%2C%20and%20then%20the%20description%20needed%20would%20come%20from%20the%20%22comment%22%20column.%20Does%20that%20make%20sense%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-770902%22%20slang%3D%22en-US%22%3ERe%3A%20reporting%20specific%20data%20to%20a%20separate%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770902%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380583%22%20target%3D%22_blank%22%3E%40moohead%3C%2FA%3E%26nbsp%3B%2C%20thanks%20for%20the%20sample%20sheet.%26nbsp%3B%20It%20helps!!%3C%2FP%3E%3CP%3EYour%20requirement%20to%20update%20School%20Purchase%20tab%20is%20feasible%2C%20but%20i%20am%20afraid%20it%20will%20be%20complex%20and%20difficult%20to%20maintain.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20alternative%20option%2C%20do%20you%20have%20flexibility%20of%20changing%20the%20structure%20of%20Cash%20Book%20tab%20%3F%20if%20yes%2C%20I%20recommend%20a%20simpler%20structure%20with%20following%20columns%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3ERef%26nbsp%3B%3C%2FLI%3E%3CLI%3EDate%26nbsp%3B%3C%2FLI%3E%3CLI%3EDescription%26nbsp%3B%3C%2FLI%3E%3CLI%3ETransaction%20type%20(income%2Fexpense)%26nbsp%3B%3C%2FLI%3E%3CLI%3EHeading%20(%20this%20might%20have%20event%20name%26nbsp%3B%20etc)%26nbsp%3B%3C%2FLI%3E%3CLI%3ECategory%20(%20all%20of%20the%20categories%20you%20have%20in%20columns%20)%26nbsp%3B%3C%2FLI%3E%3CLI%3EIncome%20Amount%26nbsp%3B%3C%2FLI%3E%3CLI%3EExpense%20Amount%26nbsp%3B%3C%2FLI%3E%3CLI%3Ecomments%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3CP%3EOnce%20you%20have%20this%2C%20you%20can%20create%20summaries%20using%20Pivot%20tables.%20this%20is%20very%20flexible%20reporting%20tool%20and%20easy%20to%20use.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
moohead
New Contributor

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.

3 Replies

@moohead 

It would help if you can upload  the structure of your worksheet with some sample data in it.  

@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?

@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 

  1. Ref 
  2. Date 
  3. Description 
  4. Transaction type (income/expense) 
  5. Heading ( this might have event name  etc) 
  6. Category ( all of the categories you have in columns ) 
  7. Income Amount 
  8. Expense Amount 
  9. comments 

Once you have this, you can create summaries using Pivot tables. this is very flexible reporting tool and easy to use. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies