Forum Discussion

gruntledCyclops's avatar
gruntledCyclops
Copper Contributor
Jun 09, 2025

Filter overwhelmed? Not showing all data

Hello! I've developed an application for my workplace, logging absences through the year on a backend spreadsheet and presenting the data on a frontend spreadsheet, with a filter available as managers requested the ability to review data based on names, dates, shift times, etc. It is a lot of data, there are 1833 absences logged over the last 2 weeks. The frontend filter continues to work as expected, and there is practically no delay in presenting the data. However, I've noticed that some of the columns are presented as blank. If I open the backend spreadsheet, suddenly the frontend filter shows all columns as populated. I'm not sure how to lessen the load on my filter, as I am assuming there's excess processing afoot. Any ideas worth a try, thank you!

Filter code (crunchy, I know. Eek.) The date variables were my only idea to simplify the want to search from, to, and between dates depending on blank cells: "

=IFERROR(

LET(

fromdate,('BACKEND.xlsm]RECORD'!E:E>=G7)*('BACKEND.xlsm]RECORD'!F:F>=G7),

todate,('BACKEND.xlsm]RECORD'!E:E<=H7)*('BACKEND.xlsm]RECORD'!F:F<=H7),

tweendate,(('BACKEND.xlsm]RECORD'!E:E>=G7)*('BACKEND.xlsm]RECORD'!E:E<=H7))+(('BACKEND.xlsm]RECORD'!F:F>=G7)*('BACKEND.xlsm]RECORD'!F:F<=H7)),

blanks,FILTER('BACKEND.xlsm]RECORD'!A:L,('BACKEND.xlsm]RECORD'!B:B<>"")*('BACKEND.xlsm]RECORD'!B:B<>"Co-Ordinator Entering Leave:")*IF(ISBLANK(B3),1,'BACKEND.xlsm]RECORD'!B:B=B3)*IF(ISBLANK(C3),1,'BACKEND.xlsm]RECORD'!C:C=C3)*IF(LEN(E7)>0,'BACKEND.xlsm]RECORD'!D:D=E7,1)*IF(ISBLANK(I3),1,'BACKEND.xlsm]RECORD'!G:G=I3)*IF(ISBLANK(J3),1,'BACKEND.xlsm]RECORD'!J:J=J3)*IF(ISBLANK(K3),1,'BACKEND.xlsm]RECORD'!L:L=K3)*SWITCH(LEN(G7)+LEN(H7),0,1,10,tweendate,5,IF(LEN(G7)=0,todate,fromdate))),

IF(blanks=0,"",blanks)),"No Results")

"

The data / filter results are listed below, of course personal details removed. Columns A, H, I, and K are all presented as blank until the BACKEND spreadsheet is opened (which defeats the purpose of this filter).

BACKEND Data

RECORDEDCo-Ordinator Entering Leave:Employee Name:Date Called:Absence Start DateAbsence End DateShift Time AffectedActioned?Rostered?Reason for LeaveCommentsCovered by:
TRUEJohn SmithDon Douglas08/06/202509/06/202509/06/20250900-1730NO Personal Leave (PL)Sick, recuperating from injury. Note pending. 
TRUEJane SmithDeb Douglas09/06/202509/06/202509/06/20250600-1430  Sick / InjuredDid not feel well, Victoria moved to cover.Victoria Veck
 Jane SmithDoug Douglas09/06/202509/06/202509/06/20250600-1430  Sick / Injured Victor Veck

FILTER Result

ENTEREDCo-Ordinator Entering Leave:Employee Name:Date Called:Absence Start DateAbsence End DateShift Time AffectedActioned?Rostered?Reason for LeaveCommentsCovered by:
 John SmithDon Douglas08/06/202509/06/202509/06/20250900-1730  Personal Leave (PL)  
 Jane SmithDeb Douglas09/06/202509/06/202509/06/20250600-1430  Sick / Injured Victoria Veck
 Jane SmithDoug Douglas09/06/202509/06/202509/06/20250600-1430  Sick / Injured Victor Veck
admin
excel

5 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jun 10, 2025

    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.

     

     

    • gruntledCyclops's avatar
      gruntledCyclops
      Copper Contributor
      Jun 10, 2025

      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 !

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jun 09, 2025

    Take this:

     

    1. Ensure Automatic Calculation Is Enabled
      • Go to Formulas > Calculation Options and set it to Automatic instead of Manual.
    2. Use INDIRECT() to Force Updates
      • Instead of referencing BACKEND.xlsm directly, try using INDIRECT() to dynamically pull values:
    =IFERROR(LET(data, INDIRECT("'[BACKEND.xlsm]RECORD'!A:L"), FILTER(data, ...)), "No Results")

     

      • This forces Excel to actively look for the backend sheet data.

     

    3. Reduce the Data Load

      • With 1833 entries in two weeks, your formula may be struggling. Try reducing the range from A:L to only the essential columns.

     

    4. Check External Links Settings

      • Go to Data > Edit Links and ensure that automatic updates are enabled for external references.

     

    5. Using Power Query

    • gruntledCyclops's avatar
      gruntledCyclops
      Copper Contributor
      Jun 09, 2025

      Thank you for the assist!

      1. and 4. : Yep, got those settings : / :/
      2. I'm not sure if I copied it over incorrectly, but I think it's still too much :/ When I use INDIRECT within a FILTER function, I get "something is wrong with your formula" highlighted on the indirect references. I tried just having a cell use INDIRECT to reference 1 value from BACKEND.xlsm, and it came up with #REF until I had the backend open again :/
      3. Good idea! I divvied up the formula across the columns (A:G, H:L) and it came out looking the same, with the same exact columns left blank. I wish I knew what it was about these columns in particular!

      5. Using power query how? I'm not familiar.

    • gruntledCyclops's avatar
      gruntledCyclops
      Copper Contributor
      Jun 09, 2025

      Thank you for the assist!

      1. Yep, auto calc is definitely on.
      2. Not sure if I've copied it over badly, or if it's just panicking? Using INDIRECT in the FILTER function gets a "something wrong with your function" message, and I tried just using INDIRECT to reference a single cell, and it comes up with #REF unless i have the backend open, in which case it works fine again. -_-
      3. Frustrated because the A:L columns are all the information being requested. I tried dividing the same function into two groups (A:G, H:L) but still presenting the same, with the same columns blank. I can't figure out what it is about these specific columns!
      4. Yep, automatic updates are on :(
      5. Use Power Query how? I'm not familiar with it.

       

      Thank you!

Resources