Can I use INDIRECT.EXT for this?

[Guest]

Here's the formula in question:

={INDEX('Z:\Mill Schedules Production\Mill Schedules
Archive\[20060918.xls]Mill 1'!$B$3:$Q$500,SMALL(IF('Z:\Mill Schedules
Production\Mill Schedules Archive\[20060918.xls]Mill
1'!$B$3:$B$500=$B$7,ROW('Z:\Mill Schedules Production\Mill Schedules
Archive\[20060918.xls]Mill 1'!$B$3:$B$500)-2),ROW(1:1)),2)}

Is it possible to replace the file name with a reference to a cell with the
file name in it using INDIRECT.EXT? Do I need a cell in 20060918.xls with a
CELL formula that holds the file name for INDORECT.EXT to reference? Any
help would be greatly appreciated...

Jay

 

[Guest]

yes.Indirect will let you use a list of file names as a reference to a file
(or anything actually) in a formula.So you can day have a drop down list of
all your files and when yoou select one your informaion is completed!

 

[Guest]

Forgive my density...

Does INDIRECT.EXT have to reference a cell in the 20060918.xls file that
contains a =CELL(...) formula with the filename?

yes.Indirect will let you use a list of file names as a reference to a file
(or anything actually) in a formula.So you can day have a drop down list of
all your files and when yoou select one your informaion is completed!
--
paul
(e-mail address removed)
remove nospam for email addy!

Here's the formula in question:

={INDEX('Z:\Mill Schedules Production\Mill Schedules
Archive\[20060918.xls]Mill 1'!$B$3:$Q$500,SMALL(IF('Z:\Mill Schedules
Production\Mill Schedules Archive\[20060918.xls]Mill
1'!$B$3:$B$500=$B$7,ROW('Z:\Mill Schedules Production\Mill Schedules
Archive\[20060918.xls]Mill 1'!$B$3:$B$500)-2),ROW(1:1)),2)}

Is it possible to replace the file name with a reference to a cell with the
file name in it using INDIRECT.EXT? Do I need a cell in 20060918.xls with a
CELL formula that holds the file name for INDORECT.EXT to reference? Any
help would be greatly appreciated...

Jay