I'm trying to generate a second table that tells me the name of the Labor Group (A) for each red cell.
Example, My list for Column 850 should be Appliances, GDO, Plumbing-Water Heater
I'm trying to generate a second table that tells me the name of the Labor Group (A) for each red cell.
Example, My list for Column 850 should be Appliances, GDO, Plumbing-Water Heater
Hello,
what you describe cannot easily be done with formulas. First, colour is not data and Excel cannot evaluate the fill colour of a cell in any formula.
What causes the cells to be red or green? Are you using conditional formatting? If so, that is a good starting point, because you already have established a logic to identify cells. That may be a hook for further processing.
Can you provide more detail?
Yes, conditional formatting. If the cell is greater than the Service Standard, the cell is Red. In the other table I would like it to come out saying:
"Your discrepancies are in Appliances, GDO, Plumbing Water Heaters"
OK, here is a simplified example. The formula in C19 of the screenshot is
=IFERROR(INDEX($A$4:$A$14,SMALL(IF(C$4:C$14>$B$4:$B$14,ROW($A$4:$A$14)-3,""),ROW(A1))),"")
copied across and down. How far down depends on how many rows of data you expect, up to the total number of rows in your original table.
2019-01-09_09-45-43.png
PS: If that formula does not work for you, try entering it as an array formula with CTRL-SHIFT-ENTER. I'm on the Excel Insider build that has the new functions where all formulas are treated as arrays automatically.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks