Filter function help

%3CLINGO-SUB%20id%3D%22lingo-sub-3122263%22%20slang%3D%22en-US%22%3EFilter%20function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122263%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20in%20the%20process%20of%20updating%20and%20managing%20our%20work%20projects%20log.%26nbsp%3B%20As%20you%20will%20see%20from%20attached%20document%20I%20have%20merged%20rows%20at%20the%20beginning%20of%20the%20table%20to%20allow%20each%20project%20to%20include%20a%20basic%20Gantt%20chart%20to%20detail%20timeline%20for%20different%20aspects%20of%20each%20project.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20filtering%20I%20cannot%20perform%20the%20function%20correctly%20to%20hide%20a%20complete%20project%20when%20I%20say%2C%20deselect%20any%20project%20which%20has%20reached%20100%25%20progress%2C%20as%20this%20only%20hides%20the%20top%20row%20(where%20the%20merged%20data%20is%20located)%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20have%20a%20function%20to%20allow%20me%20to%20hide%20each%20completed%20project%20when%20selecting%20either%20project%20phase%20(to-do%2C%20ongoing%2C%20complete)%20or%20percentage%20complete%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MalcolmBrowning_0-1644147044399.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345657iB6343E05631E68C3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MalcolmBrowning_0-1644147044399.png%22%20alt%3D%22MalcolmBrowning_0-1644147044399.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3122263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3122316%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298443%22%20target%3D%22_blank%22%3E%40MalcolmBrowning%3C%2FA%3E%26nbsp%3BOne%20good%20reason%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20to%20use%20merged%20cells.%20They%20often%20cause%20trouble.%20Just%20like%20you%20have%20discovered%20now.%20Consider%20to%20un-merge%20the%204%20cells%2C%20repeat%20the%20percentage%20in%20each%20of%20them%20and%20use%20conditional%20formatting%20to%20have%20the%20font%20colour%20blend%20in%20with%20the%20back-ground.%20Basically%20similar%20to%20how%20you%20coloured%20the%20first%20column%20now.%20Then%20you%20can%20filter%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3122320%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122320%22%20slang%3D%22en-US%22%3EHai%2C%3CBR%20%2F%3ETo%20solve%20the%20issue%20Insert%20a%20temporary%20column%20without%20merging%20the%20cells.%3CBR%20%2F%3ESuppose%20you%20inserted%20a%20column%20at%20%22I%22%3CBR%20%2F%3EPlace%20the%20formula%20IF(An%3D%22%22%2CI(n-1)%2CAn)%20and%20drag%20the%20formula%20till%20the%20end.%3CBR%20%2F%3En%20is%20the%20row%20number%2C%3CBR%20%2F%3ENow%20filter%20based%20on%20the%20Column%20I%20it%20will%20work.%3CBR%20%2F%3EThank%20You.%3C%2FLINGO-BODY%3E
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