SOLVED
Home

How to hide rows if a cell value is above a specified value from a different cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-289769%22%20slang%3D%22en-US%22%3EHow%20to%20hide%20rows%20if%20a%20cell%20value%20is%20above%20a%20specified%20value%20from%20a%20different%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289769%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20spreadsheet%20and%20part%20of%20it%20looks%20like%20this%20(you%20can%20see%20that%20cell%20C4%20has%2060)%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20504px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60432i744AC665CC600C0D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBased%20on%20the%20above%20cell%2C%26nbsp%3B%3CSTRONG%3EC4%2C%20%3C%2FSTRONG%3Ewhich%20is%2060%2C%20it%20will%20count%20from%200%20up%20tp%20what%20is%20in%20C4%2C%20like%20this%2C%20in%20the%20A%20column%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20799px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60433i4539B58381B0A493%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture2.PNG%22%20title%3D%22Capture2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20make%20it%20so%20that%20all%20rows%20from%2061%20(%3CSTRONG%3EA74%3C%2FSTRONG%3E)%20are%20completely%20hidden%2C%20%3CU%3E%3CSTRONG%3Ebecause%20the%20number%20is%20above%20what%20has%20been%20entered%20in%20cell%20C4%3F%3C%2FSTRONG%3E%3C%2FU%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESo%2C%20because%20in%20the%20example%20here%2C%20C4%20is%2060%2C%20I%20want%20it%20to%20then%20look%20like%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20845px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60434iC45EA802D6AE5648%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture3.PNG%22%20title%3D%22Capture3.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20the%20data%20stops%20at%2060%2C%20because%20cell%20A74%20is%20higher%20than%20the%20value%20in%20C4%20(I%20just%20deleted%20it%20all%20to%20make%20this%20screenshot%2C%20I%20want%20to%20do%20it%20either%20through%20a%20formula%20or%20conditional%20formatting%20so%20it%20is%20done%20automatically).%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20achieve%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%2C%20and%20I%20hope%20this%20makes%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-289769%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-290317%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20hide%20rows%20if%20a%20cell%20value%20is%20above%20a%20specified%20value%20from%20a%20different%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290317%22%20slang%3D%22en-US%22%3E%3CP%3Eyou%20can%20not%20use%20if%20to%20control%20value%20of%20multiple%20cells.%20You%20need%20to%20create%20an%20array%20formula.%20How%20ever%20if%20you%20want%20to%20sum%20the%20value%20of%20cells%20depending%20on%20a%20value%20on%20another%20range%20than%20you%20can%20use%20SUMIF%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIF(%24A%2414%3A%24A%2497%2C%22%26lt%3B%22%26amp%3B%24C%244%2C%20%24E%2414%3A%24E%2497)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebasically%20this%20formula%20will%20check%20all%20the%20cells%20in%20A14-A97%26nbsp%3Brange%20with%20the%20value%20of%20C4%20cell%20and%20if%20the%20value%20of%20the%20cell%20is%20smaller%20than%20the%20value%20in%20C4%20the%20value%20in%20the%20correspnding%20cell%20in%20E%26nbsp%3Bcolumn%26nbsp%3Bwill%20be%20added%20to%20the%20sum.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290162%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20hide%20rows%20if%20a%20cell%20value%20is%20above%20a%20specified%20value%20from%20a%20different%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290162%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20again%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20make%20it%20so%20that%20the%20formulas%20in%20my%20sheet%20only%20apply%20to%20the%20visible%20data%20and%20ignores%20what%20is%20now%20hidden%3F%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20that%20possible%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20want%20to%20calculate%20the%20total%20amount%20payable%20based%20on%20the%20following%3A%3CBR%20%2F%3E%3CBR%20%2F%3EIF%20repayment%20number%20(between%201%20and%2084)%20(cells%20A14%20to%20A97)%20is%20less%20than%20number%20of%20months%20(cell%20C4)%20THEN%20sum%20column%20E%20(cells%20E14%20to%20E97)%2C%20otherwise%20blank.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20formula%20looks%20like%20this%2C%20but%20it%20results%20in%20an%20error%20%3CSTRONG%3E(%23VALUE!)%3C%2FSTRONG%3E%2C%20can%20you%20help%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(A14%3AA97%26lt%3B%24C%244%2CSUM(E14%3AE97)%2C%22%22)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289878%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20hide%20rows%20if%20a%20cell%20value%20is%20above%20a%20specified%20value%20from%20a%20different%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289878%22%20slang%3D%22en-US%22%3E%3CP%3EGood%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289877%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20hide%20rows%20if%20a%20cell%20value%20is%20above%20a%20specified%20value%20from%20a%20different%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289877%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20so%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%26nbsp%3B%20I%20managed%20to%20get%20it%20working.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289872%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20hide%20rows%20if%20a%20cell%20value%20is%20above%20a%20specified%20value%20from%20a%20different%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289872%22%20slang%3D%22en-US%22%3E%3CP%3ELike%20this%2C%20but%20in%20formula%20it%20shall%20be%20relative%20reference%20for%20the%20row%2C%20not%20absolute.%20Other%20words%20%24A14%2C%20not%20%24A%2414.%20And%20it%20shall%20be%20first%20(most%20top%20left)%20cell%20of%20your%20range%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20633px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60460iCE43C05774C5E8D9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20see%20attached%2C%20%22not%20visible%22%20region%20is%20in%20grey.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289856%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20hide%20rows%20if%20a%20cell%20value%20is%20above%20a%20specified%20value%20from%20a%20different%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289856%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%26nbsp%3B%20Thanks%20for%20replying.%3CBR%20%2F%3E%3CBR%20%2F%3ESilly%20question%2C%20but%20what%20cell%20would%20I%20put%20it%20in%3F%26nbsp%3B%26nbsp%3BCan%20it%20not%20be%20done%20as%20a%20formula%20with%20an%20IF%2C%20such%20as%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3EIF%20cell%20value%20%26gt%3B%20C4%2C%20hide%20all%20rows%20that%20occur%20afterwards%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20tried%20this%2C%20but%20nothing%20changes.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60457i38E5A6CFA45DA777%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20see%20that%20I%20have%20put%20your%20formula%2C%20with%20white%20formatting%2C%20to%20be%20applied%20to%20my%20entire%20dataset.%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20am%20I%20doing%20wrong%3F%26nbsp%3B%20Many%20thanks.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289776%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20hide%20rows%20if%20a%20cell%20value%20is%20above%20a%20specified%20value%20from%20a%20different%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289776%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20by%20conditional%20formatting%20you%20may%20use%20the%20rule%20with%20formula%3C%2FP%3E%0A%3CPRE%3E%3D%24A1%26gt%3B%3D%24C%244%3C%2FPRE%3E%0A%3CP%3Eapplied%20to%20your%20entire%20range%20and%20using%20Fill%3DWhite%2FFont%20Color%3DWhite%20format%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
ianwuk
Contributor

