Copy sheet name into cell

[ronoee]

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

 

[Mike]

=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255)

Try this

 

[Max]

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.

 

[ronoee]

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

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

 

[ronoee]

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 don’t 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.

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

 

[George Gee]

Just copy & paste the formula into any cell.
Do not change anything!

George Gee

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

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]

Try it without changing those "filename"'s.

Leave them As-Is.

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

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

 

[ronoee]

Hello Greorge Gee



"George Gee" skrev:

Just copy & paste the formula into any cell.
Do not change anything!

George Gee

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

:

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

 

[ronoee]

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

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

:

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

 

[ronoee]

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.

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

:

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]

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.

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.

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

:

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

 

[ronoee]

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

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

:

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

 

[ronoee]

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.

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

:

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

 

[ronoee]

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.

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

:

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

 

[ronoee]

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.

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

 

[Pete_UK]

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

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]

Glad you got it up and running !

 

[Ali]

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.

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.

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

 

[Max]

welcome, Ali.
Thanks for feeding back