INSERT WORKSHEET NAME INTO CELL

[littleme]

Hello again...

You people have helped me do such a good job on the time sheets that my boss decided that i was the one to do the rest of the office's excel stuff... yay? so here i am again...

Got 3 questions:

1: Is there a way to insert the worksheet name into a cell, in a similar way that it is done in the header? Have looked at an answer by Juan to a seemingly similar question, but couldnt get it quite to work, or rather, didnt understand how it was mean to work... would prefer to not use code but...

2: Is there a way for a cell to find the most recent date in a column?

3: Is there code that will prevent user, regardless if push Enter or Tab, to jump to the next unlocked cell? Im using a mac, so the choice of choose only unlocked cells or whatever under protection is not available to me, as it seems to be on a PC...

Answers to any of the above are welcome =)

 

[martinee]

1. Try this formula:
=RIGHT(CELL("filename",D4),LEN(CELL("filename",D4))-FIND("]",CELL("filename",D4),1))

2. Assuming your list of dates is in A1:A10 (Change to suite):
=Max(A1:A10)


Not sure on #3.
HTH.

 

[Oaktree]

1: =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

2: =max(a1:a10), formatted as a date

3: If you're using Excel 2002 or later, try going to tools --> protection --> protect sheet and don't allow users to select protected cells

 

[littleme]

thank you for the very quick replies =) Some follow up questions though:

For the first formula, do you mean type in the actualy file name where youve written "filename" or.... What if I only want the name of the worksheet, not the whole workbook? What am i telling excel to do...? Dont understand how the formula is meant to work... am also assuming that the commas are mean to be ";"?

Am getting value error message...=S

and as for the protection, that choice that youre refering to is not available to be as I am using office 2004 for Mac. Is there a code I could use? My boss is the most computer illiiterate person I have ever met, so the less cells he has access to in any way, the better...

take care

nina

 

[Oaktree]

1) No. =cell("filename") means just type "filename", not the name of your file. You can look at help concerning the =cell() function for more help on this.

2) Make sure your dates in A1:A10 are stored as dates and not text. (If you format A1 as a number, it should show something like 38742)

3) What options do you have if you go to tools --> protection --> protect sheet?

 

[littleme]

thank you =)

1:realised thats what you meant... still cant get it to work though...

3:when I go to toold/protection/protect sheet, only geet aksed if i want to use passoword and allows me to choose if want to protect Content, Objects and Scenarios....

 

[martinee]

1:: You say it is still not working? What does happen then? Basically, how the formula works is it returns the entire filename, but then trims it down so only the sheet name appears in the cell. If the cell remains blank after entering the formula, make sure you have saved the workbook before entering the formula. Otherwise, there is no filename to display. HTH.