As I do some more experimentation, I think my problem is in the VLOOKUP
function side of it. When I simplify the formula without INDIRECTs, I have:
=VLOOKUP(F6,'[CM - Debt Forecast.xls]Pru ABC'!$B$152:$D$174,3,FALSE),
and at the 24th sheet through the end, the VLOOKUP returns #N/A.

When I copy the worksheet from the other workbook and attach it to the same
workbook as the formulas, and then readjust the VLOOKUPS to use the new sheet
in the same workbook, the formula is fine. Therefore, I am wondering if the
link between the two workbooks has some problems.

I am going to repost this question under VLOOKUP.






"malik641" wrote:


I'm having a similar problem with the indirect function. (Actually, I've
been using it a couple of ways)
Mine references to a Defined Name which holds a Dynamic List of the
sheets relavant to my SUM function based on 2 criteria. This is what
mine looks like:

=SUMPRODUCT(INDEX(INDIRECT("'"&Employees&"'!B4:HA3 2"),MATCH(A2,INDIRECT("'"&Employees&"'!A4:A32"),0) ,MATCH(B1,INDIRECT("'"&Employees&"'!B1:HA1"),0)))

That's just ONE of them....I had such a hard time with this that I
decided to make a macro function for it...Turns out that the macro
function too ENTIRELY too long to recalculate everything...here's what
THAT looks like (just incase you're interested):

Code:
--------------------

Function Productivity(ByVal date1 As Range, ByVal name1 As Range) As Long
'Application.Volatile
On Error Resume Next

Dim dateRng As Range
Dim nameRng As Range
Dim RG1 As Range
Dim RG2 As Range
Dim WS As Range
Dim Total As Long
Const TableDate As Date = #6/25/2005#

Total = 0

For Each WS In Range("Employees").Cells

If date1 <= TableDate Then
Set RG1 = Sheets(WS.Text).Range("A4:A32")
Set RG2 = Sheets(WS.Text).Range("B1:HA1")
Else
Set RG1 = Sheets(WS.Text).Range("A37:A65")
Set RG2 = Sheets(WS.Text).Range("B34:HB34")
End If

For Each Cell In RG1.Cells
If Cell.Value = name1.Value Then
Set nameRng = Cell
End If
Next Cell

For Each Cell In RG2.Cells
If Cell.Value = date1.Value Then
Set dateRng = Cell
End If
Next Cell

If Not nameRng Is Nothing And Not dateRng Is Nothing Then
Total = Total + Intersect(dateRng.EntireColumn, nameRng.EntireRow).Value
End If

Next WS

Productivity = Total

End Function

--------------------

While making the code, I noticed that the formula wouldn't SUM
correctly. This was because of the sheet's that it was referencing.
Something was invalid in some of the sheets causing the function to
create an error and skip that sheet entirely because of the error (and
the following sheets, I believe).

The reason I'm telling you this is because MAYBE your formula is fine,
but maybe something is wrong with the sheet's it is referencing. Check
it out.

Just a little insight :)


--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=468078