Emma has attended:
Excel Intermediate course
Conditional Formatting
Hello
I was hoping you could help with an Excel question I can’t figure out. I would like for a line to turn red if it’s missing a cost. Each line has multiple currency columns but will only have one box from these currency columns filled in. We want it to recognise that the whole row is without a value. We tried a number of conditional formulas but couldn’t quite find the correct fit.
Any help would be so appreciated. I could also send the template I have which might make things clearer.
Best wishes,
Emma
RE: Conditional Formatting
Hi Emma,
Thank you for the forum question.
Select all the rows in your table. In Conditional Formatting you need to create a new rule. Use A formula to determine which cells to format.
In the formulas box type:
=SUMPRODUCT(--($A2:$G2<>""))=0
Well you have to change the range. You need to put in the range of your first row in your select.
Let us say that you want to test the from B5 to M200. Then the formula should be:
=SUMPRODUCT(--($B5:$M5<>""))=0
I hope this make sense
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Conditional Formatting
Hi Jens
Thank you for replying to me. Although this doesn't change the colour of the row, it just brings up whether the statement is true or false. I would like if the row turned red if all of the columns (currencies) are without a value for that particular row, indicating I am missing a price.
I look forward to hearing from you.
Best
Emma
RE: Conditional Formatting
Hi Emma,
The formula must as in my reply be typed as a new rule in the conditional formatting tool and not in the sheet.
It is right that it will just return true or false if you type it in the worksheet.
Please follow the step below:
Select all the rows in your table. In Conditional Formatting you need to create a new rule. Use A formula to determine which cells to format.
In the formulas box type:
=SUMPRODUCT(--($A2:$G2<>""))=0
Click format and choose the fill colour you will like to use.
Well you have to change the range. You need to put in the range of your first row in your select.
Let us say that you want to test the from B5 to M200. Then the formula should be:
=SUMPRODUCT(--($B5:$M5<>""))=0
I hope this make sense
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