Hello.

I have a spreadsheet and part of it looks like this (you can see that cell C4 has 60):
Capture.PNG

Based on the above cell, C4, which is 60, it will count from 0 up tp what is in C4, like this, in the A column:
Capture2.PNG

How can I make it so that all rows from 61 (A74) are completely hidden, because the number is above what has been entered in cell C4?

So, because in the example here, C4 is 60, I want it to then look like this:

Capture3.PNG

As you can see, the data stops at 60, because cell A74 is higher than the value in C4 (I just deleted it all to make this screenshot, I want to do it either through a formula or conditional formatting so it is done automatically).

How can I achieve this?

Many thanks, and I hope this makes sense.

7 Replies

If by conditional formatting you may use the rule with formula

=$A1>=$C$4

applied to your entire range and using Fill=White/Font Color=White format

 

Hi @Sergei Baklan.  Thanks for replying.

Silly question, but what cell would I put it in?  Can it not be done as a formula with an IF, such as:

IF cell value > C4, hide all rows that occur afterwards

I tried this, but nothing changes.

Capture.PNG

 

You can see that I have put your formula, with white formatting, to be applied to my entire dataset.

What am I doing wrong?  Many thanks.


Solution

Like this, but in formula it shall be relative reference for the row, not absolute. Other words $A14, not $A$14. And it shall be first (most top left) cell of your range

image.png

Please see attached, "not visible" region is in grey.

 

 

Highlighted

Thanks so much @Sergei Baklan.  I managed to get it working.

Hi again @Sergei Baklan.

How can I make it so that the formulas in my sheet only apply to the visible data and ignores what is now hidden? 

Is that possible? 

 

Also, I want to calculate the total amount payable based on the following:

IF repayment number (between 1 and 84) (cells A14 to A97) is less than number of months (cell C4) THEN sum column E (cells E14 to E97), otherwise blank.

My formula looks like this, but it results in an error (#VALUE!), can you help please?

 

=IF(A14:A97<$C$4,SUM(E14:E97),"")

Thanks!

you can not use if to control value of multiple cells. You need to create an array formula. How ever if you want to sum the value of cells depending on a value on another range than you can use SUMIF

 

=SUMIF($A$14:$A$97,"<"&$C$4, $E$14:$E$97)

 

basically this formula will check all the cells in A14-A97 range with the value of C4 cell and if the value of the cell is smaller than the value in C4 the value in the correspnding cell in E column will be added to the sum.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies