Is there an alternative use to the 'Indirect' formula?

[smerrick]

Hello,

I am using the indirect formula to link information from another spread sheet. Whilst the formula does work there are 2 issues with using this:

1, The file is very slow and takes minutes to update as opposed to seconds
2, Unless the linked files are all open the formula result shows #Ref !

What I want to know please is if there is a suitable substitute that I can use to help eliminate the above issues?

The formula that I have is the following inserted in cell D4:

=INDIRECT("'[Dist_June.xlsm]"&$B4"'!B6)

Where cell B4 is the tab name.


Any help will be greatly appreciated.

Thank You

 

[Legacy 143009]

Hello,

I am using the indirect formula to link information from another spread sheet. Whilst the formula does work there are 2 issues with using this:

1, The file is very slow and takes minutes to update as opposed to seconds
2, Unless the linked files are all open the formula result shows #Ref !

What I want to know please is if there is a suitable substitute that I can use to help eliminate the above issues?

The formula that I have is the following inserted in cell D4:

=INDIRECT("'[Dist_June.xlsm]"&$B4"'!B6)

Where cell B4 is the tab name.


Any help will be greatly appreciated.

Thank You

If I were you, I would prepare a helper sheet in local workbook which pulls the data directly from the required workbooks and anll revelant data with the same column headers and etc...
Then I would refer to that sheet's columns using INDIRECT() function.

I don't know if there is a more practicle way....