Replace INDIRECT with more elegant function

It is more likely that your laptop is running on an older version of Excel, older OS or lower Ram. It is also likely that one system is in a 64 bit environment and the other is in 32 bit (this matters only when trying to use more RAM as 32 Bit OFFICE will utilize less RAM where the 64 BIT will allow you to access virtually all your RAM). So I would say compare the specs as the indirect function matched with an index/match is a solid solution that is recommended when pulling from other workbooks and/or multiple sheets. You can take the time to write the exact lookup instead but then you lose flexibility and I would not venture that way.

I think you will find that in comparing the specs of the office pc to your laptop you will find that one has higher RAM, better processor and likely a different environment all together. Note that just because you are in a 64 bit Windows environment does not mean your OFFICE is also 64 bit, you can run (A lot of people have it setup to) run 32bit OFFICE on a 64bit windows environment.

But directly to your question, I do not believe there is a better solution for what you are doing with that many books, sheets and records! (But I am not all knowing so perhaps someone else will have a trick up their sleeve)

Cheers -

There is some Ram issues I know I ran into with 2007 when utilizing more than 7 sheets.
Check your office version though - Go to File>Account> About Excel or it may be in the Help toolbar.

But like you said if that is the latency source there is not much you can do about it now -

Cheers

Just for clarification on the RAM limitations, it doesn't matter if your machine is 32 or 64 bit as much as what the installation of Office is. The default installation on both 32 and 64 Windows is 32 bit Office. You have to take a few extra steps at the beginning of the installation process to install it as 64 bit Office on a 64 bit Windows machine. The RAM issue only comes into play in very large workbooks.

As for the delay, you can test RAM speed, etc. but in general speed is a relative issue and there are many, many reasons why this could be happening. I highly doubt this is being cause by Excel not being able to access more than 2 GB of RAM.

My alternative to your problem is try to set up an intermediate cell where the Indirect formula would be and reference that cell. You are currently calculating the Indirect formula three times, but if you used an intermediate cell then it should cut the speed down a bit. It might not be noticeable, but worth a try.

Good luck.

abousetta

The indirect function will slow down the performance.

But why not all the data in 1 sheet (instead of 2 files)?

I know it's been 5 years, but I stumbled across this when I was searching for an alternative.
If anyone else is in need of a solution, this UDF (found on another forum) helped me.

Public Function IndirectNotVolatile(sheetName As String, sheetRange As Range) As Variant
Set IndirectNotVolatile = Worksheets(sheetName).Range(sheetRange.Address)
End Function

Originally Posted by jbiehl

I know it's been 5 years, but I stumbled across this when I was searching for an alternative.
If anyone else is in need of a solution, this UDF (found on another forum) helped me.

Public Function IndirectNotVolatile(sheetName As String, sheetRange As Range) As Variant
Set IndirectNotVolatile = Worksheets(sheetName).Range(sheetRange.Address)
End Function

Thanks for the input, Im sure others will find this helpful.

For those that use thos suggestion, keep in mind that a UDF (User Defined Function) is really a macro, and your file will need to be saved in .xlsm format