Help with INDEX and IF to create a report

**zed369** · 01-19-2015, 10:56 PM

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!

**FlameRetired** · 01-20-2015, 12:19 AM

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:

Please Login or Register  to view this content.

Does this do what you want?

**zed369** · 01-20-2015, 01:19 AM

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

**samba_ravi** · 01-20-2015, 01:27 AM

=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_ravi** · 01-20-2015, 01:29 AM

or simply go with pivot table

**Bobsone** · 01-20-2015, 01:50 AM

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))),"")