|
Copy sheet name into cell
I would like to copy the sheet name into a cell in the same sheet in a way
that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255)
Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
Try this technique from a post by Harlan ..
First, save the book (this is a pre-requisite) Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in formulas. It will auto-extract the sheetname implicitly. Test it out. In any sheet in the book, in any cell, just enter: =WSN to return the sheetname in the cell. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing , with ; and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I dont seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
Hello Max
I tried out your proposal: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32) In the function Insert Name Define as you described. I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing , with ; and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I dont seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Max" skrev: Try this technique from a post by Harlan .. First, save the book (this is a pre-requisite) Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in formulas. It will auto-extract the sheetname implicitly. Test it out. In any sheet in the book, in any cell, just enter: =WSN to return the sheetname in the cell. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
Just copy & paste the formula into any cell.
Do not change anything! George Gee "ronoee" wrote in message ... Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing "," with ";" and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I don't seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
Try it without changing those "filename"'s.
Leave them As-Is. ronoee wrote: Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing , with ; and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I dont seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you -- Dave Peterson |
Copy sheet name into cell
Hello Greorge Gee
"George Gee" skrev: Just copy & paste the formula into any cell. Do not change anything! George Gee "ronoee" wrote in message ... Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing "," with ";" and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I don't seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
Hello GΓ©orge Gee
I started up with this, but it only shows the same text as it is pasted. If I paste it in formula line I get a error message. "George Gee" skrev: Just copy & paste the formula into any cell. Do not change anything! George Gee "ronoee" wrote in message ... Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing "," with ";" and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I don't seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
Hello Dave
I started up with this, but it only shows the same text as it is pasted. If I paste it in formula line I get a error message. "Dave Peterson" skrev: Try it without changing those "filename"'s. Leave them As-Is. ronoee wrote: Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing Γ’¬Ε,Γ’¬Β with Γ’¬Ε;Γ’¬Β and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I donΓ’¬’t seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you -- Dave Peterson |
Copy sheet name into cell
Make sure that the cell is formated as General (or anything but Text). Then
paste that formula directly into the formula bar. And make sure you don't have any leading spaces before that initial equal sign. And this formula will return an error if the workbook isn't saved. ronoee wrote: Hello Dave I started up with this, but it only shows the same text as it is pasted. If I paste it in formula line I get a error message. "Dave Peterson" skrev: Try it without changing those "filename"'s. Leave them As-Is. ronoee wrote: Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing Γ’¬Ε,Γ’¬Β with Γ’¬Ε;Γ’¬Β and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I donΓ’¬’t seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you -- Dave Peterson -- Dave Peterson |
Copy sheet name into cell
Hello George
I got a Danish version and I tried to modify syntax further and now I works Danish syntax should bee: =MIDT(CELLE("filename";A1); FIND("]"; CELLE("filename"; A1))+ 1; 255) Thank you for helping. "George Gee" skrev: Just copy & paste the formula into any cell. Do not change anything! George Gee "ronoee" wrote in message ... Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing "," with ";" and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I don't seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
Hello Dave
I got a Danish version and I tried to modify syntax further and now I works Danish syntax should bee: =MIDT(CELLE("filename";A1); FIND("]"; CELLE("filename"; A1))+ 1; 255) Thank you for helping. "Dave Peterson" skrev: Try it without changing those "filename"'s. Leave them As-Is. ronoee wrote: Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing Γ’¬Ε,Γ’¬Β with Γ’¬Ε;Γ’¬Β and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I donΓ’¬’t seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you -- Dave Peterson |
Copy sheet name into cell
Hello Dave I got a Danish version and I tried to modify syntax further and now I works Danish syntax should bee: =MIDT(CELLE("filename";A1); FIND("]"; CELLE("filename"; A1))+ 1; 255) "Dave Peterson" skrev: Make sure that the cell is formated as General (or anything but Text). Then paste that formula directly into the formula bar. And make sure you don't have any leading spaces before that initial equal sign. And this formula will return an error if the workbook isn't saved. ronoee wrote: Hello Dave I started up with this, but it only shows the same text as it is pasted. If I paste it in formula line I get a error message. "Dave Peterson" skrev: Try it without changing those "filename"'s. Leave them As-Is. ronoee wrote: Hello Mike I tried out your proposal: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) I saved the file and changed filename in formula to actual filename. I got failure in formula and tried with changing Γ’Ò¬à ,ΓΒ’Γ’Β¬ΓΒ with Γ’Ò¬à ;ΓΒ’Γ’Β¬ΓΒ and formula was accepted, but I only received #NAVN? (Danish version) in English #NAME? returned in cell. Somehow I donΓ’Ò¬Ò’t seem to understand the syntax for the formula. If you cold help me out further I will appreciate it. "Mike" skrev: =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) Try this "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you -- Dave Peterson -- Dave Peterson |
Copy sheet name into cell
Hello Max
Modified syntax further and now it works fine. I got a Danish version and that was the trouble. Danish syntax should bee: =MIDT(CELLE("Filename";INDIREKTE("A1"));FIND("]";CELLE("Filename";INDIREKTE("A1")))+1;32) Thank you for helping. "Max" skrev: Try this technique from a post by Harlan .. First, save the book (this is a pre-requisite) Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in formulas. It will auto-extract the sheetname implicitly. Test it out. In any sheet in the book, in any cell, just enter: =WSN to return the sheetname in the cell. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
Many European countries seem to use a semicolon rather than a comma in
formulae to separate the syntactic elements - you should bear that in mind in any future formulae which you get from the English newsgroups (as well as needing to translate the names of the functions). Pete On Jan 27, 5:43 pm, ronoee wrote: Hello Max Modified syntax further and now it works fine. I got a Danish version and that was the trouble. Danish syntax should bee: =MIDT(CELLE("Filename";INDIREKTE("A1"));FIND("]";CELLE("Filename";INDIREKTE*("A1")))+1;32) Thank you for helping. "Max" skrev: Try this technique from a post by Harlan .. First, save the book (this is a pre-requisite) Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1"*)))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in formulas. It will auto-extract the sheetname implicitly. Test it out. In any sheet in the book, in any cell, just enter: =WSN to return the sheetname in the cell. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you- Hide quoted text -- Show quoted text - |
Copy sheet name into cell
Glad you got it up and running !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ronoee" wrote in message ... Hello Max Modified syntax further and now it works fine. I got a Danish version and that was the trouble. Danish syntax should bee: =MIDT(CELLE("Filename";INDIREKTE("A1"));FIND("]";CELLE("Filename";INDIREKTE("A1")))+1;32) Thank you for helping. |
Copy sheet name into cell
This formula worked better for me because the earlier formula changed all the
cells that used that formula in the workbook regardless of what worksheet they were in. "Max" wrote: Try this technique from a post by Harlan .. First, save the book (this is a pre-requisite) Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in formulas. It will auto-extract the sheetname implicitly. Test it out. In any sheet in the book, in any cell, just enter: =WSN to return the sheetname in the cell. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ronoee" wrote: I would like to copy the sheet name into a cell in the same sheet in a way that if the sheet name is changed the content of the cell is changed too. Is there a function to do this? Thank you |
Copy sheet name into cell
welcome, Ali.
Thanks for feeding back -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ali" wrote in message ... This formula worked better for me because the earlier formula changed all the cells that used that formula in the workbook regardless of what worksheet they were in. |
| All times are GMT +1. The time now is 01:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com