Steven,
You don't need Access, just a reference to "Microsoft ActiveX Data Objects
x.x Library".
Your Excel data needs to be in a regular table structure for this to work
correctly.
Then just query the file with SQL.

Here's one:
http://www.microsoft.com/technet/scr.../tips0607.mspx

NickHK


"Steven Cheng" wrote in message
...
NickHK;

I didn't think of this. Is ADO accessible in Excel without referencing to
an Access library file?

"NickHK" wrote:

Steven,
As long as your data in the range "RCSLData" is sorted on your first
criteria, say Period, the you can cut down on the amount of the list
that
you search. You can down further .finding the second criteria only in
the
range that matches the first etc. Something like
Dim PeriodStart As Range
Dim i As Long
Set PeriodStart = Range("RCSLData").Columns(1).Find(what:=Period)
With PeriodStart
Do Until .Offset(i, 0).Value < Period
If .Offset(i, 1).Value = DeptID And .Offset(i, 2).Value =
Account
And .Offset(i, 2).Value = FiscalYear Then
GetValue=<YourValue
Exit Function
End If
i = i + 1
Loop
End With
GetValue = CVErr(xlErrNA)

To me though the easiest way would be to use ADO and just issue an SQL
statement, leaving the hard work up to the db components.
"SELECT FinalValue FROM RCSLData WHERE Account= """ & AccStr & """,
DeptID="
& DeptID & ", DayofPeriod=" DayofPeriod.....etc
You can further process any records returned before returning the
function's
result.
The workbook holding the data would need to closed though and be in a
table
format.

NickHK

"Steven Cheng" wrote in message
...
thanks NickHK. I will check out the .text and .value for the problem
cells.

as for the easier search, how do I find/vlookup to narrow the range
that I
am looking through?

"NickHK" wrote:

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have ToolsOptionsViewZero values checked ?

As for making you search easier:
If you can sort the range "RCSLData" on the worksheet (or sort a
copy),
then
..Find/VlookUp the range that match the first widest criteria, you
will
have
narrowed your search considerable. Work your way through your
criteria
searching a narrowing range, until you find a match, or run out of
rows.
Array are very fast and you can achieve the same as above, but you
have
do
all the work yourself.

NickHK

"Steven Cheng" wrote in
message
...
I have a UDF that goes like this:

Private Function GetValue(Account As String, DeptID As String,
DayofPeriod
As Integer, Period As Integer, FiscalYear As Integer) As Single
Dim rcslarray
Dim tempholdingcell As Double
Dim lastrow As Integer

rcslarray = [RCSLData]

GetValue = 0
For lastrow = 2 To UBound(rcslarray, 1)
If rcslarray(lastrow, 2) = Period And rcslarray(lastrow,
3) =
DeptID
And rcslarray(lastrow, 4) = Account And rcslarray(lastrow, 1) =
FiscalYear
Then
If Mid(Account, 1, 1) = 3 Then
tempholdingcell = rcslarray(lastrow, DayofPeriod +
4)
* -1
Else
tempholdingcell = rcslarray(lastrow, DayofPeriod +
4)
End If
End If
Next
GetValue = tempholdingcell
End Function

basically, its take a range passing it into an array, finding the
values I
want from the parameters passed over by the funciton, and
returning
the
value
I want.

problem is when it is returning a zero value, although the
formating
is
correct in the cells, it shows up as $0 rather than $- as it
should
be. I
tested the value to see if it returned a 0 value to find that
there is
some
"rounding" or lack of precision. is there anyway that i can get
this
done
within the function?

PS. I tried the rounding tempholdingcell but that didn't work and
I
have
tried to alter the data type of GETValue from double to single.

PSS. since the range is rather large and there are 5 data points
to
search
through, is there a way of speeding this up as well? or a better
worksheet
function?