Conditional formatting not matching third criteria

[Ochimus]

Col A shows the Equipment number, Col B the date rented out and Col C date returned.

Cols I - S are the dates from 1 - 16 Jan.

Conditional Formatting formula works perfectly in identifying the "date ranges", but posts them sequentially in the rows matching the dates in Cols A - C.
=AND($B2<=I$1,$C2>=I$1)

But when the Equipment is added, it doesn't work:
=AND($A2=$H2,$B2<=I$1,$C2>=I$1)
Correct location is shown by thick border around each bloc of cells.

All solutions, suggestions and recommendations received gratefully as ever,

Ochimus

[etaf]

i think this will work
=AND(INDEX($B:$B,MATCH($H2,$A:$A,0))<=I$1,INDEX($C:$C,MATCH($H2,$A:$A,0))>=I$1)

[6StringJazzer]

$A2=$H2

This conditions means "if the value in column A matches the value of column H in the same row." From what I can guess (there is no description of what you are trying to do) you really want to use the equipment number in column H and look up the data for that equipment in columns A:D.

Change your formula for yellow formatting to:

Please Login or Register  to view this content.

and it will match your blocks that say "S/be here"


This will also mean that the extra condition you added is not necessary because the VLOOKUP already takes into account matching the equipment number.

[6StringJazzer]

I didn't preview before posting--etaf has essentially the same solution but uses INDEX/MATCH instead of VLOOKUP.

[Ochimus]

Many thanks for the prompt responses which resolved what I posted but - as shown on the revised attachment - throws up a second issue.

I assumed the formula would search and match every row where Col A matched the Equipment number, filling the relevant dates on the same row, so Equipment 1 would fill not only 5th - 7th as it does, but would also fill the second instance on 11th.

As shown on the attached it fills only the first instance.

Hope someone can see how to get round that?

Ochimus

[Fluff13]

How about
=SUMPRODUCT((Equipment=$H2)*(From<=I$1)*(To>=I$1))

[Ochimus]

More interesting by the minute!

As you can see on the attached, SUMPRODUCT approach added the second rental to Equipment 1, but not to any others.

Even tried adding different CFs to each Equipment row, and that didn't work.

Ochimus

[Fluff13]

You might want to check the dates that you have entered for the last to rows (the ones in blue)

[Ochimus]

Fluff,

Many thanks for seeing the error. Obviously should have stayed in bed today!

Thanks to everyone who solved this for me. It's ridiculous when I can't resolve what should be simple!

Will now mark this as Solved

Ochimus

[Fluff13]

Glad to help & thanks for the feedback