Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Indirect function - Limitations
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! |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Indirect Function | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
INDIRECT function question | Excel Worksheet Functions | |||
Indirect( ) function loosing values when spreadsheets are closed | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |