Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INDIRECT Function Alternatives? (XP/SP2)

    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...

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDIRECT Function Alternatives? (XP/SP2)

    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

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDIRECT Function Alternatives? (XP/SP2)

    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,

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: INDIRECT Function Alternatives? (XP/SP2)

    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
    [MS MVP - Word]

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDIRECT Function Alternatives? (XP/SP2)

    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,

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: INDIRECT Function Alternatives? (XP/SP2)

    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
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •