I have a complicated indirect function reference. After about 24 sheets it
is not bringing back the correct cell value (through a vlookup). Is there
some limit to the amount of memory required for the use of Indirects?

FYI, I have a large workbook with 60+ worksheets, and an indirect function
which references (1) the cell in the workbook which contains the name of
another workbook, (2) the tab in this other workbook, and (3) the vlookup
range on that particular sheet.

My formula looks like this:
=VLOOKUP(F6,INDIRECT("'["&Index!P8&"]"&J5&"'!$b$152:$h$174"),3). This
formula works fine for the first 24 tabs, but then subsequent tabs bring back
identical values. - - I've checked the vlookup portion of the formula and
that works fine.

Any advice? I am considering not leveraging the Indirect function so much
in my spreadsheets. Thanks!

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

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