Filter function help

New Contributor

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?

 

MalcolmBrowning_0-1644147044399.png

 

4 Replies

@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.

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.

@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)

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