| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| list sheet names vertically below the active cell - need macro. | Excel Worksheet Functions | |||
| Sheet names linking to cell | Excel Discussion (Misc queries) | |||
| How can I make the names for Sheet tabs a reference to a cell? | Excel Discussion (Misc queries) | |||
| Concatenating cell values to create sheet names | Excel Discussion (Misc queries) | |||
| Sheet Names and Cell References | Excel Worksheet Functions | |||
Linear Mode
