Index/Match & Dynamic Range name problem

I’ve started having problems running out of Resources when I have two specific workbooks open which after searching this forum I suspect may be related to Index/Match functions where I referenced the ranges using columns rather than defining the actual ranges (I didn’t know how to make dynamic range names when I created the formulas).

So I revised the Index/Match formulas using dynamic range names rather than simply column references (originally done because the rows within the range vary).
Now I’m getting a #REF! error with the Index/Match formulas using the dynamic range names. The same Index/Match formula with static range names works perfectly.
The range names are worksheet specific. The following is the problem formula and the respective range names:

=IF(AA10="","?",INDEX(INDIRECT($AA10&"!DataTable"&$AA10),MATCH(B10,INDIRECT($AA10&"!DataList"&$AA10),0),3))
DataTableNDX =OFFSET(NDX!$A$1,1,0,COUNTA(NDX!$B:$B)-2,27)
DataListNDX =OFFSET(NDX!$A$1,1,1,COUNTA(NDX!$B:$B)-2,1)

This is the same formula / range names with a “2” suffix using static range names which works fine.

=IF(AA10="","?",INDEX(INDIRECT($AA10&"!DataTable"&$AA10&"2"),MATCH(B10,INDIRECT($AA10&"!DataList"&$AA10&"2"),0),3))
DataTableNDX2 =NDX!$A$2:$AA$99
DataListNDX2 =NDX!$B$2:$B$99

Appreciate any help resolving this issue. Thanks for reading.

Thanks for the reply George. I have attached a sample per your instructions. I tried your syntax suggestion, nothing changed.

Sorry Glenn, clearly I'm not explaining if very well. The "2" version is literally an alternative version where the range names "DataTableNDX2" and "DataListNDX2" are duplicate range names that are statically defined to show the formula works. That is the only difference between the formula on Row 2 vs. Row 3. When the range name is statically defined, i.e. for the "2" version defined like this "=NDX!$A$2:$AA$99" the formula works, and where it is dynamically defined in Row 2 like this "=OFFSET(NDX!$A$1,1,0,COUNTA(NDX!$B:$B)-2,27)" I get an error.

Just ignore Row 3 since that has only added confusion - sorry. How can I make Row 2 work?

So just to ask it more simply,

What are the file sizes of both files?
Are you able to observe any of the issues I mentioned?
When did/does the resources error happen?
Is there anything specific you can do to replicate the resources error?

Thanks for the info Zer0Cool. The formulas above had already been partially revised. Would it change your opinion if I told you the original formulas were in this format: =IF(AA12="","?",INDEX(INDIRECT($AA12&"!A:L"),MATCH(B12,INDIRECT($AA12&"!B:B"),0),3))

and there were six worksheets with 8 or 9 similar formulas each? The files are between 600-800k each. Between the two files the max rows - approx 500, columns - to BG, Sheets -25

No significant excess row / columns.

Styles - I haven't ever (intentionally) modified / added in that section

One file does have approx 100 objects both visible and hidden

I use a lot of range names - one file has over 300, the problem file has approx 100

I do a lot with Events - WorksheetChange, WorksheetSelectionchange, FollowHyperlink, etc.

I'll go back through the two files and double check that some how I don't have any excess content in the files.

I get the resource error when either triggering an Event - worksheetchange or selection change in the problem file or when triggering a macro in the other.

My PC is an i7 3.4G with 16G of RAM - the task manager is never too tasked when I get the our of resources message. Is there a way to increase resources to Excel?

Glenn - I'm ok with a VBA workaround - I should be able to add it to a worksheet change event. What are you thinking?

Thanks Glenn - I'll looking into the links. I guess another question would be would replacing formulas such as this example "=IF(AA12="","?",INDEX(INDIRECT($AA12&"!A:L"),MATCH(B12,INDIRECT($AA12&"!B:B"),0),3))" with a VBA solution save resouress, use more resources or be about the same?

So the resources error generally doesnt really mean you are resource limited.

It often occurs when either you have excess content (which it seems you likely do not) or based on how an operation is done. For example its possible to force the error by copying/pasting in a certain way even in a blank file with 1 entry in a cell.

If you disable events and do not get the error it is likely something one of those events triggers. Since I do not know what each event sub does I cannot say for sure, but it could be something like how it is copying and pasting or how its manipulating some data.

Id troubleshoot it from that angle, diable the events and see if you can trigger the error. If not inspect the code/debug and see at what line/point it causes the error.

Thanks Glenn for your suggestions – I’ve been researching (relatively new to tables) trying to figure out the best alternative for me. I’m inclined to use the structured table (“structured table is the one that automatically adjusts formulas whenever rows/columns are added/deleted – correct?)

My current process is to import a variable length .csv file into the workbook – which is why I needed the dynamic range name initially. If I use your table suggestion, it seems I’ll need a couple extra steps:
1. Import the .csv to a different worksheet,
2. Determine how many table rows are required,
3. Size the table accordingly,
4. Copy the imported info into the table.

Does that make sense? Is there a way that I can Paste the desired data from the import into the table and have the table automatically resize? The limited experience I’ve had with tables was that if the copy selection was larger than the table I’m pasting it to it just overwrites the Totals row at the end of the table.

Appreciate your help and any additional insights you might have. Thanks.

One more question. Is there a way (tool, vba command, etc.) to evaluate how many resources my current file requires vs. one that uses tables? The original goal was to reduce the resources required by my file.

Hmmm, well you are absolutely right – I can replicate your A1:A2 / A1:F20 copy test. Don’t really remember what I did (it was back in Jan) and I managed to create a problem overwriting the table. I’ve added to your reputation for the great help you’ve given me. I would like to leave the thread open a few more day’s just in case I run into an issue converting my current worksheet into a table if that’s ok. I will mark it Solved when I’m finished – it doesn’t reflect badly on anyone leaving it open does it?

I did a prelim search for some type of resource assessment, but I focused it more toward a system / VBA direction. I’ll didn’t think about a separate application. Thanks for the suggestion.

Have a great evening – let me know if leaving this open for now is an issue. Thanks.