Index/Match formula over 2 workbooks problem

[ripps]

Hi

I struggled with this all day yesterday and had it working when used in the same workbook but hit problems when I tryed to use it accross 2 workbooks as it will need to be used.

Hopefully someone can help.

I have a worksheet with a table of stock and quantity due dates as below
columnsA - I seperated by ,

Product Code, Description, Channel, Supplier, Date 1, Date 2, Date 3, Date 4, Comments

Row 1 is my column headers and then a list of items and the quantity that is due in under each date or a - or 0 if none are due in that week.

My formula needs to find a defined product code in column A and return the first date that item has stock coming in.

(hopefully your still following this)

The formula I got working in the same sheet is;

=INDEX(A1:I10,1,IF(INDIRECT(ADDRESS(MATCH(J5,A1:A10,0),5,3))>0,5,IF(INDIRECT(ADDRESS(MATCH(J5,A1:A10,0),6,3))>0,6,IF(INDIRECT(ADDRESS(MATCH(J5,A1:A10,0),7,3))>0,7,IF(INDIRECT(ADDRESS(MATCH(J5,A1:A10,0),8,3))>0,8,9)))))

A1:I10 being a small range I used to test code (actual range will be roughly 250 rows)
J5 is the product code I want to find

I tried to avoid Volitile functions but this code wouldnt work without the INDIRECT in there (the first IF statement was returning true even though it was false and just returned the first date)

This works fine but when I re-write it so the formula is in a different workbook and is querying the same table all the IF statements are coming back as false even if one if true so the dates are missed and it returns cell I9.

Sorry for the super essay but just making sure all the info is present.

Anybody have any ideas? I am quite aware that my formula may be sloppy so I wont be hurt

 

[GlennUK]

I can't see why you are using such a convoluted method to get values ... your use of INDIRECT us why it won't work on closed external workbooks. Use a formula of this type instead:

=INDEX(A1:I10,1,IF(INDEX(E1:E10,MATCH(J5,A1:A10,0))>0,5,IF(INDEX(F1:F10,MATCH(J5,A1:A10,0))>0,6,IF(INDEX(G1:G10,MATCH(J5,A1:A10,0))>0,7,IF(INDEX(H1:H10,MATCH(J5,A1:A10,0))>0,8,9)))))

 

[ripps]

Thanks Glenn I will try this.

My methods will probably be all over the place as I am self teaching as I go.

This got my head in a right state yesterday.

 

[ripps]

Glenn, you are a genious.

Thanks you so much.

+Rep

 

[GlennUK]

Am glad that I was able to help.