Changing Conditional IF Values

**BrownTeddyBear** · 05-29-2009, 10:30 AM

Hi there,

Sorry. I know I asked about this before but I thought my explanation wasn't very clear so I'll try again.

I have two worksheets. One is called "Orders" and the other is "Invoice2".

On my "Orders" sheet I have a listed of product codes and ordered quantity. One column being ordered quantity and the other being product codes.
For instance it is like this:

Product Codes Ordered Quantity

TH1 5
AB3 0
JC5 10
LFK 11

On my "Invoice2" sheet I have a column for product codes and ordered quantity. Basically, I want the "Invoice2" sheet to reference the "Orders" sheet in relation to these columns. However, on the invoice I only want to display the product codes ordered and ordered quantity. For instance TH1 would be the first line and JC5 the second line. This is because AB3 is less than 0. So ever product code on the "Invoice2" sheet must be greater than 0 in ordered quantity on the "Orders" Sheet. This is what the invoice2 sheet would look like:

Product Codes Ordered Quantity

TH1 5
JC5 10

Does anybody have a suggestion on how to achieve this?

Thank you.

Kind regards,

Brown Teddy Bear

**NBVC** · 05-29-2009, 10:43 AM

See attached.

Formula in E1 to count number of orders over 0...

Please Login or Register  to view this content.

Formula in A2 to extract product codes for orders above quantity 0:

Please Login or Register  to view this content.

This formula is confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied over 1 column and down 100 rows..

If you make formula adjustments for sheetnames, ranges, etc.. you need to reconfirm with cSE keys before copying over and down.

**BrownTeddyBear** · 05-29-2009, 10:22 PM

Hi NBVA,

Thank you for your response.

It is much appreciated.

The code you provided sounds like an excellent solution. I pasted the code in cell D19 for the product code (where it starts on the invoice) and the first product code came up fine. Here is my code:

=IF(ROWS($D$18:$D18)>$E$57,"",INDEX(Orders!B$17:B$21,SMALL(IF(Orders!B$17:$B$21>0,ROW(Orders!$D$17:$D21)-ROW(Orders!$D$17)+1),ROWS($D$18:$D18))))

I tried to copy the next line down using the code you suggested:

=IF(ROWS($D$18:$D19)>$E$57,"",INDEX(Orders!B$17:B$21,SMALL(IF(Orders!B$17:$B$21>0,ROW(Orders!$D$17:$D21)-ROW(Orders!$D$17)+1),ROWS($D$18:$D19))))

However, I kept getting a "NUM#" error in the cell.

To give you a clearer view my product codes start in the "Orders" sheet at cell B:17 and go to B21. The Quantity goes in the "Orders" sheet from D17:D21. I'm adding to "Invoice2" sheet in D19 for the product code and E19 for the quantity.

There's probably a simple solution to this but I can't find it.

Thank you for your help again.

Kind regards,

BTB

**BrownTeddyBear** · 05-30-2009, 02:25 AM

Hi NBVC,

Just letting you know the code worked a treat. I found out I hadn't confirmed the CSE Keys. Thank you for your help. It was much appreciated.

I have another problem where I have two additional columns which appear on the "invoice2" worksheet. These are "Description" and "Unit Price". I'm trying to do the same thing so the sheet would look like this for instance:

Product Code Ordered Quantity Description Unit Price

TH0 5 Handheld Calculator $56.00

Is there any way to reference these two additional fields as well and make them line up with the appropriate product codes and quantity if applicable?

Thank you again. I appreciate your time and efforts.

Kind regards,

BTB

**DonkeyOte** · 05-30-2009, 03:11 AM

BTB, you just need to ensure the INDEX range in each column is referencing the appropriate range on "Orders" worksheet, ie:

INDEX(Orders!B$17:B$21

the SMALL section should remain unchanged as that should always reference B given it is looking at Quantity column.

Attached is a slight variant (non array) - based on assumption that

a) Product Codes are unique (ie not repeated on multiple rows)
b) No Blanks interspersed amongst Product Code list on Invoice2 (ie contiguous range)

(note in attachment I think the invoice2 sheet is incorrectly named - ie should be other way around!)

**BrownTeddyBear** · 05-30-2009, 06:15 AM

Hi DonkeyOte and NBVC,

Thank you very much for all your help. Both your solutions seem to work fine and I really appreciate the effort you have gone to.

Donkey Ote, you're correct about renaming the index cell range. I never knew you could do this. All I did was change the ranges so they matched with the ones on my sheet and they worked very nicely. NBVC, I never also knew about CSE so thank you for teaching me about that too.

Kind regards,

BrownTeddyBear