Filtering a column hides rows with a formula, but no values

Hi, I'm working on optimizing a table with some information - Office 2010. I've applied on column N the following formula, which isn't supposed to be triggered if there aren't any values in colu...

Excel Desktop

Formulas & Functions

JKPieterse
Oct 17, 2017
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.

JKPieterse

Silver Contributor

Oct 17, 2017

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.

Kaloyan Stoyanov
Copper Contributor
Oct 17, 2017
Converting the range to a table solved it for me - thanks!
- JKPieterse
  Silver Contributor
  Oct 18, 2017
  You're welcome!

Forum Discussion

Filtering a column hides rows with a formula, but no values

Resources