Automatic Function Name

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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.
 
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

Bob Phillips said:
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


Dave said:
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
Click to expand...
Click to expand...
 
Back
Top