# Help with INDEX and IF to create a report

1. ## 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?

2. ## 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:
`Please Login or Register  to view this content.`
Does this do what you want?

3. ## 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. ## 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

5. ## Re: Need help with this one

or simply go with pivot table

6. ## 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. ## Re: Help with INDEX and IF to create a report

It worked!

Thanks everyone!

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

#### 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