filter formula frustration

Copper Contributor

Hi, I'm wondering if someone can help me. 

I want data from spreadsheet B (invoice tracker) to auto populate lines in spreadsheet A (invoice request). Sounds simple and the FILTER formula I've applied =FILTER('Invoice Tracker'!N3:N58,'Invoice Tracker'!O3:O58>0) works to a degree.  

My invoice tracker records multiple items designated by a code e.g.

code  V1 is Visit 1 and there may be 6 of this item - the invoice tracker identifies 6 of that code and provides a total $ value.

I want to auto populate my invoice request (spreadsheet A) with each code that returns a value in my invoice tracker (spreadsheet B)

When I apply the FILTER formula above it will work when 1 code has a value but not when multiple codes do.

How do I correct this?


Rebecca :)

4 Replies
I'm really not following what the issue is here. You example formula looks fine to return the values from column N when values in column O are >0. IF you want to return column N when values in column O or P or Q are >0 then you can use
=FILTER('Invoice Tracker'!N3:N58,('Invoice Tracker'!O3:O58+'Invoice Tracker'!P3:P58 + 'Invoice Tracker'!Q3:Q58)>0)
but that assumes all those values are 0 or positive number or at least their isn't a chance that the sum isn't <=0 but if that is possible then you could "or" the individual comparisons:
=FILTER('Invoice Tracker'!N3:N58,('Invoice Tracker'!O3:O58>0+('Invoice Tracker'!P3:P58>0) + ('Invoice Tracker'!Q3:Q58>0))
but then again I may be way off on what your issue is.
Could you please attach or give a link to sample example sheet?
You may need BYCOL() or MMULT() function with FILTER(). Please attach a sample file and show use your desired output then we could suggest you better solution.

@mtarler thanks for your reply.  I seem to have solved the problem :)

@Harun24HR Thank you, problem solved!