Display data from a cell, of a sheet, of a book, whose file name is spelt out in a cell.

[Topher]

Display data from a cell, of a sheet, of a book, whose file name is spelt out in a cell. ...

Scenario,

B2 reads ORANGEJUICE.XLS
B3 READS LYRICS.XLS
B4 READS TRAVELINGINFO.XLS
B5 READS COLLECTION OF ESSAYS.XLS


How can I write a formula, in cell C2, that will allow me to display the data of cell "AL327,SHEET2,(book listed in B2)"?????

 

[hiker95]

Topher,


For the below to work will you need to download the free MOREFUNC add-in available here:
http://xcell05.free.fr/morefunc/english/


Sample worksheets that are closed:

Excel Workbook
	AL
327	Topher
Sheet2

Excel Workbook
	AL
327	hiker95
Sheet2

Excel Workbook
	AL
327	345.78
Sheet2

Excel Workbook
	AL
327	Essay for Topher: Display data from a cell, of a sheet, of a book, whose file name is spelt out in a cell.
Sheet2

Excel Workbook
	B	C
2	ORANGEJUICE.XLS	Topher
3	LYRICS.XLS	hiker95
4	TRAVELINGINFO.XLS	345.78
5	COLLECTION OF ESSAYS.XLS	Essay for Topher: Display data from a cell, of a sheet, of a book, whose file name is spelt out in a cell.
6
Sheet1

The Function in cell C2 copied down:

=INDIRECT.EXT("'C:\TestData\[" & B2 & "]Sheet2'!AL327")

 

[hiker95]

Topher,

Excel Workbook
	A	B	C
2	'C:\TestData\[	ORANGEJUICE.XLS	Topher
3	]Sheet2'!	LYRICS.XLS	hiker95
4	AL327	TRAVELINGINFO.XLS	345.78
5		COLLECTION OF ESSAYS.XLS	Essay for Topher: Display data from a cell, of a sheet, of a book, whose file name is spelt out in a cell.
6
Sheet1

The formula/function in cell C2 copied down:

=INDIRECT.EXT($A$2&B2&$A$3&$A$4)





The data in cell A2 begins with two ' characters:

''C:\TestData\[

 

[Topher]

Thanks a million!
I got all of it operating properly. life is good now, until the next issue arises of course