View Single Post
  #10   Report Post  
Old February 8th 08, 03:59 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson Dave Peterson is offline
external usenet poster
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Cell names = sheet names




with worksheets("somesheetnamehere")
.cells.replace what:="=", replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
end with

I didn't test either.

dc wrote:


If I do a "saveas" on my Excel file,... what's the easiest way to get the
cell to update the filename? If I double click on the cell, it will update,
when I exit the cell, but not on its own.

Any way to do this without getting to fancy? (Limited resources here.)

Dave C

"Dave Peterson" wrote:

ps. The formula I posted assumes that the extension is 3 characters plus a

The formula David posted assumes that there are no dots in the filename--except
for right before the extension.

dc wrote:

Sorry to intrude here, but this is somewhat related. I have the following
formula to place the file name in a cell.


Is there a switch so that the file extension is not shown in the cell?

Dave C.

"Vince" wrote:

Thank you! Sometimes the simpe work arounds are the ones that are thought of
last! That works just fine.

"Bernard Liengme" wrote:

A simple workaround
Put that formula in some out of the way cell (IV1) of Sheet3
In Sheet1 B3 enter =Sheet3!IV1
Remember the CELL function need the file to have saved at least once
best wishes
Bernard V Liengme
Microsoft Excel MVP
remove caps from email

"Vince" wrote in message
The following formula works great if you want the cell name to be the


Is there a way to reference a different sheet? For example is there a way
for Sheet 1 Cell B3 to show the name of Sheet 3?

Thanks for you help


Dave Peterson


Dave Peterson