Automatic Function Name

[Dave]

Hi,

I have a function eg

=SUM(['[fileA.xls]YYY'!C1:C2])

Is there any way I can set this up so that YYY references the name of the
current worksheet?

This is so I can use "Select all sheets" in file B to enter this formula in
every sheet.

( File A contains the same sheet names as file B)

Thanks

 

[Bob Phillips]

Dave,

It's a bit long-winded, but try this

=SUM(INDIRECT("'[fileA.xls]"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A
1))-FIND("]",CELL("filename",A1)))&"'!C1:C2"))

The workbook that you put this formula in must have been saved at least once
for the CELL function to work.

 

[Tom Ogilvy]

If the file where you are working is FileA.xls (your are not refering to
another file) then just

=Sum(C1:C2)

would refer to the sheet on which the formula is located.

Just a thought.

Regards,
Tom Ogilvy

Dave,

It's a bit long-winded, but try this

=SUM(INDIRECT("'[fileA.xls]"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A
1))-FIND("]",CELL("filename",A1)))&"'!C1:C2"))

The workbook that you put this formula in must have been saved at least once
for the CELL function to work.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

Hi,

I have a function eg

=SUM(['[fileA.xls]YYY'!C1:C2])

Is there any way I can set this up so that YYY references the name of the
current worksheet?

This is so I can use "Select all sheets" in file B to enter this formula in
every sheet.

( File A contains the same sheet names as file B)

Thanks