The easy part first:

Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)

Application.caller is the cell that holds the formula

so application.caller.parent is the worksheet that holds that cell that holds
the formula

so appliacation.caller.parent.parent is the workbook that holds that worksheet
that holds the cell that holds the formula

So you're finding the correct workbook and then using a worksheet named: ACN-
(and whatever you pass as S)


I didn't open your workbook (or look at other posts in the thread...),

But you're passing the date (as d) in:

Function ACNLookup( _
s As string, _
p As String, _
d As Variant _
) As Variant

I think one of these should be using p and one should be using d:

r = Application.Worksheet.Match(p, ws.Range("A4:A30"), 0)
c = Application.Worksheet.Match(p, ws.Range("B3:IV3"), 0)

If the dates are in row 3, then use D in the second line. If the dates are in
column A, then use it in the first line.



Sami82 wrote:

Hi Harlan,

Thank you for your help once again.

I'm put this in as a module (function) in excel, but it doesnt seem to
want to work for me. If there is nothing in the state cell reference i
get #REF, which is fine, but as soon as I put any of the correct
references in I keep getting #NA. Is there something I should be
editing to make this work.

I think I understand most of what is being done in this code but I am
stumped on the following line:

Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)

Also I can't work out how it references the date?

Thank you again!

Harlan Grove Wrote:
"Sami82" wrote...
This seemed to work, thank you very much. But i was hoping that I
could simplify it further by making a custom function, so that it
would be easy to explain to basic users, it would look something
like this:

=ACNLookup(State,product,date)
....

Without minimal error checking, something like


Function ACNLookup( _
s As string, _
p As String, _
d As Variant _
) As Variant
'----------------------
Dim ws As Worksheet, r As Long, c As Long

On Error Resume Next

Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)

If Err.Number < 0 Then
ACNLookup = CVErr(xlErrRef) 'bad worksheet, return #REF!
Exit Function
End If

r = Application.Worksheet.Match(p, ws.Range("A4:A30"), 0)
c = Application.Worksheet.Match(p, ws.Range("B3:IV3"), 0)

If Err.Number < 0 Then
ACNLookup = CVErr(xlErrNA) 'bad prod/date, return #N/A
Exit Function
End If

ACNLookup = ws.Range("A3").Offset(r, c).Value
End Function

--
Sami82
------------------------------------------------------------------------
Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111
View this thread: http://www.excelforum.com/showthread...hreadid=466252

--

Dave Peterson