Forum Discussion
Filter function not detecting values
Hi! Never posted here but hopefully I can get a quick response. I’m building a payroll calculator for a client and have hit a snag. My problem is as follows; I have an array that includes some co...
FILTER looks at the values of cells, not at their formulas, so it should work, in principle.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Absolutely, thanks so much. Can't figure out how to add an attachment so I uploaded it to google drive. Hopefully that works, it says unknown function when viewing it in a browser but seems to download normally.
So the tab "clean data" just references cells in the tab "raw data" with the addition of column L, among others.
If you see on the last tab the "master sheet", cell D11 contains a working formula. However cell E11, where I try to reference column L in the "clean data" tab, returns the #N/A error, which is incorrect. There should actually be three results. I just put the two formulas side by side for comparison purposes, and have not added the IFERROR and ROW portions yet.
So the problem is column L which contains (as an example) the formula =TEXTBEFORE(TEXTAFTER(K4,"Couples with ",,1,,),",",,,1) in the "clean data sheet". These values are not being detected.
Hopefully this makes sense. I'm sure there are easier ways of doing what I'm trying to do here, but I've only ever gotten through Excel projects by googling, so, forgive any redundancies.
Thanks. The problem appears to be that most of the formulas in column L of the Cleaned Data sheet return #N/A. You can suppress that by supplying the if_not_found argument of TEXTBEFORE and TEXTAFTER:
=TEXTBEFORE(TEXTAFTER(K2,"Couples with ",,1,,""),",",,,1,"")
The formula in E11 on the Master Pay Sheet then returns a spilled range:
See the attached version.
At first glance, there's some issues with the data. It's not entirely clean.
You're not getting FILTER results because the errors need to be checked in L in the Cleaned Data sheet.
Clear out the formulas in L and try this:
=LET(name, TEXTBEFORE(TEXTAFTER(K2:K200, "Couples with ", , 1, , ""), ",", , , 1, ""), TRIM(name))
