May 03 2019 03:18 AM
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.
May 03 2019 03:33 AM
May 03 2019 04:00 AM
Solution
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","")
May 03 2019 04:35 AM
Thank you Sergei, you are a diamond.
May 03 2019 04:56 AM
@NonEventHorizon , you are welcome
May 03 2019 04:00 AM
Solution
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","")