Forum Discussion
Filter overwhelmed? Not showing all data
This is interesting. The main thing I notice is that the columns causing an 'issue' are the only columns not included anywhere in the formula. My suggestions (possible from smallest bandaid to best practice):
a) Have you tried just adding a conditional for each of those columns?
b) Alternatively what if you assign a variable to that entire range and then select columns from that assigned variable (i.e. pull all the data at once)
c) add that data to the data model, use data import, use power query
other (minor) things to consider:
a) tweendate = fromdate * todate
BUT maybe not possible because it is technically not the same because from and to are set to BOTH dates must meet criteria but tweendate is currently set that if EITHER date is in between it is TRUE, was that intentional? If so consider breaking the 4 individual conditional up to a,b,c,d then fromdate=a*b, todate=c*d, tweendate=a*c + b*d
b) note the FILTER already has a if-no-results-found parameter. By using IFERROR you could be masking other errors or problems.
Thank you! I couldn't think of a conditional to apply to the columns, but i tried b and the solution worked! I set variable "backend" = [BACKEND.xlsm]RECORD!A:L, and then changed the filter function to say FILTER(backend, [BACKEND.xlsm]RECORD!B:B<>"", ...etc) and it all loaded! Thank you. I tried to use a variable to reference for all the filter criteria, but it didn't work as the columns were different each time. Oh well!
Working now, thanks !
