Newbie question re Worksheet Name

[Jason]

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

Regards,

Jason

 

[Pete_UK]

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

 

[Guest]

The best way i found is to have both files open (at half size so you can see
both in excel) and when typing the formula into one spreadsheet, when you get
to the point where you need the name entered you can click on any cell in the
other file, this should give you the file name and cell number (for example
A2) just delete this back to give you just the database name. I'm not sure if
that is any use, but that's how i do it.