I have an array in 'Workbook B'

'Workbook A' contains a cell, say A1, in which I've Calculated a variable
cell reference

How can I obtain the value held in the un-open 'Workbook B' from the cell
defined in 'Workbook A' A1?

Thank you

You can use INDIRECT, but it requires Workbook B to be open. Sort of
this solution you will need VBA.

=INDIRECT("'[Workbook B]Sheet1'!A1")

This will be updated as soon as Workbook B opens.

HTH
Kostis Vezerides


Glynn Taylor wrote:
I have an array in 'Workbook B'

'Workbook A' contains a cell, say A1, in which I've Calculated a variable
cell reference

How can I obtain the value held in the un-open 'Workbook B' from the cell
defined in 'Workbook A' A1?

Thank you

Thank you

I thought of this solution but was hoping it would be possible without
opening the other workbook.

Thanks once again for your response

"vezerid" wrote:

You can use INDIRECT, but it requires Workbook B to be open. Sort of
this solution you will need VBA.

=INDIRECT("'[Workbook B]Sheet1'!A1")

This will be updated as soon as Workbook B opens.

HTH
Kostis Vezerides


Glynn Taylor wrote:
I have an array in 'Workbook B'

'Workbook A' contains a cell, say A1, in which I've Calculated a variable
cell reference

How can I obtain the value held in the un-open 'Workbook B' from the cell
defined in 'Workbook A' A1?

Thank you

OK then, here is a VBA solution with a UDF:

Function RemoteRef(wbk As String, sht As String, rng As String)
Dim oApp As Excel.Application
Dim oWbk As Excel.Workbook
Dim oSht As Excel.Worksheet

Set oApp = CreateObject("Excel.application")
Set oWbk = oApp.Workbooks.Open(wbk)
Set oSht = oWbk.Sheets(sht)
RemoteRef = oSht.Range(rng)
End Function

The function can then be called like:

=remoteref("d:\vezerides\excel techniques\strange
sort.xls","sheet2","a1")

Does this help?
Kostis Vezerides


Glynn Taylor wrote:
Thank you

I thought of this solution but was hoping it would be possible without
opening the other workbook.

Thanks once again for your response

"vezerid" wrote: