Reading values from a file referenced in a cell

I have a column with the folder name, a column with the filename and a column which creates the entire path to the data I want which is:

'/Users/darren/Desktop/3401/[CAR Master.xlsx]Master'!$C$4

If I use a cell containing:

='/Users/darren/Desktop/3401/[CAR Master.xlsx]Master'!$C$4

then it reads the location C4 from tab Master in the excel spreadsheet "CAR Master.xlsx" in folder "/Users/darren/Desktop/3401" and I get the correct value.

However if I use

=INDIRECT(C3) I get #ref I have also tried =INDIRECT(C3,false) but still get #ref.

Any ideas?

Unfortunately, the INDIRECT function can only work on files that are open, so you can't use it in this case.

The only alternative that I know of (well, there are 2 actually) is to download the free add-in called morefunc - you can do a Google search for sites where you can download it from. Once installed, this has the extra function INDIRECT.EXT, which can be used with external (closed) files in the way you want. Some contributors here have noted that it works with Excel 2010, although it was originally written for much earlier versions of Excel, but I don't know about versions since then (and your profile doesn't state which version you are using).

The second alternative is a PULL UDF written by Harlan Grove - again, written some time ago, and again you should be able to find it through Google.

I think both these use the approach to open the external file in the background, retrieve the data, and then close the file.

Hope this helps.

Pete

Originally Posted by cowasaki

... If I specify specific locations I can access closed files......

Yes, I know you can, but you want to have variable locations which are specified as text strings. Normally, you would use INDIRECT for this, but it doesn't work with closed files.

Can you open the file in the background?

Pete

It's 50 files which other people might need to open....

Looks like I need to create a row on the hidden tab of each of the 50 files and copy the cell data to one line on that tab then place a direct read to the first cell

='/Users/darren/Desktop/3401/[CAR Master.xlsx]Hidden'!$C4

Then copy this to the read only manager sheet into the first column for each file and then just copy it across allowing excel to increase the 4 to 5,6,7,8,9 etc

then copy the first cell down as above, change 3401 to 3402 and do the process again. It's not as elegant but it will at least work

Thank you for your input, it is appreciated.