Worksheet Names

[Cameron]

Is there a way to get the Worksheet name/s into a cell without vba code??

 

[Frank Kabel]

Hi Cameron
try the following formula for the sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

Frank

 

[Bob Phillips]

Frank,

That's the seventeenth time you've posted this reply this week, but you've
suddenly dropped all the details about path, file, etc. You must be getting
tired<vbg>.

Bob

Hi Cameron
try the following formula for the sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

Frank

Is there a way to get the Worksheet name/s into a cell without vba
code??

 

[Frank Kabel]

Bob
I've to templates for this one. This time my search got this one so I
just posted it
Thinking about it, a FAQ for this NG would be a good idea
Frank

Frank,

That's the seventeenth time you've posted this reply this week, but
you've suddenly dropped all the details about path, file, etc. You
must be getting tired<vbg>.

Bob

Hi Cameron
try the following formula for the sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

Frank

Is there a way to get the Worksheet name/s into a cell without vba
code??

 

[Frank Kabel]

Hi Cameron,
don't repalce "filename" with your workbook name
just simply enter CELL("filename"...)
so you don't have to change anything in the formulas
Frank

will do

Frank,

Thank-you very much for your reply in relation to my query.

I copied your solution and made the appropriate changes ... (I think)

=RIGHT(CELL("C:\Documents and Settings\Cameron\Desktop\PowerUp
Costings.xls",A1),LEN(CELL("C:\Documents and
Settings\Cameron\Desktop\PowerUp
Costings.xls",A1))-FIND("]",CELL("C:\Documents and
Settings\Cameron\Desktop\PowerUp Costings.xls",A1),1))

However the cell A1 comes up #VALUE!.

Any chance you might provide some more insight to your solution?

The attached JPG is indicative of my problem.

I'd like to get the Worksheet Names of any worksheet and have it
listed on the "Control Page". without coding it.

Thanks again,
Cam

Hi Cameron
try the following formula for the sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

Frank

Is there a way to get the Worksheet name/s into a cell without vba
code??

 

[Cameron]

Hi Frank,
Sorry my misunderstanding.

OK, I now see how that formula works for the instance that you've exampled.
But how do you manipulate that formula to reflect the names of the other
worksheets back to the initial worksheet?? This is where I'm now getting
stuck.

I've copied the formula across range(A3:A6) and I've been trying to get A3
to reflect the 2nd worksheet, A4 to reflect the 3rd, etc. Hope I'm making
sence.

Again many thanks for the assistance thus far.

Cam.

Hi Cameron,
don't repalce "filename" with your workbook name
just simply enter CELL("filename"...)
so you don't have to change anything in the formulas
Frank

will do

Frank,

Thank-you very much for your reply in relation to my query.

I copied your solution and made the appropriate changes ... (I think)

=RIGHT(CELL("C:\Documents and Settings\Cameron\Desktop\PowerUp
Costings.xls",A1),LEN(CELL("C:\Documents and
Settings\Cameron\Desktop\PowerUp
Costings.xls",A1))-FIND("]",CELL("C:\Documents and
Settings\Cameron\Desktop\PowerUp Costings.xls",A1),1))

However the cell A1 comes up #VALUE!.

Any chance you might provide some more insight to your solution?

The attached JPG is indicative of my problem.

I'd like to get the Worksheet Names of any worksheet and have it
listed on the "Control Page". without coding it.

Thanks again,
Cam

Hi Cameron
try the following formula for the sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

Frank

Cameron wrote:
Is there a way to get the Worksheet name/s into a cell without vba
code??

 

[Frank Kabel]

Hi Cameron
one way: you can use the second parameter of the CELL function as
reference for each worksheet. e.g.
=CELL(.....,'sheet1'!A1)

another way if your are trying to build a table of contents you may
have a look at
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Frank

Hi Frank,
Sorry my misunderstanding.

OK, I now see how that formula works for the instance that you've
exampled. But how do you manipulate that formula to reflect the names
of the other worksheets back to the initial worksheet?? This is
where I'm now getting stuck.

I've copied the formula across range(A3:A6) and I've been trying to
get A3 to reflect the 2nd worksheet, A4 to reflect the 3rd, etc.
Hope I'm making sence.

Again many thanks for the assistance thus far.

Cam.

Hi Cameron,
don't repalce "filename" with your workbook name
just simply enter CELL("filename"...)
so you don't have to change anything in the formulas
Frank

will do

Frank,

Thank-you very much for your reply in relation to my query.

I copied your solution and made the appropriate changes ... (I
think)

=RIGHT(CELL("C:\Documents and Settings\Cameron\Desktop\PowerUp
Costings.xls",A1),LEN(CELL("C:\Documents and
Settings\Cameron\Desktop\PowerUp
Costings.xls",A1))-FIND("]",CELL("C:\Documents and
Settings\Cameron\Desktop\PowerUp Costings.xls",A1),1))

However the cell A1 comes up #VALUE!.

Any chance you might provide some more insight to your solution?

The attached JPG is indicative of my problem.

I'd like to get the Worksheet Names of any worksheet and have it
listed on the "Control Page". without coding it.

Thanks again,
Cam


Hi Cameron
try the following formula for the sheet name

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file

name",A1),1))

Frank

Cameron wrote:
Is there a way to get the Worksheet name/s into a cell without vba
code??