Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default access cell in other workbook

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 751
Default access cell in other workbook

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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default access cell in other workbook

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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 751
Default access cell in other workbook

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:


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to access a cell value in a different workbook. HELP! [email protected] Excel Worksheet Functions 2 October 18th 06 09:32 PM
Reading Data from another workbook... depending on variable in a cell? Rob Moyle Excel Discussion (Misc queries) 4 March 13th 06 05:21 PM
Protect Workbook vs Worksheet?? Dan B Excel Worksheet Functions 3 November 7th 05 10:02 PM
copying cell names Al Excel Discussion (Misc queries) 12 August 11th 05 03:01 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM


All times are GMT +1. The time now is 01:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"