Cell names = sheet names

[Vince]

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]

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

 

[Vince]

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

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

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]

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.

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

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

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]

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

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.

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

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

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]

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.

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.

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

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

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]

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.

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.

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

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

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]

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!

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.

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.

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

:

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

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]

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

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.

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.

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

:

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

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]

Maybe

application.recalculate

maybe

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

I didn't test either.

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

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.

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.

:

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

:

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

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