As the title suggests.
Please no over complications as most people seem to.
If I search this on Google it returns how to enter a sheet’s name in a cell, which I know, but not how to name a sheet with text from a cell.
e.g. I type “Hello” into cell A1. I then want the sheet name to automatically update with the contents of A1 and be named “Hello”.
It’s nothing more complicated than that.
Thanks
Keeping separate tables for each month makes everything a lot more complicated than necessary.
Yes, of course this can be implemented, the macro code can be short enough if you promise that in this spreadsheet there will be no more than one sheet with the text “January 2023” in cell A1, that you will never clear cell A1 so that the sheet does not received an empty name, and in addition to the fact that you will always write the correct lines in cell A1:
1 Like
@ Villeroy.
Hi Thanks for replying. Not sure what you mean by that as you don’t know what my spreadsheet contains but I was using “Hello” as an example to illustrate my question easily. It could be any text.
@ JohnSUN . Are you suggesting I use a macro? If so how do i do that?
Is there not an easy option?
Thanks.
https://ask.libreoffice.org/uploads/short-url/omMlzc26euKXULnKggksJ7XNh4U.ods
One header row, one column of complete dates, values below values, text below text, no empty rows, no empty columns. Rows in random sort order. Very simplistic and similar to a database table.
The pivot table on same sheet is derived from the flat list. First I created a pivot with dates and categories as row fields, persons as column field. Then I grouped the dates by months and years.
Add data to the list in newly inserted rows, right-click>refresh the pivot.
Use the auto-filter button in cell A1 in order to view data from any year, any month or any day.
Contrary to monthly sheets, one normalized table allows for statistical comparison between months, years, quarters, weeks. You simply dump equally structured data anywhere into the same table. The sort order is meaningless. You are free to sort the table any way you need.
Yes, of course I was talking about a macro - there are no built-in functions for the action you described.
By the way, if you know a working way to put the name of a sheet in a cell, then why don’t you use this method? What difference does it make where to write “Hello” - in a cell or in a sheet label - if the result is the same?
Oh, there are many approaches that can be taken, but in the end they all boil down to calling the method
oSheet.setName(sNewName)
For example, for the case when cell A1 contains a formula that collects values from different cells of the sheet to form the name of the sheet (I could not think of another application for your task). Let’s say A1 contains the formula =TRIM(A2&" “&A3&” "&A5), you enter the word “Rent” in A5, the word “February” in A3, the number 2023 in A2 and you expect the sheet to be automatically named “2023 February Rent”. No problem! Write a simple function that takes a new sheet name as a parameter, takes the current sheet, and gives it this new name:
Function setNameToActiveSheet(sNewName As String)
Dim oSheet As Variant
oSheet = ThisComponent.getCurrentController().getActiveSheet()
If oSheet.getName() <> sNewName Then
On Error Resume Next
oSheet.setName(sNewName)
On Error GoTo 0
EndIf
End Function
Now complicate the original function in A1 a little, add a call to this function:
=TRIM(A2&" "&A3&" "&A5) & SETNAMETOACTIVESHEET(CURRENT())
You don’t want the formula in A1, do you want to write text there? Not a problem either - select some other cell on the sheet and write =SETNAMETOACTIVESHEET(A1) there
Are you not satisfied with the need to write a call to this function in each sheet of the book (including newly added sheets)? Write a procedure that will rename all the sheets in a spreadsheet in one go:
Sub renameAllSheets(Optional oEvent As Variant)
Const CELL_WITH_NEW_NAME = "A1"
Dim oSheets As Variant, oSheet As Variant
Dim sSheetName As String, sNewSheetName As String
Dim i As Long
oSheets = ThisComponent.getSheets()
For i = 0 To oSheets.getCount()-1
oSheet = oSheets.getByIndex(i)
sSheetName = oSheet.getName()
sNewSheetName = Trim(oSheet.getCellRangeByName(CELL_WITH_NEW_NAME).getString())
If sNewSheetName <> sSheetName Then
If sNewSheetName <> "" Then
Rem ... There may be other checks here
On Error Resume Next
oSheet.setName(sNewSheetName)
On Error GoTo 0
EndIf
EndIf
Next i
End Sub
How you will call it is a separate question. You can create a keyboard shortcut to call this procedure, you can put a button on the toolbar, you can create a special menu item or use the existing Tools - Macros - Run Macro
You can even think of a way to run this macro on any spreadsheet action so that you don’t miss the moment when the next sheet needs to be renamed… but it seems like it would be too much to force the program to execute the macro on every move, isn’t it ?
This is complicated. The complicated thing isn’t to have a few lines of user code accomplishing the job. It is the fact that you need to tell the software that or under what conditions it should work for you. Does it need to listen to changes of cell contents, but only act then if a “hard” change to cell A1 was made. Shall this be done with all the sheets or only with those you dreamt of last night? There may be dependent actions - and different conditions.
You may then want to get the change also if A1 was recalculated, but may not understand that then a different approach is needed.
The next user will want to also set a new tab color for the sheet and many things more?
I would encourage you to play actively with the creation of user code for such features if you are interested in programming or are looking around for a new hobby.
Otherwise: For what all this?
As luck would have it, I wrote flexible and extensible custom code for this kind of tasks quite recently - without any specific intention to apply it. But I will not make a reduced version of it for every extra request. If you are looking for a suitable starting point for your own considerations, feel free to study and use my code. See attachment.
customizeSheetFormatting.ods (29.8 KB)
Made and tested with LibreOffice V7.4.3.2