Can I use INDIRECT.EXT for this?

J

Jay Nichols

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
 
P

paul

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!
 
J

Jay Nichols

Forgive my density...

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

paul said:
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!



Jay Nichols said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top