Forum Discussion
Filtering a column hides rows with a formula, but no values
First of all, your formula misses the False part of the second IF function:
=IF(NOT(ISBLANK($A2)),IF(NOT(ISBLANK($M2)),YEAR($M2),"FALSE PART"),"")
Secondly, convert your range to a table (Home tab, format as table dropdown), you can then leave the table just as large as you need, adding information below the table will cause Excel to automatically expand the table and fill in formulas too.
Lastly, I don't think I understand your problem with adding data and having to remove the filter.
First of all, your formula misses the False part of the second IF function:
=IF(NOT(ISBLANK($A2)),IF(NOT(ISBLANK($M2)),YEAR($M2),"FALSE PART"),"")
Secondly, convert your range to a table (Home tab, format as table dropdown), you can then leave the table just as large as you need, adding information below the table will cause Excel to automatically expand the table and fill in formulas too.
Lastly, I don't think I understand your problem with adding data and having to remove the filter.
- You're welcome!