SOLVED
Home

Return single value from array of data from different worksheet using IF

%3CLINGO-SUB%20id%3D%22lingo-sub-524346%22%20slang%3D%22en-US%22%3EReturn%20single%20value%20from%20array%20of%20data%20from%20different%20worksheet%20using%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-524346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20All%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EHope%20everyone%20is%20fine%20and%20fruity.%20This%20is%20my%20first%20post%20here%2C%20I've%20tried%20to%20find%20an%20answer%20in%20the%20forum%20posts%20but%20to%20no%20success.%20Hopefully%20someone%20can%20give%20me%20some%20advice%20or%20point%20me%20in%20the%20right%20direction%20for%20my%20problem.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20a%20multi%20sheet%20workbook%20for%20stock%20control%2C%20sheet%201%20is%20a%20dashboard-type%20page%20with%20links%20to%20the%20other%20sheets.%20I'm%20trying%20to%20use%20the%20dashboard%20page%20as%20a%20quick%20overview%20to%20indicate%20if%20any%20low%20stock%20levels%20exist%20in%20the%20product%20ranges%20in%20the%20other%20sheets.%20So%20the%20page%20is%20something%20like%20this%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20A%20-%20Cells%20A1%20to%20A5%20contain%20product%20line%20description%20and%20hyperlink%20to%20corresponding%20sheet.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20B%20-%20Cells%20B1%20to%20B5%20contain%20number%20of%20products%20for%20that%20product%20line%20in%20the%20corresponding%20sheet.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20C%20-%20Cells%20C1%20to%20C5%20will%20indicate%20if%20any%20products%20in%20that%20line%2C%20on%20the%20corresponding%20sheet%2C%20are%20below%20a%20minimum%20stock%20level.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Ee.g%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESheet%201%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ECell%20A1%20-%2022%20inch%20rim%20wheels%20product%20line%20(hyper%20link%20to%20sheet2)%2C%20Cell%20B1-%2020%20total%2022%20inch%20rims%2C%20Cell%20C1%20-%20(true%2Cfalse%20condition)%20are%20any%20of%20the%20stock%20lines%20low%20for%2022%20inch%20rims%20in%20Sheet%202%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESheet%202%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ECells%20A1%20to%20A50%20contain%20individual%2022%20inch%20rim%20variants%2C%20Cells%20B1%20to%20B50%20contain%20the%20number%20in%20stock%20for%20corresponding%20cell%20in%20A1%3AA50%20(with%20Conditional%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22highlight%22%3EFormatting%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bto%20indicate%20stock%20low%20based%20on%20corresponding%20cell%20in%20C1%3AC50)%2C%20Cells%20C1%20to%20C50%20contain%20that%20variants%20minimum%20stock%20level.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EWhat%20I'm%20trying%20to%20achieve%20is%20a%20formula%20in%20Sheet1!%20C1%20that%20looks%20at%20the%20range%20Sheet2!%20B1%3AB50%20and%20returns%20True%20if%20any%20of%20the%20cells%20in%20Sheet2!%20B1%3AB50%20have%20a%20True%20result%20for%20their%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22highlight%22%3EConditional%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22highlight%22%3EFormatting%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bindicating%20a%20low%20stock%20level.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EI%20thought%20it%20might%20be%20something%20along%20the%20lines%20of%20a%20CSE%20formula%20in%20Cell%20C1%20in%20Sheet%201%3A%20%3DIF(Sheet2!E3%3AE50%26lt%3B%20sheet2!f3%3Af50%2C%22y%22%2C%22%22)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EUnfortunately%20this%20doesn't%20return%20anything%20in%20C1%20when%20any%20cells%20in%20Sheet2!B1%3AB50%20are%20less%20than%20their%20corresponding%20cell%20in%20Sheet2!C1%3AC50%2C%20not%20even%20an%20error.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EAny%20help%20or%20advice%20would%20be%20much%20appreciated.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%20in%20advance.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-524346%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-524395%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20single%20value%20from%20array%20of%20data%20from%20different%20worksheet%20using%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-524395%22%20slang%3D%22en-US%22%3EFor%20clarity%2C%20please%20attach%20your%20sample%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-524684%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20single%20value%20from%20array%20of%20data%20from%20different%20worksheet%20using%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-524684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F332815%22%20target%3D%22_blank%22%3E%40NonEventHorizon%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20to%20return%20Yes%20if%20any%20of%20the%20cells%20in%26nbsp%3B%3CSPAN%3ESheet2!E3%3AE50%20is%20less%20than%20correspondent%20cell%20in%26nbsp%3Bsheet2!f3%3Af50%2C%20when%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3DIF(SUMPRODUCT(--(Sheet2!%24E%243%3A%24E%2450%26lt%3BSheet2!%24F%243%3A%24F%2450))%2C%22Yes%22%2C%22%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-524910%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20single%20value%20from%20array%20of%20data%20from%20different%20worksheet%20using%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-524910%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3C%2FP%3E%3CP%3EThank%20you%20Sergei%2C%20you%20are%20a%20diamond.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-525008%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20single%20value%20from%20array%20of%20data%20from%20different%20worksheet%20using%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-525008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F332815%22%20target%3D%22_blank%22%3E%40NonEventHorizon%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
NonEventHorizon
New Contributor

Hi All,
Hope everyone is fine and fruity. This is my first post here, I've tried to find an answer in the forum posts but to no success. Hopefully someone can give me some advice or point me in the right direction for my problem.


I have a multi sheet workbook for stock control, sheet 1 is a dashboard-type page with links to the other sheets. I'm trying to use the dashboard page as a quick overview to indicate if any low stock levels exist in the product ranges in the other sheets. So the page is something like this:


Column A - Cells A1 to A5 contain product line description and hyperlink to corresponding sheet.
Column B - Cells B1 to B5 contain number of products for that product line in the corresponding sheet.
Column C - Cells C1 to C5 will indicate if any products in that line, on the corresponding sheet, are below a minimum stock level.
e.g
Sheet 1
Cell A1 - 22 inch rim wheels product line (hyper link to sheet2), Cell B1- 20 total 22 inch rims, Cell C1 - (true,false condition) are any of the stock lines low for 22 inch rims in Sheet 2
Sheet 2
Cells A1 to A50 contain individual 22 inch rim variants, Cells B1 to B50 contain the number in stock for corresponding cell in A1:A50 (with Conditional Formatting to indicate stock low based on corresponding cell in C1:C50), Cells C1 to C50 contain that variants minimum stock level.
What I'm trying to achieve is a formula in Sheet1! C1 that looks at the range Sheet2! B1:B50 and returns True if any of the cells in Sheet2! B1:B50 have a True result for their Conditional Formatting indicating a low stock level.


I thought it might be something along the lines of a CSE formula in Cell C1 in Sheet 1: =IF(Sheet2!E3:E50< sheet2!f3:f50,"y","")
Unfortunately this doesn't return anything in C1 when any cells in Sheet2!B1:B50 are less than their corresponding cell in Sheet2!C1:C50, not even an error.

Any help or advice would be much appreciated.
Thanks in advance.

4 Replies
For clarity, please attach your sample file.
Solution

@NonEventHorizon ,

 

If to return Yes if any of the cells in Sheet2!E3:E50 is less than correspondent cell in sheet2!f3:f50, when

=IF(SUMPRODUCT(--(Sheet2!$E$3:$E$50<Sheet2!$F$3:$F$50)),"Yes","")

 

Highlighted

@Sergei Baklan 

Thank you Sergei, you are a diamond.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies