Countif formula (I think) in combination with date range

Hello, I've got some difficult formulas that I'm struggling to get right to enable a small charity to monitor its activities. I've also described them in the link below (highlighted in red) on the "user log" sheet and the "monitoring dashboard" sheet : https://docs.google.com/spreadsheets...it?usp=sharing

If anyone can offer any help I'll be enormously appreciative!


1) conditional formatting depending on status of another cell/date range. SO...
When column A = 3, I'd like to apply conditional formatting to column D depending on whether the date is within last week. Something like =IF(A:A=3,TODAY()-7) then background green.
When column A = 2, I'd like to apply conditional formatting to column D depending on whether the date is within the last two weeks. Something like =IF(A:A=3,TODAY()-14) then background green.

2): a column that looks up a column in a separate sheet, and reports back the most recent date alongiside a specified name

Sheet 1: Sheet 2:
Row Column A Column B Column E Column F
1 Name Last reported Name Report date
2 Bob Hope <23/02/2014> Bob Hope 22/02/2014
3 John Shawer <formula> Bob Hope 19/02/2014
4 Alice Hills <formula> Bob Hope 23/02/2014

I'm looking for a formula for Sheet 1 column B, that results B2 looking-up the most recent report date by Bob Hope. B3 would look-up the most recent report date by John Shawer.

3) Similar to 2, I'm looking for a formula for sheet1 that counts the number of times a cell in sheet 2 is 'YES' AND the name is the same as in column A sheet 1.

4) Count the number of times a column of dates displays a date within the last 7 days... something like: =COUNTIF('Report Log'!C:C,TODAY()-7) ...but apparently not quite that!

5) Similar to above, except I'd like count instances in where J:J=Yes AND Date (C:C) is within last 7 days

6) Finally I'm looking for a formula that gives me % (of the total where column A =3) where column A =3 AND where column D is within last week.

Hugely appreciate any help!