Home

Looking for condition to ease at work

%3CLINGO-SUB%20id%3D%22lingo-sub-1013653%22%20slang%3D%22en-US%22%3ELooking%20for%20condition%20to%20ease%20at%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013653%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20looking%20for%20condition%2Fformula%20to%20lookup%20value%20while%20using%20filter%20on%20data.%20Right%20now%20what%20I%20was%20doing%20is%20coping%20a%20value%20from%20order%20column%20'L'%20and%20pasting%20it%20to%20the%20upper%20right%20corner%20order%20cell%20in%20column%20'AO'%20(green%20highlighted).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20730px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157565i8D00C45D2AD30F7D%2Fimage-dimensions%2F730x86%3Fv%3D1.0%22%20width%3D%22730%22%20height%3D%2286%22%20alt%3D%22clipboard_image_5.png%22%20title%3D%22clipboard_image_5.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20show%20in%20the%20above%20picture%20to%20escape%20C%26amp%3BP%2C%20I'm%20want%20to%20use%20Condition%2FFormula%20on%26nbsp%3B%3CU%3Eorder%20value%3C%2FU%3E%20(upper%20right%20corner)%20to%20find%20same%20%3CU%3Eorder%20value%3C%2FU%3E%20selected%20in%20selected%20from%20%3CU%3Efilter%3C%2FU%3E%20in%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%20me%2C%20which%20condition%2Fformula%20should%20i%20apply%20to%20ease%20that%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1013653%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013688%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20condition%20to%20ease%20at%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460054%22%20target%3D%22_blank%22%3E%40Shaheryar_Ali%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGoogled%20%22find%20value%20of%20filtered%20cell%20excel%22%20and%20found%20a%20formula%20that%20I%20got%20to%20work%20in%20a%20simple%20workbook%20of%20my%20own.%20Enter%20the%20following%20in%20%22AO1%22%20(assuming%20that%20the%20first%20row%20is%20indeed%20row%201%20and%20that%20your%20column%20headers%20are%20in%20row%205)%20and%20it%20should%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(E%3AE%2CMIN(IF(SUBTOTAL(5%2COFFSET(L5%2CROW(L%3AL)-ROW(L5)%2C0))%2CROW(L%3AL)-ROW(L5)%2B5)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013911%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20condition%20to%20ease%20at%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460054%22%20target%3D%22_blank%22%3E%40Shaheryar_Ali%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20if%20the%20filter%20returns%20only%20one%20row%2C%20in%20AO1%20it%20could%20be%20the%20formula%20like%20%3DL6%20or%20so.%20Or%20you'd%20like%20to%20keep%20the%20value%20in%20AO1%20after%20the%20filter%20is%20cleaned%2Fchanged%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Shaheryar_Ali
Occasional Visitor

I'm looking for condition/formula to lookup value while using filter on data. Right now what I was doing is coping a value from order column 'L' and pasting it to the upper right corner order cell in column 'AO' (green highlighted).

 

Example:

clipboard_image_5.png

 

As show in the above picture to escape C&P, I'm want to use Condition/Formula on order value (upper right corner) to find same order value selected in selected from filter in table.

 

Could you please help me, which condition/formula should i apply to ease that problem.

2 Replies

@Shaheryar_Ali 

 

Googled "find value of filtered cell excel" and found a formula that I got to work in a simple workbook of my own. Enter the following in "AO1" (assuming that the first row is indeed row 1 and that your column headers are in row 5) and it should work.

 

=INDEX(E:E,MIN(IF(SUBTOTAL(5,OFFSET(L5,ROW(L:L)-ROW(L5),0)),ROW(L:L)-ROW(L5)+5)))

@Shaheryar_Ali 

IMHO, if the filter returns only one row, in AO1 it could be the formula like =L6 or so. Or you'd like to keep the value in AO1 after the filter is cleaned/changed?

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