Refer to array in another file, when the file name is computed

[phillman5]

I download spreadsheets each week that I am comparing, (I am using Excel many as the download files are in excel, I might move to a database) I want to see if one company listed one week is listed the next. For example in file2.xls

MATCH(E7,[file1.xls]Sheet1!$E$7:$E$106,0)

will give the row number (offset from row 7) that the company name in cell E7 is listed the array E7:E106 in file1.xls. This works just fine. My quest is I don't want to be changing this formula every week everywhere in the file as the file numbers increase. I can "compute'' the filename and use that to get a cell,

If cell B4 contains the computed filename, then the formula

=INDIRECT(ADDRESS(5,1,1,TRUE,B4))

will give the value of cell A5 in the computed fllename's file, but how do I refer to an array like in the match formula above when the filename 'file1' is computed by a formula or is stored in a cell.

Second question,

The formula

=CELL("filename")

gives the filename of the current active file, NOT THE FILENAME OF THE FILE THE FORMULA RESIDES IN. How do I get this not to change if I click on another file? (I don't want to store the filename as a value)

 

[Bob Greenblatt]

I download spreadsheets each week that I am comparing, (I am using Excel many
as the download files are in excel, I might move to a database) I want to see
if one company listed one week is listed the next. For example in file2.xls

MATCH(E7,[file1.xls]Sheet1!$E$7:$E$106,0)

will give the row number (offset from row 7) that the company name in cell E7
is listed the array E7:E106 in file1.xls. This works just fine. My quest is I
don't want to be changing this formula every week everywhere in the file as
the file numbers increase. I can "compute'' the filename and use that to get a
cell,

If cell B4 contains the computed filename, then the formula

=INDIRECT(ADDRESS(5,1,1,TRUE,B4))

This should work:
=match(e7,indirect("'"&b4&"'!sheet1!$E$7:$E$106),0)

will give the value of cell A5 in the computed fllename's file, but how do I
refer to an array like in the match formula above when the filename 'file1' is
computed by a formula or is stored in a cell.

Second question,

The formula

=CELL("filename")

gives the filename of the current active file, NOT THE FILENAME OF THE FILE
THE FORMULA RESIDES IN. How do I get this not to change if I click on another
file? (I don't want to store the filename as a value)

Cell, gives the file name of the file containing the reference. Try changing
to: =cell("filename",a1) or changing the reference to another workbook.

 

[phillman5]

this seems to work, however if cell b4 contains the file name the formula should be:

=match(e7,indirect("['"&b4&"]Sheet1!$E$7:$E$106"),0)

 

[JE McGimpsey]

this seems to work, however if cell b4 contains the file name the formula
should be:

=match(e7,indirect("['"&b4&"]Sheet1!$E$7:$E$106"),0)

Did it really work? It *should* give a #REF! error. Perhaps a small
modification:

=MATCH(E7,INDIRECT("'["&B4&"]Sheet1'!$E$7:$E$106"),0)