Name of Worksheet into cell 1

[lrfcbabe]

Excuse me for asking this again but I am confused on one thing. If I open an Excel spreadsheet ("Myfile.xls") it opens up using the Excel application. This is also for the most part considered a "Workbook", for me anyway being a VB(A) kinda person. In that "Workbook" there are multiple "Worksheets", the tabs that run along the bottom of the "Workbook". I used "worksheet name into cell" for my search criteria. I get back something other than what I would expect, like putting the filename into a cell or something. My question is how do I get the name of a "Worksheet" into a cell?

 

[SkipVought]

Hi,

Use the CELL function with the "filename" argument and then the RIGHT function and FIND function to find the ] character...
[tt]
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lrfcbabe]

With no direct reference to the name of the "Worksheet" I would have never guessed that this would work. So I tried it and there it was. Hint Hint Hint, CELL("filename") returns, "C:\Documents and Settings\nnn\Start Menu\Programs\[Myfile.xls]4-3-2009". The "Worksheet" name is "4-3-2009". I would not have thought to look at the filename to get the name of the "Worksheet". Grats, Again Skip.

 

[lrfcbabe]

Well Well Well, turns out that this does not work after all. Any other ideas?

 

[SkipVought]

Care to explain?

What does =Cell("filename") return?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

For the record, I had typed the exact same formula that Skip posted, but was a bit slow so I didn't press "submit". It will, indeed, return the name of the worksheet in which the formula resides.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lrfcbabe]

If there are multiple Worksheets and I add the formula to a cell then all the sheets(that have the formula) will have the same name as the sheet that the formula was just added to. If I goto another sheet it also has the previous sheet name until I repaste the formula into it's cell. Now all the other sheets change to that sheets name. I was wanting to have a cell on each sheet to have it's sheets name. So if I change the sheet name then it will update the cell as well. I would not have to change both. I hope I did not just confuse everyone. Thanks again

 

[SkipVought]

My question is how do I get the name of a "Worksheet" into a cell? "

My post answered this question.

"If there are multiple Worksheets and I add the formula to a cell then all the sheets(that have the formula) will have the same name as the sheet that the formula was just added to. "

Now THAT is another DIFFERENT question. Ask the right question, and you'll get the right answer!!!

So now I'm going to probe and attempt to discover what you're really after.

How do you intend to use the worksheet names that are on your sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

lrfcbabe, if I understand you correctly, you are saying that the results are the same in all of the sheets. Well, I would always put a cell of the sheet in the formula anyway, like:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
and then make sure that calculation mode was automatic and not manual.

Does that help?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[lionelhill]

Yes, I've had this problem too; under some circumstances, filename doesn't seem to update as automatically as one might expect.

However, I have a confession: I used to use cell("filename", A1) merely because I wanted to print the name of the worksheet on the worksheet.

If you are using it for the same purpose, it's much easier and better to use the header-footer bits of print options to add the sheet name.

 

[SkipVought]

Yes, I've had this problem too; under some circumstances, filename doesn't seem to update as automatically as one might expect."

...as one would expect using ANY spreadsheet function, if the calculation mode has been set to MANUAL.

Excel 101!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

...as one would expect using ANY spreadhseet function, if the calculation mode has been set to MANUAL."

That's a little over-simplistic. Even with Automatic recalculation switched on, a re-calculation is not triggered by saving, printing, or copying (at least in Excel 2003). As a result, if you enter an =cell("filename", A1) formula and then use "Save as" with a new name, the old, incorrect name will remain on the sheet that is open, and the sheet will print and copy/paste with the wrong information. This doesn't apply to header/footer names, which seem to be up to date. Note that this is exactly the opposite of the behaviour found in Word.

 

[JPaules]

ASAP Utilities has a function that will do what you want. MUCH easier than messing with a formula. One click and it's done.

--
JP

 

[mintjulep]

I can reproduce the behavior that lrfcbabe reports.

Enter =cell("Filename") in a cell on sheet1 and it returns the filename ending with sheet1.

Enter =cell("Filename") in a cell on sheet2 and it returns the filename ending with sheet2.

So far so good.

But now, return to sheet1. The result that previously returned sheet1 now returns sheet2.

Enter something in another cell to force an auto-recalc and the cell formula updates to correctly return sheet1.

But now go back to sheet2 and the cell formula there now is returning sheet1.

This definitely seems wrong to me.

 

[SkipVought]

Strange!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

From excel help about CELL function:
if the second argument is omitted, the result refers to last changed cell.

combo

 

[SkipVought]

When in doubt, read the instructions!!!

Thanks, combo! ==> *

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

You are welcome!

combo

 

[mintjulep]

And useful to know.

I seem to recall people asking about how to id the last changed cell.

The answer now is =CELL("address")

Unfortunately you don't seem to be able to recurse it. For example, you would think that

=CELL("contents",CELL("address"))

would return what was entered in the last cell changed. Unfortunately it doesn't work.

 

[combo]

=INDIRECT(CELL("address")) will do it (except of the first entry - circular reference).

combo