3-d References

[vecia]

Hello,

I need to know if I can create a 3-d reference to another worksheet b
specifying the workbook name in a cell in the original workbook?

e.g.

A1 contains 900542
B1 contains 600576
C1 contains a number

D1 contains the formula:

=$C1-(VLOOKUP($A1,[Book2]Sheet1!$A$1:$F$2,2))

but I want the [Book2] to be [600572] i.e. whatever is in column B o
the row you are in.

Can anyone help?

 

[Frank Kabel]

Hi
if the other workbook is open try

=$C1-(VLOOKUP($A1,INDIRECT("'[" & B1 & "]Sheet1'!
$A$1:$F$2"),2))

Note: This dows only work if the other workbook 8named in
cell b1) is open!

 

[vecia]

That's great - thanks!

Is there any way I could get this sort of thing to work without th
other spreadsheet being open (I can see that being the next thing I a
asked to do)

 

[Frank Kabel]

Hi
there're some alternatives for accessing closed workbooks. But they
won't work in this case as you would use them to return a range (and
not only a single cell). A range does not exist in a closed workbook.
But take a look yourself at the following site:
http://tinyurl.com/2c62u

 

[vecia]

Thanks Frank - I am still digesting this information but I think it'
sorted my problem out.: