Use cells for network paremeters in formulas

[Marcster]

Hi All,

In cell C27 I have the following formula:

=SUMPRODUCT(--('\\SomeServer\SomeFolder\AnotherFolder\[ExcelFile.xls]TabName'!W$12:$W65536=$A$20),--('\\SomeServer\SomeFolder\AnotherFolder\[ExcelFile.xls]TabName'!K$12:$K$65536=$D30),--('\\SomeServer\SomeFolder\AnotherFolder\[ExcelFile.xls]TabName'!$W$12:$W$65536=$A$20))

In cell E27 I have the following formula:

=SUMPRODUCT(--('\\SomeServer\SomeFolder\AnotherFolder\[ExcelFile.xls]TabName''!W$12:$65536=$A$20),--('\\SomeServer\SomeFolder\AnotherFolder\[ExcelFile.xls]TabName'!K$12:$K$65536=$D27),--('\\SomeServer\SomeFolder\AnotherFolder\[ExcelFile.xls]TabName'!$E$12:$E$65536))

How do I change the
"'\\SomeServer\SomeFolder\AnotherFolder\[ExcelFile.xls]TabName'"
part of the formulas to use refs held in the following cells:


Cell A1: \\SomeServer\SomeFolder\AnotherFolder\ (file://someserver/SomeFolder/AnotherFolder/)
Cell A2: [ExcelFile.xls]
Cell A3: TabName

This is so I can change the clls A1, A2 and A3 and the formulas values should
update accordingly when the sheet is calculated.

Thanks,

[Sagy]

You can use the Indirect function.

Create the address that you want by concatenating the three cells = "'" & A1 & A2 & A3 & "'!" & ... ("..." is the specific range you want) so you end up with the string:
"'\\SomeServer\SomeFolder\AnotherFolder\[ExcelFile.xls]TabName'!..." in a cell and then do:
=SUMPRODUCT(INDIRECT(Ax),INDIRECT(Ay))