INDIRECT Function Alternatives? (XP/SP2)

Topic

New Reply

Good afternoon to one and all.

Here’s my current problem. I have a user who is using the INDIRECT function to refer to a cell that contains a filename, making it possible for him to simply type a filename in that cell in order to obtain data from that file. (He is using a master sheet to get information from many similar sub-files.) The problem is that if the file he wants is not open, the INDIRECT gives a REF error. And because he has so many subfiles, he’s looking for a way to overcome this problem, short of having to open each subfile.

Does anyone have any ideas on how he/I might be able to get around this?

Thanks in advance…

Reply | Quote

Replies

George,

Another alternative would be to let the EndUser select the file or files from a ComboBox. You would have to create a table say in Sheet2 where ColA = Filename and ColB =Path of File, assign the ComboBox to this table and use the following code to open the file upon selection.

Workbooks.Open filename:=”” & Var1 & “” & Var2 & “.xls”

Var1 would be the path to the file(s) and Var2 the file name itself. I made the assumption that all files being retrieved are XL files.

John

Reply | Quote

Hi John,

Thanks for your suggestion. I am inferring that, based on your suggested approach, that the selected file(s) would have to be open (i.e., there is no way to get the info without opening the file). I like your idea about using a combo box to let the user select the file from a combo box. I’ll give it a shot and let you know how it comes out.

Thanks again,

Reply | Quote

Hi George,

The way I’ve overcome the ‘open’ workbook limitation of INDIRECT() is to have a number of (hidden) worksheets in my target workbook that explicitly refer to source data in each of the normally closed workbooks. I then only need to use the INDIRECT() function to display the data from the appropriately-named worksheets in my open workbook.

Hope this helps

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks for the suggestion; I will pass it along to the user and see if he is interested in such an approach.

I’m curious about file size with this approach. If the user ultimately has as many as 80 or 90 files (or more), how would that affect the file size of the master file? Any ideas?

Thanks again for the suggestion.

Regards,

Reply | Quote

Hi George,

The size of the target workbook really depends on the number of linked cells. The last time I did this, was for controlling three sets of five dispatch records from 99 overseas locations to 148 domestic destinations. My target workbook had 5 worksheets, each using INDEX()/MATCH() referencing about 450 cells in their respective sources, plus the master worksheet with the INDIRECT() formula for viewing each of these. The master worksheet allowed me to select whichever overseas dispatch I was interested in for any domestic destination. Including formatting, the target workbook was about 0.5Mb (which isn’t large by my standards) and, even on a Pentium 233, changing the indirect references gave an almost instantaneous update.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote