• 408K Members
• 7,350 Online
• 463K Conversations
SOLVED

New Contributor

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

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.

4 Replies

Highlighted
Solution

# Re: Return single value from array of data from different worksheet using IF

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","")

# Re: Return single value from array of data from different worksheet using IF

Thank you Sergei, you are a diamond.

# Re: Return single value from array of data from different worksheet using IF

@NonEventHorizon , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies