Home

Pull Data from a column in Excel for a Summery Sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-805525%22%20slang%3D%22en-US%22%3EPull%20Data%20from%20a%20column%20in%20Excel%20for%20a%20Summery%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805525%22%20slang%3D%22en-US%22%3E%3CP%3EHey%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20very%20basic%20excel%20spreadsheet%20which%20we%20use%20to%20check%20stock%20level.%20In%20column%20H%20a%20formula%20calculates%20whether%20any%20product%20should%20be%20ordered.%20I%20would%20like%20to%20create%20a%20summery%20sheet%20pulling%20only%20the%20products%20that%20require%20ordering%20(the%20products%20in%20green)%2C%20is%20there%20a%20way%20to%20do%20this%20in%20Excel%3F%20I%20fairly%20new%20to%20Excel%20and%20so%20not%20familiar%20with%20all%20of%20its%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20will%20be%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Nate%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-805525%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-805576%22%20slang%3D%22en-US%22%3ERe%3A%20Pull%20Data%20from%20a%20column%20in%20Excel%20for%20a%20Summery%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392498%22%20target%3D%22_blank%22%3E%40NatePopo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Nate%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(Sheet1!B%249%3AB%242000%2CAGGREGATE(15%2C6%2C1%2F(Sheet1!%24G%249%3A%24G%242000%26gt%3B0)*(ROW(Sheet1!%24G%249%3A%24G%242000)-ROW(Sheet1!%24G%248))%2CROW()-ROW(%24B%244)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Edrag%20it%20down%20and%20to%20the%20right%2C%20please%20see%20Sheet2%20in%20attached.%20I%20only%20changed%20in%20your%20IFS%20formula%20%220%22%20on%200%20to%20have%20numbers%20in%20entire%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
NatePopo
Occasional Visitor

Hey,

 

I have attached a very basic excel spreadsheet which we use to check stock level. In column H a formula calculates whether any product should be ordered. I would like to create a summery sheet pulling only the products that require ordering (the products in green), is there a way to do this in Excel? I fairly new to Excel and so not familiar with all of its functions.

 

Any help will be much appreciated!

 

Thanks, Nate

1 Reply

@NatePopo 

Hi Nate,

 

The formula could be like

=IFERROR(INDEX(Sheet1!B$9:B$2000,AGGREGATE(15,6,1/(Sheet1!$G$9:$G$2000>0)*(ROW(Sheet1!$G$9:$G$2000)-ROW(Sheet1!$G$8)),ROW()-ROW($B$4))),"")

drag it down and to the right, please see Sheet2 in attached. I only changed in your IFS formula "0" on 0 to have numbers in entire column.

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