Forum Discussion

MalcolmBrowning's avatar
MalcolmBrowning
Copper Contributor
Feb 06, 2022

Filter function help

Hi,

 

I am in the process of updating and managing our work projects log.  As you will see from attached document I have merged rows at the beginning of the table to allow each project to include a basic Gantt chart to detail timeline for different aspects of each project.  

 

When filtering I cannot perform the function correctly to hide a complete project when I say, deselect any project which has reached 100% progress, as this only hides the top row (where the merged data is located)  

 

Is it possible to have a function to allow me to hide each completed project when selecting either project phase (to-do, ongoing, complete) or percentage complete?

 

 

excel
Formulas and Functions

4 Replies

  • JU51M3's avatar
    JU51M3
    Copper Contributor
    Feb 06, 2022
    Hai,
    To solve the issue Insert a temporary column without merging the cells.
    Suppose you inserted a column at "I"
    Place the formula IF(An="",I(n-1),An) and drag the formula till the end.
    n is the row number,
    Now filter based on the Column I it will work.
    Thank You.
    • MalcolmBrowning's avatar
      MalcolmBrowning
      Copper Contributor
      Feb 06, 2022

      JU51M3 

       

      I am trying to insert this formula into the spreadsheet as you have mendtioned.  Could you give me a written out complete formula for the first group of boxes please as i cant seem to get it to work.  Do i change every n for one row number or for each consecutive row number?

       

      so In cell I4 the formula would be IF(An="",I(n-1),An)

           In cell I5 the formula would be IF(An="",I(n-1),An)

           In cell I6 the formula would be IF(An="",I(n-1),An)

           In cell I7 the formula would be IF(An="",I(n-1),An)

      • JU51M3's avatar
        JU51M3
        Copper Contributor
        Feb 06, 2022
        for the cell i4 the formula is =if(a4="",i3,a4)
        copy the formula and paste it in the i4 cell
        then simply drag the formula down across all the rows
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 06, 2022

    MalcolmBrowning One good reason not to use merged cells. They often cause trouble. Just like you have discovered now. Consider to un-merge the 4 cells, repeat the percentage in each of them and use conditional formatting to have the font colour blend in with the back-ground. Basically similar to how you coloured the first column now. Then you can filter as desired.