Curious Filter-by-formula issue

[gordonpsmith]

I'm trying to work out a "map" of certain types of files that are on our legacy network drives. The information is pretty stable, so I wanted a better way than searching and re-searching the drives over and over. I have created three tabs (one for each drive - M, Q, and Y) that consist of a text output of a) the filename and b) the location.

I then have an additional tab where I enter my search term into cell B1. When I enter that value, several things happen:
1) Each drive tab does a quick comparison
F1=IF(ISERROR(SEARCH(Search!$B$1,B1)),"",A1) <- This checks to see it the filepath in column B contains my search term. If there's no error, display the filename (from column A)
G1=IF(F1="","",B1) <- for completeness, if there is a filename that made it out from above, display the corresponding filepath as well

2) The search tab pulls it all together with
Search!A5=SORT(VSTACK(SORT(FILTER(Y!F:G,(Y!F:F<>""))),SORT(FILTER(M!F:G,(M!F:F<>"")))))
When everything is working, I get what I want, a sorted list of all files from M and Y (Q is forthcoming once I figure this issue out) that excludes all empty cells from the pre-filtering done on the drive tabs.

However, in certain cases, I get an error at 2). On the off chance it's details, some additional info
M (97,592 entries) contains

.1._RBD_ADI-009001_Point-ReRig_Body_Linkage.SLDPRT

M:\02 Shop Documents and Photos\Engineering\R.Schimmel\FELD TREX\TREX Analysis\TREX Body Frame Analysis\RBD\_Archive\04.15.2019.NeckPlots\.1._RBD_ADI-009001_Point-ReRig_Body_Linkage.SLDPRT

Y (121,253 entries) contains

Feld TREX Eye Reference.SLDPRT

Y:\DBWorks_Physical_Files\000009\Feld TREX Eye Reference.SLDPRT

Q (86,625 entries) contains

190329_neck and body rings drawings.SLDDRW

Q:\01 Projects\Feld\Jurassic World\02_DesignAndEngineering\01_ArtisticAssets\2D\190329_neck and body rings drawings.SLDDRW

• "FELD" results in a name error (and simplifying things to =SORT(FILTER(Y!F:G,(Y!F:F<>""))) also gets me a NAME error)
• "Feld" results in a name error (and simplifying things to =SORT(FILTER(Y!F:G,(Y!F:F<>""))) also gets me a NAME error)
• "Feld " or "FELD " (with a space) gets a name error, but the simplified version(s) show
  • =ISERROR(SORT(FILTER(Y!F:G,(Y!F:F<>"")))) gets a Spill! Error
  • =SORT(FILTER(Y!F:G,(Y!F:F<>""))) returns expected results
  • =SORT(FILTER(M!F:G,(M!F:F<>""))) gets a #Name Error
  • =SORT(FILTER(Q!F:G,(Q!F:F<>""))) gets a #Calc Error

So, the combination appears to be failing because the individual sorts are failing, but a) I'm not sure why and b) I'm not sure why I get two different errors for the same search on a different tab?

 

[gordonpsmith]

After some more experimentation, the issue seems to be from the dynamic nature of the combination and the large numbers of rows. Instead of trying to compress everything into a single formula, I focused on better understanding the sizes involved.

On each tab (M,Q,Y) I created the filtered data for the list on that tab. I then used a cell to determine how many rows were involved in the filtered data
I then used that number to indirectly define the length of the data ("A1:B"&rows)
Once I did that, everything seemed to clear up.