Tom,

I changed Var_Name and Var_Date as suggested but it still only works on the
sheet in focus.

Do I need to change the part of HolAvail = Evaluate("SUMPRODUCT......?

Ta

Andi


"Tom Ogilvy" wrote in message
...
all your unqualifed ranges would refer to the activesheet.

for example,

Set Var_Date = Range(Var_Date_Column & "2")

refers to the activesheet.

You would need to qualify these ranges with the sh reference

Dim sh as Worksheet
set sh = Application.Caller.Parent

Set Var_Date = sh.Range(Var_Date_Column & "2")

as an example.

--
Regards,
Tom Ogilvy



"Andibevan" wrote in message
...
Hi All,

The following function works fine, but only on for the worksheet which
the
user is currently looking at.

Can anyone provide some guidance so that this code might work on 3-4
different worksheets within 1 workbook?

Function HolAvail()

Application.Volatile



Dim Var_Name As Range 'Location of Name

Dim Var_Date As Range 'Location of Date

Dim Var_Name_Row As String 'Name row

Dim Var_Date_Column As String 'Date Column



Var_Date_Column = Application.Caller.Column 'Column where date is



'Converts Column number into Column Letter

If Var_Date_Column 26 Then

Var_Date_Column = Chr(Int((Var_Date_Column - 1) / 26) + 64) & _

Chr(((Var_Date_Column - 1) Mod 26) + 65)

Else

' Columns A-Z

Var_Date_Column = Chr(Var_Date_Column + 64)

End If



Set Var_Date = Range(Var_Date_Column & "2")



Var_Name_Row = Application.Caller.Row 'Row where name is



Set Var_Name = Range("A" & Var_Name_Row)



'Criteria to lookup against holiday / resource list

HolAvail = Evaluate("SUMPRODUCT((" & Var_Date.Address & _

"=Hol_Start)*(" & Var_Date.Address & _

"<=Hol_End)*(" & Var_Name.Address & _

"=Hol_Name)*(Hol_Type_Code))")



If (Evaluate("SUMPRODUCT((" & Var_Date.Address & _

"=Hol_Start)*(" & Var_Date.Address & _

"<=Hol_End)*( Hol_Name =""Public
Holiday"")*(Hol_Type_Code))"))

= 2 Then



HolAvail = 2

Else

HolAvail = HolAvail

End If

'SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Name ="Public

Holiday")*(Hol_Type_Code))

'If (Evaluate(SumProduct((I2 = Hol_Start) * (I2 <= Hol_End) * (Hol_Name
=

"Public Holiday") * (Hol_Type_Code)))) Then



'End If









'Workdays are Blank

HolAvail = IIf(HolAvail = 0, "", HolAvail)



'Weekends are W (Weekdays 7 & 1)

HolAvail = IIf((Weekday(Var_Date) = 7) Or (Weekday(Var_Date) = 1), _

"W", HolAvail)



End Function



Ta



Andi