Fleet Services

You guys have all been a tremendous help for me in the past. I have learned a lot about the more complex excel formulas just for some help. But now I am trying to make a very large spreadsheet for the company I work for that will help us keep track of our fleet. There are some formulas that would help us do this, but I dont know them.


Problem 1
The first thing I need help with is a table I made of all of our drivers. The drivers name is on the left side of the table, and there is other information along the top of the chart. The first column (B) of the table should count the number of driving complaints that each driver has. So the formula should look for the drivers name in column A, find the worksheet with that same name, and count the number of occurances for that name in row F on a tab called "Driving Violations". If count cannot search an entire column, then it can just look at F2:F200 or so.



Problem 2
The second column on the table (C) is in referrence to a certifiction that all of our technicians have. This column should look at the techs name in row A, move to the worksheet "employees", find that name in row a, then find the certifications expiration date from column D, and return that date less three months. The three months is just to give me time to get the tech in a class before the certification expires.

Back on my "Fleet" tab, I have column D as an if statement. Yes is returned if the odometer is greater than the oil change mileage. The only thing is that I want some advanced notice so I can tell my boss what to expect.

heres the formula i am using now:
IF(MAX(INDIRECT(A3&"!"&"d2:d500"))>MAX(INDIRECT(A3&"!"&"e2:e500")), "Yes", "No")

I want yes to return if max of row E minus max of row D is less than or equal to 500 miles.


This is still a work in progress, so I might ask more questions later. But Ill try to figure the rest out on my own first.
Thanks for you help!

=IF(MAX(INDIRECT(A5&"!"&"d2:d500"))-MAX(INDIRECT(A5&"!"&"e2:e500"))>=-500 or(MAX(INDIRECT(A5&"!"&"d2:d500"))>MAX(INDIRECT(A5&"!"&"e2:e500")), "Yes", "No")

This would fix the oil change problem, but the syntex is wrong.

I tried your equation and a box poped up that looked like the box when you click save as. Im not sure what to do with it.

but basically this is what I need:
=COUNT(VLOOKUP(A6,DrivingViolations!F:F,1))

i figured it out, I had the name spelled two different ways. and that box i mentioned that poped up was because i changed the name of the drivers complaints worksheet and didnt change the referrence to that page in the formula. so excel wanted me to update it on were the page was. kind of like what itunes does when you change the file name or delete an mp3 file

But it worked, thanks for your help

heres what I need for the certification problem I mention.

=or(IF((VLOOKUP(A3,Employees!A2:D18,4))>=12/7),Vlookup(A3,Employees!A2:D18,4)<3/08,Yes,No)

This is similar to the oil change problem. Except, instead of 500 miles notice, I want 3 months notice. Im not sure if excel will recognize that 3/08 is a date. Maybe there is a better way to do this.