ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Cell names = sheet names (https://www.excelbanter.com/excel-worksheet-functions/174489-cell-names-%3D-sheet-names.html)

Vince January 25th 08 09:24 PM

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


Bernard Liengme January 25th 08 09:49 PM

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




Vince January 25th 08 09:58 PM

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





DC January 30th 08 09:07 PM

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





David Biddulph[_2_] January 30th 08 09:22 PM

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







Dave Peterson January 30th 08 09:25 PM

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

Dave Peterson January 30th 08 10:24 PM

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

DC January 30th 08 10:25 PM

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


DC February 8th 08 04:28 PM

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


Dave Peterson February 8th 08 04:59 PM

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


All times are GMT +1. The time now is 02:16 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
ExcelBanter.com