It's even worse than that.

The name of the sheet has to be the same as the workbook (without the
extension).

Worksheet name = Test
workbook name = Test.xls
means no []'s.

I'd use a couple of extra cells.

This will return that test.xls in your situation.

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"\","^^",LEN(A1)
-LEN(SUBSTITUTE(A1,"\","")))))

then
=left(cellwithformula,len(cellwithformula)-4)

=======
Depending on your filenames and worksheet names, you may want to use a few
cells--one to look for the []'s to see how the name is returned.

ps.

=cell("filename")
will evaluate to the workbook name that was active when excel recalculated.

=cell("filename",a1) will return that workbook/worksheet for the cell with the
formula.

Chris wrote:

Bob I figured out why Excel isn't using brackets when I use
cell("filename")..... If the workbook has only 1 sheet it will not use
brackets and only show you the path and book name. If the workbook has more
than 1 worksheet, it will display the path,book name, and sheet name.

This complicates things because I need to be able to extract the sheet name,
yet all my files only have 1 sheet........

"Chris" wrote:

I'm trying to reference the sheet name of the workbook I'm using.

When I type =Cell("filename") into a cell I get this,

C:\documents and settings\chris\desktop\test.xls

How do I extract just the word test(without the .xls)?

--

Dave Peterson