using the Cell function

[WT]

I am able to aquire the name of a page with the following equasion.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255).
And I have found that I can aquire any page name using a label rather than a
cell address which gives the page title where the select cells of the label
exist.
(Where John is the label address of cell A1:A10: on sheet2)
=MID(CELL("filename",John),FIND("]",CELL("filename",John))+1,255)
This returns "Sheet2"
I also know that you can obtain the contents of a cell using this equasion.
=CELL("Contents",A1)

Finally the question

So why can I not nest the "CELL" function to obtain the page name for a
label that is contained in a cell??
For example,
If I have a label say "John" that points to the cells A1:A10 on Sheet2 and I
want to use the page title in another equasion on Sheet1 and it's possible
that the page title can change over time. I want to be able to have the
title no matter what it might change to. So I plased the word "John" in a
cell A1 to use as a reference.
So I tried to nest the "CELL" function in this equasion but it consistantly
gives me an error although I can not find any error in the syntax. What is
wrong with this and why won't it allow nexting of the "CELL" function??

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

 

[Luke M]

If your wanting cell contents to be used as a cell reference, you need to use
the INDIRECT function:

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

Curious though, why all the work to find a certain sheet/cell, when you're
wanting file name?

 

[T. Valko]

Not real sure I follow what you're wanting to do.

See if this does what you want:

Sheet2 A1 = named cell = John
Sheet1 A1 contains the word John

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

Returns Sheet2