Hi,

I have a workbook with many rows - it will have up to 20000 of them. I use other small workbooks that contain vlookup formula connected to the mentioned big workbook. As long as both files are open I have no problems but having big workbook closed (it should stay closed as many employees will take fresh data from it) causes the following - if I select range up to A2;I8000 in a small workbook everything is fine, but exceeding 8000 for example A2;I8500 leads to "excel cannot complete this task with available resources" error.

I seemed to have a solution to this problem but the whole formula acts strange. Creating a small range past 8000 value as for instance A8000:I8050 still causes same error despite having only 51 rows to process. As result it makes impossible to order a series of vlookup checks within smaller ranges with "if" formula depending on whether a given value is found (as I understand it would be a viable workaround if not the given problem). The mentioned vlookup formula is the only formula using range in both workbooks and is located in only one cell.

Is there any solution to bypass this error?