Alternate to Indirect formula?

[asad]

Hello Guys,

I was just wondering, is there any alternate to Indirect function that we could use in formulas to look up values in other files?

Asad

 

[Andrew Fergus]

I presume you are using the Indirect function to derive the name of the other workbook? If this is the case, I'm not aware of any native functions that could be used in place of Indirect - although I would be happy to be proved wrong.

If however Indirect is being used to derive the range being looked at then there are other methods that could be used - it depends on your data.

Andrew

 

[asad]

I presume you are using the Indirect function to derive the name of the other workbook? If this is the case, I'm not aware of any native functions that could be used in place of Indirect - although I would be happy to be proved wrong.

If however Indirect is being used to derive the range being looked at then there are other methods that could be used - it depends on your data.

Andrew

I am using the following formula

VLOOKUP($A7,INDIRECT.EXT(("'E:\Ali Land\MrExcel\Footscray Roster\[FSCY "&TEXT(F$3,"yy-mm-dd")&".xls]Weekly Roster'!$BF$7:$BG$201")),2,FALSE)

It is working fine. Only problem is that it is making the file tooooooooooo slow. The data is upto 200 rows long and stretches over about 150 columns.
I replaced it with

=VLOOKUP($A5,'E:\Ali Land\MrExcel\Footscray Roster\[FSCY 11-05-15.xls]Weekly Roster'!$BF$8:$BG$200,2,FALSE)

This is working exactly the same way and is not making the file slow. So I would prefer this second formula.
But as you can see that I will have to change the file name in each column manually. THAT I wanted to avoid.
Can you suggest anything that will help me?

Asad

 

[Andrew Fergus]

When faced with this sort of issue, and assuming the data isn't constantly changing, then I usually have one row that contains the real formulas and I copy > paste special (values) over the other formulas so I can work with the spreadsheet. Any time you need to accommodate changed data, then you copy the formula down, make a cuppa while the data recalculates and then copy > paste special values again. Not perfect but I understand the frustration of working with slow workbooks.

An alternative method (for when the data is constantly changing either within the spreadsheet or on the other spreadsheet) is to turn off the auto calculation and manually force the recalculation (by pressing F9) any time I want the data refreshed. To turn off the auto calculation go to menu option Tools > Options > Calculation > set to 'Manual'.

I'm the first to admit neither method is perfect but you do what you have to to get the job done. If anyone has another method they use I would want to hear about that too.

The issue with the Indirect function is that it is volatile. This means that any time you want to calculate anything, every formula that contains this function will be recalculated whether it needs it or not (hence my temporary solutions of either removing the formulas or manual recalculation). Another anomaly with Indirect is that Excel doesn't immediately recognise this formula as containing a link to an external workbook - notice the other spreadsheet will not be listed as an external link under menu option Edit > Links.

Andrew

 

[asad]

You are right, I may take up the option of manual calculations by pressing F9.

Thanks for the suggestion.

Asad