help on indirect()

[Guest]

I am trying to extract from closed workbook (diff books saved on daily basis)
file is in the path:
'C:\D Backup\HQCOLN\SGP REPORTS\CFO\MISC\COLLECTION REPORT\COLNDATA\
file name : cms0111.xls
data in : sheet1
cell to refer : F2
I am keeping the path at B1
file name & rowno to refer at b4 & c4
- in b4 "cms0111" and in c4 value 2
now formula at : d4
=INDIRECT(TRIM($B$1&"["&B4&".XLS]Sheet1'!F"&C4))
I would be copying this formula down the rows to get row data from remote file
I would be copying this formula sideways for column data (ofcourse I will
change column ref "F" to "M", "V" and other columns as required.

the below formula gives error "REF"
=INDIRECT(TRIM($B$1&"["&B4&".XLS]Sheet1'!F"&C4))

any suggestion please and advance thanks.

 

[Arvi Laanemets]

Hi

INDIRECT works only with opened source file.