Bernie,

Thanks for the help - I tried you suggestion and it still only works on the
sheet I that is active.

Any ideas?

Ta

Andi

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Andi,

Get rid of all this:

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)


and use

Set Var_Date = Application.Caller.Parent.Cells(2,
Application.Caller.Column)
Set Var_Name = Application.Caller.Parent.Cells(Application.Caller .Row, 1)

HTH,
Bernie
MS Excel MVP


"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