+ Reply to Thread
Results 1 to 6 of 6

Pulling information from sheet to help generate invoices

  1. #1
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Pulling information from sheet to help generate invoices

    So here is my dilemma,

    I have updated a sheet that has the order totals (it is set up this way to make it easier to get our totals to the supplier) and i need to pull item type, color and size to corresponding person so i can generate an invoice to be delivered with the items.

    I have attached a sample with the desired results for the first line.

    Any and all help would be greatly appreciated.

    Los
    Attached Files Attached Files
    Last edited by Losguapos1; 12-01-2015 at 01:31 PM.

  2. #2
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Re: Pulling information from sheet to help generate invoices

    i updated the original sample to be more specific


    i am really stuck any help would be greatly appreciated.

    Los

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: Pulling information from sheet to help generate invoices

    The layout of your Orders does not lend itself to easy "formula" solutions. You use merged cells (not advised) and the various items have a differing number of columns (sizes) associated with them.

    A more "standard" order form would follow the format used in item 1 of "Sheet2" (Qty/Item/Colour/Size) with a row per item rather going across columns.

  4. #4
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Re: Pulling information from sheet to help generate invoices

    i figured that, and hind sight is always 20/20. So without redoing my form I'm trying to work with what i have. I can undo the merged cells and just put the labels in each cell if that would help....

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Pulling information from sheet to help generate invoices

    Okay, removing your merged cells, I also combined your Item with color for consistency (some products had color choice, others didn't). In Row 1, I "Centered Across Selection" (different from merging)

    I then Cleared Row 2 and starting in D2 copied this formula across to CF2
    =IF(ISTEXT(D1),D1, C2)
    I then hid row 2 (un-hid on upload)

    In J4 and copied across to all other QTY columns, this ARRAYED formula
    =IFERROR(INDEX(Orders!$D4:$CF4, SMALL(IF(ISNUMBER(Orders!$D4:$CF4), COLUMN($D3:$CF3)-COLUMN($D3)+1), TRIM(RIGHT(J$1,2))+0)),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Similarly in K2
    =IFERROR(INDEX(Orders!$D$2:$CF$2, MATCH(SMALL(IF(ISNUMBER(Orders!$D4:$CF4), COLUMN($D3:$CF3)-COLUMN($D3)+1), TRIM(RIGHT(J$1,2))+0), COLUMN($D$1:$CF$1)-COLUMN($D$1)+1)),"")

    and in M2
    =IFERROR(INDEX(Orders!$D$3:$CF$3, MATCH(SMALL(IF(ISNUMBER(Orders!$D4:$CF4), COLUMN($D3:$CF3)-COLUMN($D3)+1), TRIM(RIGHT(J$1,2))+0), COLUMN($D$1:$CF$1)-COLUMN($D$1)+1)),"")

    And then copied down. Does this work for you?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Re: Pulling information from sheet to help generate invoices

    YOU DA MAN Chemist.....You saved my life!!! and hours of typing invoices!!!

    thanks so much

    Los

+ 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. [SOLVED] Automatically Generate Invoices from Time Sheet (somewhat complex circumstances)
    By wad06 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-13-2014, 08:04 PM
  2. [SOLVED] Pulling information from one sheet to another.
    By rwalker1052 in forum Excel General
    Replies: 3
    Last Post: 12-05-2013, 06:59 AM
  3. [SOLVED] Generate multiple invoices fetching data from rows of a sheet
    By devang.vakharia in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-12-2013, 08:14 AM
  4. Pulling Information to a new Sheet
    By halfelite in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-24-2013, 04:18 PM
  5. Vba code help pulling data from one sheet and generate results to another sheet
    By OATMAS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2013, 11:45 AM
  6. [SOLVED] Pulling information from one sheet to another
    By boll55 in forum Excel General
    Replies: 2
    Last Post: 11-27-2012, 12:21 PM
  7. Need help pulling information into new sheet!
    By RobertGriffith in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2011, 01:10 PM
  8. Pulling Information in seperate sheet based on data in current sheet
    By joekunin in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-03-2009, 05:06 AM

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