Forum Discussion
Automatic update of Cell filename Worksheet name value from one sheet to the other
Hello,
I have used a formula to recover the worksheet name in a cell and when switching from one sheet to the other the update is not done in the cell - is there an option I missed that will allow me to print the n pages of worksheets and have all sheets with the correct names in the sheets ?
Actually when printing the names stay the same of the last updated sheet within all sheets.
I use the formula as follows to recover the sheet name, but its not updated in the different sheets:
= RIGHT(CELL("filename"); LEN(CELL("filename")) - FIND("]";CELL("filename");1))
When selecting any sheet in the bottom the sheet name in the cell is not updated.
Same for printing:
I had expected that printing 3 different sheets will give in each sheet the actual sheet name and not in all three pages the same sheet name.
The only way to update I found is manually refresh the sheet by F9 even though the Excel help says the F9 or Shift F9 is only required if automatic update is off, but it is selected on. When printing I would have to pass through all sheets and update them manually one by one and then print one by one.
I found another strange issue about this problem, when opening two times Excel, then the name of the second Excel File introduces itself into the cell of the other Excel File !
In the annexe I will put a screenshot showing it is switched on automatic but showing the wrong name in the cell...
What option did I miss please ?
Best regards
Martin
PS:
The Excel Version is:
Microsoft Excel 2019 MSO (16.0.14228.20200) 32-bit
Product ID: 00413-20019-01150-AA024
Windows Version is: Windows 10 Pro 20H2 OS build 19042.1110 Windows Feature Experience Pack 120.2212.3530.0
I've had this issue before. It'll give me the same Value in all the sheets.... But, reference the cell that you're using this for. I have different tabs for different dates and they all USED TO have the same dates until I used Formula Below: ("B1" is the cell I used for this formula) All sheets will update in accordance with your tab name.
=RIGHT(CELL("Filename",B1),LEN(CELL("Filename",B1))-FIND("]",CELL("Filename",B1)))
- NikolinoDEGold Contributor
If you’re looking to ask a question or start a conversation about Excel, you’re in the right place!
Please include the following info to help others answer your question: Click on the Link
Welcome to your Excel discussion space!Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
- Luc123Copper Contributor
Hello, everyone, I have the same issue. I am trying to get file name with CELL("filename"). It is working fine, until I save any other file. Then it gives me the name of that other (just saved) file. Was this behavior expected or is it a bug?
I wanted to use date stamp from name of the file in some additional formulas. Like this, I am not able to do that.
Thank you in advance.
Lucia
Windows 10, 64 bit, Version 20H2- NikolinoDEGold Contributor
You have to rephrase the formula.
Example: Instead of =SUM(BE3:BE37), but = SUM(Sheet1!BE3:BE37).
If your worksheet is called "Sheet1".
There was still an old trick, I don't know if it still works.
In the first worksheet I mark the cells that I want to transfer to another worksheet and press Ctrl-C. Then switch to the target worksheet and mark the target position by clicking in the cell.
Then right mouse button -> paste content
... Now click on the "Link" button at the bottom right in the "Paste content" selection menu.
Then the contents are there, but all cells are references to their origin in the other worksheet.
Create or change a cell reference
A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. Click on the upper Link for more Informations.
Note: Formulas that use CELL have language-specific argument values and will return errors if calculated using a different language version of Excel. For example, if you create a formula containing CELL while using the Czech version of Excel, that formula will return an error if the workbook is opened using the French version. If it is important for others to open your workbook using different language versions of Excel, consider either using alternative functions or allowing others to save local copies in which they revise the CELL arguments to match their language. More informations on the upper Link.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.
- SandovalContractingCopper Contributor
I've had this issue before. It'll give me the same Value in all the sheets.... But, reference the cell that you're using this for. I have different tabs for different dates and they all USED TO have the same dates until I used Formula Below: ("B1" is the cell I used for this formula) All sheets will update in accordance with your tab name.
=RIGHT(CELL("Filename",B1),LEN(CELL("Filename",B1))-FIND("]",CELL("Filename",B1)))
- NS_RobertCopper Contributor
SandovalContracting Thank you, thank you, thank you, thank you. Just stepped up from Excel 2010 to 2021 so lots to learn (or unlearn and relearn).
- SandovalContractingCopper Contributor
NS_Robert No Worries! Sometimes you just need to be specific with formulas, once you start going broader in your formulas you get more errors. Glad I can Help!