Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 25th 08, 09:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 102
Default Cell names = sheet names

The following formula works great if you want the cell name to be the current
sheet.

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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


  #2   Report Post  
Old January 25th 08, 09:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default Cell names = sheet names

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
www.stfx.ca/people/bliengme
remove caps from email

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

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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



  #3   Report Post  
Old January 25th 08, 09:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 102
Default Cell names = sheet names

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
www.stfx.ca/people/bliengme
remove caps from email

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

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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




  #4   Report Post  
Old January 30th 08, 09:07 PM posted to microsoft.public.excel.worksheet.functions
DC DC is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 42
Default Cell names = sheet names

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

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

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

Thanks,
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
www.stfx.ca/people/bliengme
remove caps from email

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

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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




  #5   Report Post  
Old January 30th 08, 09:22 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default Cell names = sheet names

Instead of looking for the closing square bracket, look for the full stop
before the extension:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH(".",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
--
David Biddulph

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

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

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

Thanks,
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
www.stfx.ca/people/bliengme
remove caps from email

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

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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








  #6   Report Post  
Old January 30th 08, 09:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Cell names = sheet names

I'd use 2 cells.

Your formula in A1 and this in B1:
=LEFT(A1,LEN(A1)-4)

But you could make a giant formula if you wanted. Just replace A1 with that
longggggggg formula that you posted.



dc wrote:

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

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

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

Thanks,
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
www.stfx.ca/people/bliengme
remove caps from email

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

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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
  #7   Report Post  
Old January 30th 08, 10:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Cell names = sheet names

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

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.

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

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

Thanks,
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
www.stfx.ca/people/bliengme
remove caps from email

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

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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
  #8   Report Post  
Old January 30th 08, 10:25 PM posted to microsoft.public.excel.worksheet.functions
DC DC is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 42
Default Cell names = sheet names

Thanks a lot for the help! I spent quite a while looking for an explanation
in the help file, but didn't find it. It look like both methods will work
and I really appreciate it.

Thanks!


"Dave Peterson" wrote:

I'd use 2 cells.

Your formula in A1 and this in B1:
=LEFT(A1,LEN(A1)-4)

But you could make a giant formula if you wanted. Just replace A1 with that
longggggggg formula that you posted.



dc wrote:

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

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

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

Thanks,
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
www.stfx.ca/people/bliengme
remove caps from email

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

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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

  #9   Report Post  
Old February 8th 08, 04:28 PM posted to microsoft.public.excel.worksheet.functions
DC DC is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 42
Default Cell names = sheet names

Dave,

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

Thanks,
Dave C

"Dave Peterson" wrote:

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

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.

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

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

Thanks,
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
www.stfx.ca/people/bliengme
remove caps from email

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

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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

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

Maybe

application.recalculate

maybe

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

I didn't test either.

dc wrote:

Dave,

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

Thanks,
Dave C

"Dave Peterson" wrote:

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

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.

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

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

Thanks,
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
www.stfx.ca/people/bliengme
remove caps from email

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

=MID(CELL("filename",a1),SEARCH("]",CELL("filename",a1))+1,1024)

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
list sheet names vertically below the active cell - need macro. Eddy Stan Excel Worksheet Functions 2 September 28th 07 07:48 PM
Sheet names linking to cell Tom Excel Discussion (Misc queries) 4 September 27th 07 09:09 AM
How can I make the names for Sheet tabs a reference to a cell? PDS Excel Discussion (Misc queries) 2 May 5th 06 11:20 PM
Concatenating cell values to create sheet names XLDabbler Excel Discussion (Misc queries) 3 August 29th 05 09:55 PM
Sheet Names and Cell References Reed Excel Worksheet Functions 1 January 19th 05 11:17 PM


All times are GMT +1. The time now is 09:09 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017