Hi

I am getting steadily to grips with excel's more interesting
functions, but am definitely no expert!

I have a spreadsheet with the following ranges:

Sheet 1 - Absence Summary.
Sheet 2 - Formula Data Sheet (Hidden from view)
Sheets 3-75 - Individual employee absence sheets titled '"Surname",
"Initial"'

I have the following named ranges:
Absence_Date: Workbook level named range.
One_year_ago: Workbook level.
Absence_Code: Worksheet level.
Workdays_since_start_date: Worksheet level
Workdays_in_last_year:Worksheet level

The following ranges are used (e.g.)
Absence Summary B9 = (Text) Surname
Absence Summary C9 = (Text) Initial
Absence Summary E9 = (Date) Start Date

I have entered the following formula into Absence Summary F9:
=(SUMPRODUCT(--
(ABSENCE_DATE=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))*(--
(ABSENCE_DATE<=(TODAY())))*(INDIRECT("'"&$B9&", "&$C9&"'!
Absence_Code"={"A","U","S","US"}))))/workdays_in_last_year

What I wanted it to do is to look up the sheet titled "Surname",
"Initial", and count all cells that contain either an "a", "u", "s",
or "us" in the named range "Absence_Code" between one year ago and
today, then divide that number by the number of workdays in the last
year specified in the formula data sheet in named range
"Workdays_in_last_year".

I get the result "Ref" so I am obviously doing something wrong.

Can anyone help?