Hi, bncoxuk!
If I were you I'd give a look to these files:
https://dl.dropbox.com/u/60558749/How%20to%20read%20data%20from%20the%20workbook%20selectively_%20Source%20%28for%20bncoxuk%20at%20chandoo.org%29.xlsx
https://dl.dropbox.com/u/60558749/How%20to%20read%20data%20from%20the%20workbook%20selectively_%20Target%20%28for%20bncoxuk%20at%20chandoo.org%29.xlsm
The first one is an example source workbook with 1000x1000 cells.
The second one is the actual workbook, and it uses a little piece of VBA code as follows:
-----
[pre]
Code:
Option Explicit
Function FindAddressValue(psWorkbook As String, psWorksheet As String, _
psValue As String) As String
' constants
' declarations
Dim rng As Range, A As String
' start
' process
Set rng = Workbooks(psWorkbook).Worksheets(psWorksheet).Cells.Find( _
What:=psValue, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
A = ""
Else
A = rng.Address(False, False, xlA1)
End If
' end
FindAddressValue = A
End Function
[/pre]
-----
In B1:B3 you have 3 named ranges SourceWorkbook, SourceWorksheet, SourceValue.
In B5 you have this formula to retrieve the searched value:
=FindAddressValue(SourceWorkbook;SourcetWorksheet;SourceValue)
In C5:C14, you have this formula that retrieves the required data:
=INDICE(SourceData;FILA(INDIRECTO(B$5))+FILA()-FILA(B$5);COLUMNA(INDIRECTO(B$5))) -----> in english: =INDEX(SourceData,ROW(INDIRECT(B$5))+ROW()-ROW(B$5),COLUMN(INDIRECT(B$5)))
You have an additional named range, SourceData, that points to A1:ALL1000 (1000x1000 cells) of your source workbook.
I know you preferred to not use VBA but in this way it's very simple to achieve your goal. If you don't feel comfortable with it, I'm afraid you'll have to wait for other non-VBA solutions.
Regards!
