Make part of address indirect and keep rest of address relative?

I want to control the file and sheet name of a reference in a cell formula with indirect or something equivalent but have the rest of the reference be relative and possible to copy to other cells, how can I achieve this?

I'm looking for something like this:

=A1*varFileName&varSheetName&A1

that I can copy downwards with relative references like:

=A1*varFileName&varSheetName&A1
=B1*varFileName&varSheetName&B1
=C1*varFileName&varSheetName&C1

Where I would want the second factor to refer to A1, B1 and C1 in the other sheet

Hi,

Presumably you are wanting to reference another workbook, otherwise there's be no need to specify the file name

This will work if the other file is open in memory

Formula:

=OFFSET(INDIRECT("["&FileName&"]"&SheetName&"!$A$1"),0,ROW())

where FileName & SheetName are two range names that refer to cells that contain the actual file name.

Unfortunately this will not work when the other workbook is closed since Indirect won;t work with closed workbooks. A work around would be to have a macro that automatically opened the other workbook when this workbook is opened, and closes the other workbook when you close this one.