Marcster
02-05-2009, 11:14 AM
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,
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,