Extremely slow INDIRECT() routine. Fix?

[nivoe]

Hey guys

In my main.xlsx I have the sheet Sheet2 and the cell
B2, containing the reference string [A.xlsx]Sheet1!$A$1:$GR$3000.

As you can see, it is referencing to 600'000 cells in another workbook.

Now, in another worksheet of the main workbook I have following formula:

=IF(ISERROR(INDIRECT(Sheet2!$B$2));"";IF(INDIRECT(Sheet2!$B$2)="";"";INDIRECT(Sheet2!$B$2)))

The 1. IF statement makes sure no error appears
The 2. IF statement makes sure no zeros are written where there is no data

My problem:
The above function is triggered 2x600'000 times which makes the whoe workbook extremely slow.

My question:
Is there a faster way (under fast I mean something <1 second

Because right now, it is impossible to work with it and I tried to cut the range into the minimum of 2000x150(= ET) but it is still very slow.

Thank you guys
Kind regards

[TheExcelphile]

Try using IFERROR. It will only need to check INDIRECT once, so will work much faster.

=IFERROR(INDIRECT(Sheet2!$B$2),"")

Don't forget to click the little star to the left of this post if you feel I helped!

[nivoe]

It did improve the speed, but

- the zeros are now appearing where there is no data in the source workbook
any workaround without calling indirect twice?

I would love to have a solution without the indirect function (but I am too rookie to know how).
From my point of view, I think it is still a very slow function for such data ranges

Any ideas?
Thank you so far

[TheExcelphile]

The best solution I can come up with is to use conditional formatting to not display the zeros. There is no real substitute for Indirect.
You could try a VBA solution for your problem, but I won't be able to help you with that...

[kbkumar]

Hi - Can you upload a sample workbook to see if an alternate to indirect could be used.

for your second part of the query, select the data range and custom format (under format cells) them as

Please Login or Register  to view this content.

This will display any cells with 0 as blank

Note that the above format is only for numbers, if there are any percentages etc, you will have to modify this accordingly.

[nivoe]

Ok, I attached the files.

Be aware that my sample files are smaller than the big ones, but you can already experience the slow down.

I had to make the range in Main.xls only to 1000 rows to keep the size under 1 mb.

I'd love to have a solution that really gets the cells of A.xlsx into that one sheet so I can keep conditional formatting easily.

Thank you!!!

AND, empty data in A.xlsx must equal to "", because if I keep the zeros I cannot continue to work with the data as some entries are binary (0,1).

[nivoe]

To everyone

Thanks for the input so far, but I am trying it with VBA because I don't see an end for this

[kbkumar]

Does it help if you move / produce the output in the same workbook i.e. A.xls? Is it anybetter then (in the sample it works ok).

Since we are using Rand function in each cell it would take some time atleast to update all the values in your range, so that explains the slowness here.

For the second bit, I just selected the entire sheet 1 in Main.xls workbook and applied the above mentioned cell formats [0;-0;;@] and this takes care of your second bit.

Hope this helps.