Referring to a dynamic range of cells in another Excel file 1

[yalamo]

I have a rectangular array of cells in one Excel file that I want to refer to in a vlookup function in another file. Unfortunately, the array is dynamic, since I am constantly adding data lines to it.

When the referred-to file is open, everything is okay. I have a cell in that file which contains the range of the array, and changes as the array changes. This cell could be cell K1 in the first file, and contains the text "A1.J32". Then in the second file I have a cell D1 containing the text: "D:\Spreadsheets\[FirstFile]Sheet1!A1.J32" which uses the information from cell K1 in the first file.

I then do my vlookup: Vlookup(x,indirect(D1),5). This works fine if the file Firstfile is open, but if I close it, then I get a #REF! in the vlookup cell in the second file.

Anyone know how (or if) I can do the lookup with the first file closed?

 

[mike1955]

The reference should update automatically if you set update links in the tools/options/update remote references. If you open edit/links it will confirm the link exists.

an alternative could be;

place a hyperlink in the second sheet referencing the first sheet - this will update.

I do this where the hyperlink is across a LAN to another server drive folder.

 

[yalamo]

Mike1955, thanks, but...

I made a link as you suggested, and it shows in the edit/links box. However, it still will not show the data in the first file when it is closed.

I made a hyperlink as you suggested, but pressing on the hyperlink opens up the first file, which is what I am trying not to do, if possible.

 

[mike1955]

do you get a message box on open asking to update?

I have just created two workbooks, wb1 and wb2, entered a value in a cell in wb2 and referenced it in wb1. saved them both then alterd the value in wb2 and again closed it. opened wb1 and get the update links dialog - ok then updates.

 

[mike1955]

It might be that you do not have the update links addin installed. see the help files answerwizard/search add-in and choose add-in programs for excel

 

[yalamo]

Mike 1955, thanks for all the help, however...

Yes, I get the message window about updating links, and the update links add-in is installed. I know very little about the inner workings of Excel, but this problem may be insoluble because of the way Ecel works with closed files.

You may not have noticed that I said in my original post that I have an indirect reference to the first file in the second file. Ordinarily, if you directly refer to a cell from one Excel file to another, it doesn't matter if the referred-to file is closed, since Excel knows where that information is stored in the computer. But maybe when you have an indirect reference, the referred to file must do some additional calculation, which it can't, since it's closed.

If anyone can disprove this theory, please do so. And if anyone can tell me how to refer to and get information from a dynamic range of cells in an Excel file that's closed, then let me know.

 

[Gavona]

Certainly Excel97 gives me the same problem.
You can't use a dynamic rangname in FirstFile 'cos that won't work when the book is closed. However you could use a rangename in FirstFile and develop a routine to ensure that it updates everytime you add data or everytime you save FirstFile.
If you are happy with a bit of code:

Range("MyRange").CurrentRegion.name=Myrange

Use a close or change event to trigger this perhaps?

Thanks,

Gavin

 

[yalamo]

Gavin, thanks. Your idea for using some code to update the range, and triggering it with a close event deserves a star.

 

[Gavona]

Thanks, been somewhere similar myself. Just spotted: You will need some quotes around MyRange after the equals sign.

Thanks,

Gavin

 

[mike1955]

My instances update ranges from worksheets that are on a different server across a LAN. I just build the path and range into the cell using VBA, update the cell and then copy and paste as a vaqlue to overwrite the link. Maybe this is not remote enough to match your problem. Can add more to this if you think it is similar.

Mike

 

[yalamo]

Mike1955, I don't quite understand what you're doing, so I can't say if it applies to my problem or not. However, I implemented Gavona's suggestion, and it worked just fine.

 

[macropod]

hi Yalamo,

Your original formula was failing because of the INDIRECT function, which doesn't work with references to closed workbooks.

Is there a reason you can't use a lookup that references the whole column in your reference workbook, as in:
=VLOOKUP(A1,'D:\Spreadsheets\[FirstFile]Sheet1'!$A:$J,5)

Cheers

 

[yalamo]

Macropod, thanks. I said in one of my previous posts that I suspected that Excel can't perform an INDIRECT operation on a closed file.

I can't use a whole column because of the way the first file is built (it was written long before I wrote the second file). There are data rows below the block of cells I'm referring to, and they would confuse the lookup function if they were included in the cell range.

In any case, like I said, Gavona"s idea works just fine.