• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sequence, the filter, and the Index

josvill2010

New Member
Hi, it is my first attempt using a dynamic array formula.
I would to bring purchase orders from another table by using the filter option
The PO is linked to a specific GL code. I am trying to come up with a way that the formula brings all the PO's that are related to that GL code.
My problem is that I am getting #n/a instead of the information from the PO.
 

Attachments

  • Chandoo query.xlsx
    33.6 KB · Views: 5
You have some #N/A values in column C of the DL sheet. This is upsetting the results.
In cell A4 of test1 try:
Code:
=INDEX(FILTER(DL!$C:$L,IFERROR((DL!C:C=V9)*(DL!D:D<>""),0),""),SEQUENCE(COUNTIFS(DL!C:C,V9,DL!D:D,"<>")),{3,4,2,9,10})
 
Hi P45cal.
It is great. It has fixed the problem.

Sometimes the file shows a #Calc! error. Is there a magical formula to fix this?

For
i think the value that the formula brings for the sequence 9 and 10 is a text, instead of a value number.
Is there a way to enter a formula that transforms them into number and then to substract one from the other?
Many thanks,
j
 
Back
Top