+ Reply to Thread
Results 1 to 6 of 6

Pulling multiple items from a list of invoices to a separate sheet

  1. #1
    Registered User
    Join Date
    09-05-2017
    Location
    Bridlington
    MS-Off Ver
    2016
    Posts
    3

    Pulling multiple items from a list of invoices to a separate sheet

    Hi all
    I have a list of invoices on a sheet, each with a unique number and associated to an account number.
    Is there a way to pull all invoice data for one account number onto a separate sheet?
    Initially thought of vlookup, but this would only pull the first one and I have no clue (yet!) how to use VBA.
    Any help would be appreciated.
    Kind regards
    Chris

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: Pulling multiple items from a list of invoices to a separate sheet

    Hi,

    Welcome to the forum.

    This can be done with the help of a Pivot Table or some array formula.

    Can you post a sample file with enough data in excel format without any confidential info please? Click on "Go Advanced" and "Manage Attachments" to upload a file.

  3. #3
    Registered User
    Join Date
    09-05-2017
    Location
    Bridlington
    MS-Off Ver
    2016
    Posts
    3

    Re: Pulling multiple items from a list of invoices to a separate sheet

    thanks for your reply
    i have attached (i hope!) a sheet to this post
    the data on the left is test data from the invoice list and the bit on the right is my statement template.
    hope this helps
    chris
    Attached Files Attached Files

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: Pulling multiple items from a list of invoices to a separate sheet

    Hi,

    Please try the following array formula (need to be confirmed by pressing CTRL+SHIFT+ENTER)

    In H17:
    =IFERROR(INDEX(A$1:A$14,SMALL(IF($C$1:$C$14=$P$6,ROW(A$1:A$14)-MIN(ROW(A$1:A$14))+1),ROWS($H$1:$H1))),"")

    In J17:
    =IFERROR(INDEX(B$1:B$14,SMALL(IF($C$1:$C$14=$P$6,ROW(B$1:B$14)-MIN(ROW(B$1:B$14))+1),ROWS($H$1:H1))),"")

    In N17:
    =IFERROR(INDEX(D$1:D$14,SMALL(IF($C$1:$C$14=$P$6,ROW(D$1:D$14)-MIN(ROW(D$1:D$14))+1),ROWS($H$1:J1))),"")

    See the attached file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-05-2017
    Location
    Bridlington
    MS-Off Ver
    2016
    Posts
    3

    Re: Pulling multiple items from a list of invoices to a separate sheet

    That's brilliant!
    Thankyou so much!
    Now for the tricky question, what makes it work?
    pointless having it working if you don't know how, plus i'm keen to learn

  6. #6
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: Pulling multiple items from a list of invoices to a separate sheet

    You are welcome

    If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

    For help on array formula, please go through the following URLs:

    https://support.office.com/en-us/art...2-ecfd5caa57c7

    https://exceljet.net/excel-functions...small-function

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Multiple items per cell broken out in to indexed list on separate sheet
    By anakaine in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-29-2016, 05:20 AM
  2. [SOLVED] Pulling information from sheet to help generate invoices
    By Losguapos1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-01-2015, 08:12 PM
  3. How to keep record of invoices printed in separate database sheet (Please Help)
    By zaidbinsaleem in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2014, 02:51 AM
  4. Can taxes be totalled on separate sheet when using Excel invoices
    By badboyzmom in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  5. Can taxes be totalled on separate sheet when using Excel invoices
    By badboyzmom in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. Replies: 0
    Last Post: 07-21-2005, 02:05 PM
  7. Can taxes be totalled on separate sheet when using Excel invoices
    By badboyzmom in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-21-2005, 02:05 PM

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