+ Reply to Thread
Results 1 to 7 of 7

Help with INDEX and IF to create a report

  1. #1
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    MS Excel for Office 365
    Posts
    24

    Help with INDEX and IF to create a report

    Hi,

    I need to do this one but I'm kind of at a loss. Not sure how should I go about doing this.

    So the general idea is I want to make some sort of a template for purchase orders(or invoices/or even quotes) where a list(report?) will be generated that will ONLY show the relevant products. What I want to work out is to have all my items listed in the first worksheet. I want the list/report in worksheet 2 to work so that I only need to write how much of an item I need in worksheet 1 and that will show on worksheet 2.

    I've attached an .xlsx. In that particular example, let's say I want 1 of AAA, 2 of CCC, and 1 of EEE, or any other combination, I want worksheet 2 to show those three lines.

    Is this possible at all?

    Thanks in advanced!
    Attached Files Attached Files
    Last edited by zed369; 01-20-2015 at 10:20 AM. Reason: Changing title

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2211, rarely 2007
    Posts
    12,830

    Re: Need help with this one

    You uploaded an XLSX file. Your profile says Excel 2003. Are there backwards compatibility considerations?

    If not copy and paste this array-entered formula in B2 of PO Invoice sheet. This formula is committed by simultaneously pressing

    Ctrl + Shift while hitting Enter. Fill down and across to C4.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Does this do what you want?

  3. #3
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    MS Excel for Office 365
    Posts
    24

    Re: Need help with this one

    Must've been a typo - I'm using 2013.

    As for the formula, it's not really working. It gives me an error #NUM!. I don't really understand it but I don't think it gives me what I want.

    Thanks though. Gave me an idea with INDEX and MATCH

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,636

    Re: Help with INDEX and IF to create a report

    =IFERROR(INDEX('Products List'!A:A,SMALL(INDEX(('Products List'!$A$2:$A$100="")*10^10+ROW('Products List'!$A$2:$A$100),0),ROW($A1))),"")
    Try this in A2 and copy across

    Change The $A$2:$A$100 Range according to your needs
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,636

    Re: Need help with this one

    or simply go with pivot table

  6. #6
    Forum Contributor
    Join Date
    10-04-2014
    Location
    Europe
    MS-Off Ver
    2007
    Posts
    107

    Re: Help with INDEX and IF to create a report

    And yet another solution to the problem (but like the one from FlameRetired, this is an array formula)

    =IFERROR(INDEX('Products List'!A$2:A$500,SMALL(IF('Products List'!$A$2:$A$500>0,ROW('Products List'!$B$2:$B$500)-ROW('Products List'!$O$2)+1),ROWS('Products List'!A$2:B2))),"")

  7. #7
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    MS Excel for Office 365
    Posts
    24

    Re: Help with INDEX and IF to create a report

    It worked!

    Thanks everyone!

+ 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