Forum Discussion

Kaloyan Stoyanov's avatar
Kaloyan Stoyanov
Copper Contributor
Oct 17, 2017
Solved

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 column A (thus it stays empty/blank).

 

=IF(NOT(ISBLANK($A2)),IF(NOT(ISBLANK($M2)),YEAR($M2)),"")

 

I've applied it to several hundred rows after the actual data ends - so that I would make it easier in future. There is also a filter applied on the header row.

 

The issue is that if I decide to filter any of the other columns, it automatically hides the rows where the formula code is available, but there's no value (aka it's blank). Meaning that if I want to add a new entry on a new row, I have to remove filters or enter the formula code again - which is not optimal.

 

Is there a way to ensure that applying a filter on a column will not take into consideration the fact that there's a formula with no values in column N?

 

Thanks

Excel Desktop
Formulas & Functions
  • JKPieterse's avatar
    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.

3 Replies

Sort By
  • JKPieterse's avatar
    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.

Resources