INSERT WORKSHEET NAME INTO CELL

littleme

Board Regular
Joined
Nov 15, 2004
Messages
156
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 =)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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

Board Regular
Joined
Nov 15, 2004
Messages
156
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365

ADVERTISEMENT

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

Board Regular
Joined
Nov 15, 2004
Messages
156
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

Well-known Member
Joined
Nov 4, 2003
Messages
960
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.
 

Forum statistics

Threads
1,147,508
Messages
5,741,572
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top