Forum Discussion
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 manager...
Take this:
- Ensure Automatic Calculation Is Enabled
- Go to Formulas > Calculation Options and set it to Automatic instead of Manual.
- 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
Thank you for the assist!
- and 4. : Yep, got those settings : / :/
- 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 :/
- 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.