Conditional Formatting

[Andy B1963]

Hi

have a workbook with 2 spread sheets

sheet 1 fleet vehicles

column A "fleet Numbers"
column B "locations"
column C "test date"
column D "expire date"

sheet 2

summarises location of vehicles

trying to workout a formula whereby on summary sheet highlights the "fleet Number" cell when a vehicle test date has expired

hope someone can help

 

[Special-K99]

You havent defined the format of Sheet 2, it cant just be a list of locations as you've specified surely?

 

[Andy B1963]

sheet 2

columns A,b,c,d etc are the locations

rows 2,3,4,5,6, etc. return fleet numbers from sheet 1

so as the vehicles have to be more from location to location daily I can keep track on the summary sheet when sheet 1 is updated each night

Iferror index formula used for this

but would like the fleet numbers on the highlight when have reached expired date.

 

[Special-K99]

Some versions of Excel Condtional Formtting cannot reference data on other sheets.
The workaround is to create a Named range and use that in the formula instaead of a specific range.
Am not sure if you need to create a named Range for column A or D or even A:D, youll have to experiment to see which works.
You may not even need a Named range depending on your version of Excel.

Here's the normal formula, replace range with Named range if this doesn't work

Select the range for column A, e.g A1:A100

Conditional Formatting
New Rule
Use a formula to determine...

=AND(VLOOKUP(A2,Sheet1!A$1:D$100,4,0)>0,VLOOKUP(A2,Sheet1!A$1:D$1000,4,0)<TODAY())

Format as required

 

[Andy B1963]

Hi

Sorry only just found time to come back

still struggling with this a bit

"Sheet1" lists all veh's
"Sheet2 brings back report of expired dates, veh's & locations

Sheet1

[TABLE="width: 604"]
<colgroup><col width="159" style="width: 119pt;"><col width="80" style="width: 60pt;"><col width="173" style="width: 130pt;"><col width="192" style="width: 144pt;"></colgroup><tbody>[TR]
[TD="class: xl75, width: 159, align: center"] Vehicle No.[/TD]
[TD="class: xl74, width: 80, align: center"]Location [/TD]
[TD="class: xl75, width: 173, align: center"]Test Date [/TD]
[TD="class: xl71, width: 192, align: center"]Expire Date [/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"]675[/TD]
[TD="class: xl67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] "]London[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"]31/01/2018[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"]31/01/2019[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"]790[/TD]
[TD="class: xl67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] "]Bodmin[/TD]
[TD="class: xl68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"]28/10/2018[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"]28/10/2019[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 604"]
<tbody>[TR]
[TD="class: xl75, width: 159, align: center"]Expire Dates[/TD]
[TD="class: xl74, width: 80, align: center"]Vehicle. No.[/TD]
[TD="class: xl75, width: 173, align: center"]Location [/TD]
[TD="class: xl71, width: 192, align: center"]Comments [/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"]31/01/2019[/TD]
[TD="class: xl67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] "] 675[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"]London[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"][/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"][/TD]
[TD="class: xl67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] "][/TD]
[TD="class: xl68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"][/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Think am looking for iferror

any help would be great