+ Reply to Thread
Results 1 to 5 of 5

Automated worksheet for accounting filter problem

  1. #1
    Registered User
    Join Date
    09-24-2010
    Location
    Victoria, BC
    MS-Off Ver
    Excel 2007
    Posts
    66

    Automated worksheet for accounting filter problem

    Hi all;
    I am automating a workbook for our night audit team to use for Accounts Payable. My problem is that at the site level we track AP by accounting code, whereas Head Office oonly wants the company & invoice number listed. Our AP is split between 2 categories; General (housing) & Food (Food Services)
    I have got the system mostly set but need a hand with the final step. For head office they only want to see 1 line per invooice with company, invoice #, date, & $ total. I have all the listings set but do not know how to make the information compile onto a single line on the page.
    I have somewhat denatured the attached workbook but you will see my problem in the "Header General" & "Header Foods" tabs. These are the ones going to H/O.
    The PAP tabs can be disregarded as there is never a split invoice on those tabs.

    Any ideas would be greatly appreciated by the few remaining strands of hair that I have not yet pulled out.
    Attached Files Attached Files
    Last edited by Unca Wook; 03-20-2011 at 09:51 AM.
    If I helped, say thanks by clicking on the star.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Automated worksheet for accounting filter problem

    Hi,

    I'm assuming that for instance on the Header General tab you only want each unique Supplier/Invoice number. e.g for Acme, just one row for invoice 12035.

    On the Track General tab, add a helper column F and in F9 enter

    Please Login or Register  to view this content.
    and copy this down.

    Now use standard data filter advanced to extract a unique list of the names in column F of the Track General tab to a helper column F on the Header General tab.

    Then use the formulae I've shown in B11:E11 on the Header General tab.

    Use this same approach for the Food tabs.

    HTH
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,300

    Re: Automated worksheet for accounting filter problem

    Try autofilter
    Select the Supplier Name(B10)
    Goto Data>filter then with the dropdown arrow in B10 select what to filter for.
    Check out the subtotal function to sum Filtered data

  4. #4
    Registered User
    Join Date
    09-24-2010
    Location
    Victoria, BC
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: Automated worksheet for accounting filter problem

    Richard thank you. It worked like a charm.

    EDIT: After testing it seems not to be quite right yet. There still seem to be a lot of steps needed after entering the data. Not all our staff are fully excel literate.
    I will continue to try automating the output.
    Last edited by Unca Wook; 03-20-2011 at 09:54 AM. Reason: Change from solved to no prefix.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Automated worksheet for accounting filter problem

    Hi,

    In which case you will need to develop a macro to automatically create the list of unique helper names at the click of a button, and then either keep the formulae I gave you down all the relevant rows or have the macro copy the formula down the rows.

    As a starter try recording the macro first, then examine it and change the code where necessary so that it will always run against the original data.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1