Referencing Closed Excel workbook

[scribbler99]

I have an excle spreadsheet that I need to construct a vlookup on and started searching the web for ways to do this and so far have only been able to get to the point of retrieving cell information from an workbook/worksheet that is open using the INDIRECT function. Obviously any help would be appreciated.

My cells contain the following information;
Cell B20 = test daily orders.xls
Cell B21 = DesMor-WAKE
Cell B22 = B3

The formaula I'm using at the moment is

=INDIRECT("'[" & B20 & "]" & B21 & "'!" & B22)

This returns the expected value which is text to me. but I thought that I would only need to replace the Cell B20 with the full path and the name of the workbook and of course this doesn't work.

 

[NWBeaver]

I'm not sure exactly what you are trying to do.

Are you just trying to make one spreadsheet read data from another, without opening both of them?

You can simply paste a link to a cell or range of cells in spreadsheet "A' into spreadsheet "B", and then just update the link when spreadsheet "B" is opened.

 

[scribbler99]

Firstly thanks for the reply NWBeaver

I have 1 closed workbook "testdailyorders.xls" that has a number of tabs.

In my open Workbook(despatching.xls) I have a cell that contains the name of the tab in the closed workbook "test daily orders.xls" and each time I change this cell I wanted it to Vlookup information on the corresponing tab within the closed workbook.

I have just started off (without using vlookup) to see if I can retrieve the value from a single cell with the "testdailyorders.xls" workbook open (just to minimise any errors) as I presumed if I could do that then using vlookup would be my next step. This works fine using the INDIRECT function but I cannot figure out how to get it to do the same when the "testdailyorders.xls" file is closed.

 

[Gavona]

Assuming the workbooks are: TestA and Test B

With both books open create a formula in TestA that references a cell/range in Test B. Don't use indirect just a straightforward formula.

Save Test B and then close it.
Observe the formula.
Copy and paste to a new cell the formula without the = at the start. That is what you need to recreate with your indirect formula. (cell C3 in my example below)

Take it in steps.
Use a separate cell to construct the address. Then refer to this with the indirect function. In your example this might look like this:

C3: = "'[" & B20 & "]" & B21 & "'!" & B22
D3: = indirect(C3)

Once you have it working you can miss out some of the steps but this really helps with understanding what is going on.



Gavin

 

[scribbler99]

I will try using your reply Gavin but from tests I've done this referencing won't on a closed Excel workbook will it ?

 

[Gavona]

Sorry!

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

Vlookup will work with closed workbooks but is much quicker, especially with multpile vlookups if the linked file is open (Edit, Links, Open Source)

Why do you need Indirect? Maybe explain a bit more so an alternative approach can be suggested.



Gavin

 

[scribbler99]

Sorry gavin but I thought that Vlookup wouldn't work on a closed workbook so searched the net for answers and only found the INDIRECT function

I needed to have a cell say cell "A1" in my open workbook "despatching.xls" contain a variable that I could use to vlookup information within the corresponding tab in the closed workbook "testdailyorders.xls"

I hope this makes sense

If not I could post you examples of the two workbooks.

Colin

 

[Gavona]

I would advise you to use named ranges in "testdailyorders.xls" for your lookup ranges.

What you are trying to do is, I believe, not readily supported by Excel. Maybe there is a better way of achieving your business objectives.

Sticking with the challenge you have presented:
In "despatching.xls" you could have multiple formulae (in an additional, possibly hidden, sheet) to perform the vlookup to each of your worksheets. Each cell containing the results would be a named range with a name identical to one of the possible values in cell "A1" in your open workbook "despatching.xls".

Then in the main sheet in "despatching.xls" you could use =indirect(A1) to get the result that you want. This would work as it would refer to named ranges in the same workbook.

Gavin