Google Spreadsheet Post #1772
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Sep-26-2014
post by robodawg42:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/TwxMuRFJ2Yw)
How do I use conditional formatting governed by multiple cells?
I'm trying to use Sheets to make a list of items, the materials required to make them, costs of those materials, and calculate a total cost for each item. It's a rather large spreadsheet. I'm using the SUMPRODUCT formula to generate the cost of each item. However, I'd like to format the cost cell to turn red if I've forgotten to input a value for the cost of a material for a given item. I know how conditional formatting works, but I don't know the formula to use. Here's some codefu that's roughly what I'm looking for:
IF(AND(Material > 0, MaterialCost = 0), Red, White)
This works when I use one cell for Material and MaterialCost, however I'd need to check over the entire SUMPRODUCT arrays; in other words, this needs to check if Material1 > 0 and MaterialCost1 = 0, then Material2and MaterialCost2, etc. It would then turn red if any of the checks comes out true. I don't want the cost cell to turn red if that item doesn't require the material whose cost is missing.
I'm not sure how to get the logic formulas to check the entire list and return values without writing each check explicitly, which would be a huge pain in the ass. I've tried using something like Cell1:Cell50 in the logic formula but it doesn't return values.
Any ideas?
---
Alright I've created a test sheet that looks similar to what I'm doing:
https://docs.google.com/
So I've got prices listed in row 2, and Material 2 has no price listed. Therefore any item that requires material two (two and four) has its cost cell turn red, as I've done manually. The actual sheet is about 30x60, so like I said I don't want to type out each manually.
GimeIG's answer is a bit different than I want, it ends up turning the material cell red instead of the cost cell.
----------------------------------------------------------------------------------------------------------------------------
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Sep-26-2014
post by robodawg42:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/TwxMuRFJ2Yw)
How do I use conditional formatting governed by multiple cells?
I'm trying to use Sheets to make a list of items, the materials required to make them, costs of those materials, and calculate a total cost for each item. It's a rather large spreadsheet. I'm using the SUMPRODUCT formula to generate the cost of each item. However, I'd like to format the cost cell to turn red if I've forgotten to input a value for the cost of a material for a given item. I know how conditional formatting works, but I don't know the formula to use. Here's some codefu that's roughly what I'm looking for:
IF(AND(Material > 0, MaterialCost = 0), Red, White)
This works when I use one cell for Material and MaterialCost, however I'd need to check over the entire SUMPRODUCT arrays; in other words, this needs to check if Material1 > 0 and MaterialCost1 = 0, then Material2and MaterialCost2, etc. It would then turn red if any of the checks comes out true. I don't want the cost cell to turn red if that item doesn't require the material whose cost is missing.
I'm not sure how to get the logic formulas to check the entire list and return values without writing each check explicitly, which would be a huge pain in the ass. I've tried using something like Cell1:Cell50 in the logic formula but it doesn't return values.
Any ideas?
---
Alright I've created a test sheet that looks similar to what I'm doing:
https://docs.google.com/
So I've got prices listed in row 2, and Material 2 has no price listed. Therefore any item that requires material two (two and four) has its cost cell turn red, as I've done manually. The actual sheet is about 30x60, so like I said I don't want to type out each manually.
GimeIG's answer is a bit different than I want, it ends up turning the material cell red instead of the cost cell.
----------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment