conditional formatting with multiple rows
I have a spreadsheet with which users register the results of random breathalyzer tests.
I have it set up so that if the result is ever FAIL, the text goes red
In column B is the name of the person tested, column D is the pass/fail.
how would I go about highlighting the cell if a person has failed twice or more?
can conditional formatting do this? or will I need a macro?
Last edited by tutigan; 03-02-2011 at 12:35 AM.
Hi and welcome to the board,
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
hi arthurbr thank you for welcome and advice
I have attached a dummy workbook with how it is at the moment, with only the highlighting of text, if the text reads "FAIL"
if any clarification is needed, or anyone has questions, or cannot understand my English, please feel free to ask!
Hi,
I hthink this is what you want.
For Conditional Formatting, I selected the whole column and formatted it to Font Color = Red when Cell value = FAIL
I created a formula in "Comments" column to show "More Than Once" if the person's name as appeared before.
Hope this helps.
Hi teelim, sorry for confusion, let me clarify.Originally Posted by teelim
some people are on-site many times, so will appear many times in the list, but not always as a fail (I hope!) so I want the colour to change (or the comment to appear - good thinking with that!) if that person has more than one fail against their name
so for instance, if "mr person" appears seven times in the list, each time with a PASS, then nothing happens, but if "someone else" appears four times, and two of them are FAIL, then have the FAIL cell background change colour to red (and black text)
It is not clear to me what you want highlighted if a person fails more than once. I assumed it is the name.
See attached, and don't hesitate to come back if you need more info
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
sorry, I think this makes more sense...
in attached, "mr man" has failed more than once, so the 'fail' cell has been highlighted.
I want to be able to have that happen automatically - so if a person fails once, the text 'FAIL' turns red, if they fail again (>1) then the text 'FAIL' stays red, but the cell is highlighted (like in attached)
I hope that makes more sense!
To keep things simple I added a helper column to count the nr of occurrences of FAIL for a person, then used the column to implement the CF
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
thank you so much arthurbr!
My pleasure
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
My pleasure
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
hi arthurbr,
I've just tried using that but found one small problem... the formula you used is for a set range of cells, but I need it for the entire column... I changed it from:
=SUMPRODUCT(($B$3:$B$10=B3)*($D$3:$D$10="fail"))
to:
=SUMPRODUCT(($B:$B=B3)*($D$3:$D$10="fail"))
but now the cell text says #NUM!
I tried changing it instead to:
=SUMPRODUCT(($B$3:$B$99=B4)*($D$3:$D$10="fail"))
and now get #N/A! as the cell text
I presume this is because those cells are empty... is there anyway around this?
sorry, I've realised my mistake... I forgot to change the values for the D column!
=SUMPRODUCT(($B$3:$B$999=B4)*($D$3:$D$999="fail"))
Glad you found a solution. SUMPRODUCT in versions prior to 2007 do not accept entire columns like $B:$B, and the ranges should have the same length, as you found out.
Extensive info on this function at http://www.xldynamic.com/source/xld....T.html#classic
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks