Forum Discussion
Filter function not detecting values
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.
https://docs.google.com/spreadsheets/d/1kgmqbz4tbGxtQsM5nF_alOyuTCSVL6Fx/edit?usp=sharing&ouid=104929125651435008341&rtpof=true&sd=true
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.