Conditional Formatting Tricky question

  • Thread starter Thread starter CS
  • Start date Start date
C

CS

Hi,

I have a spreadsheet which is being used to manage assets which go out on hire.

Each new hire is added to a row. However I need to ensure no duplicate assets are added to another row if already on hire.

Customer Assett Status Date Off Hire
Smiths Tank1 On Hire
Blacks Tank2 Off Hire 3/12/2014
Davis Tank3 Off Hire 6/1/2015
Jones Tank2 On Hire
Wilson Tank1

Alert is raised and duplicates highlighted based on Tank1 already being On Hire. Notice Tank2 is ok to go on hire because it was previously taken off hire so not a duplicate on hire.

What is the best way to achieve this?

COUNTIF is where I started, but never quite works out how it needs to.

Asset field is populated via Data validation list (assets in another sheet)
Status is also a data validation field "On Hire" & "Off Hire"

Appreciate any help with this. It is doing my head in.

Thanks.
 
Hi,

Am Thu, 8 Jan 2015 22:59:10 -0800 (PST) schrieb CS:
Customer Assett Status Date Off Hire
Smiths Tank1 On Hire
Blacks Tank2 Off Hire 3/12/2014
Davis Tank3 Off Hire 6/1/2015
Jones Tank2 On Hire
Wilson Tank1
Click to expand...

select A2:Dn => Conditional Formatting
and use one of the following formulas:
=SUMPRODUCT(--($B$2:$B2=$B2),--($C$2:$C2="On Hire"))
or
=COUNTIFS($B$2:$B2,$B2,$C$2:$C2,"On Hire")


Regards
Claus B.
 
Hi,

Am Thu, 8 Jan 2015 22:59:10 -0800 (PST) schrieb CS:


select A2:Dn => Conditional Formatting
and use one of the following formulas:
=SUMPRODUCT(--($B$2:$B2=$B2),--($C$2:$C2="On Hire"))
or
=COUNTIFS($B$2:$B2,$B2,$C$2:$C2,"On Hire")


Regards
Claus B.
Click to expand...

Thanks Claus.
The SUMPRODUCT wouldn't work, kept getting a syntax error. But the COUNTIFSdoes sort of work. The only problem is the valid on hire entry highlights as a duplicate. Somehow need to have it highlighted only if the on hires are duplicate also. Otherwise it does work mostly. If two entries are added, they both highlight. However changing the first one to off hire keeps the most recent one highlighted.

Hope that makes sense. I almost need to do an if greater than forumla. I have found that formulas in the conditional formatting dialog never work the same as in a cell.
 
Hi,

Am Thu, 8 Jan 2015 23:41:05 -0800 (PST) schrieb CS:
The SUMPRODUCT wouldn't work, kept getting a syntax error. But the COUNTIFS does sort of work. The only problem is the valid on hire entry highlights as a duplicate. Somehow need to have it highlighted only if the on hires are duplicate also. Otherwise it does work mostly. If two entries are added, they both highlight. However changing the first one to off hire keeps the most recent one highlighted.
Click to expand...

look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for workbook "CF"


Regards
Claus B.
 
Back
Top