You can use the INDIRECT function to build up a reference in the
manner you suggest. However, this will only work with open workbooks.

Hope this helps.

Pete

On May 2, 4:06 pm, Jason wrote:
Hi All,

I'm fairly new to Excel and am looking for a way to insert the
Worksheet Name into a cell as part of a function.

I have a cell that contains this:

=COUNT('C:\[Refunds 2007-08.xls]Apr'!$A:$A)

And the following function that returns the Worksheet Name:

Function SheetName(rAnyCell)
Application.Volatile
SheetName = rAnyCell.Parent.Name
End Function

What I want to do is replace the "2007-08" bit in the COUNT function
with my own function so that the correct filename to count is
dynamically generated from the worksheet name.

Anyone know how to do this? I'm sure it's quite simple but it's got me
stumped!!

Any help would be much appreciated :D

Regards,

Jason