I would like to show the cell names I "defined" in referenced cells. Something like the FORMULA() function.
For example: A1<--$11.00, A2<--$22.00 and A3<--=SUM(A1:A2) showing "$33.00".
Now -- with cell A3 selected, I do: "Insert - Names - Define. The Define Names (Ctrl+F3)"
... and enter "line20a"
(Note that when cell A3 is selected by itself, "line20a" shows in the range box.)
(Note that when 4 cells are selected A3,A4,B3 & B4, "A3:B4" shows in the range box)
In cell B6, I enter the formula =line20a, now it shows "$33.00".
If I enter =FORMULA(B6) in B7, it shows "=line20a".
Is there a function like CELLNAME(A3), that I could enter in B5, for example,
that would show "line20a" ?
[Solved] Display cell name
[Solved] Display cell name
Last edited by wadem101 on Wed Jun 15, 2016 4:43 am, edited 1 time in total.
Apache OpenOffice 4.1.1
Windows 10 Home
Windows 10 Home
Re: Display cell name
Greetings and welcome to the community forum!
Sorry, I don't know of any way to get access to the name definitions from a formula.
You can insert the names and definitions as text: Insert > Names > Insert > Insert all
Sorry, I don't know of any way to get access to the name definitions from a formula.
You can insert the names and definitions as text: Insert > Names > Insert > Insert all
AOO4/LO5 • Linux • Fedora 23
Re: Display cell name
You can display the "user defined name" of a cell (or a cellrange) by a short macro code:
Example file with the macro:
Created and tested in LibreOffice 4.4.7
Code: Select all
Function GetCellUDName(sAbsName as String) as string
Dim oRanges as object
Dim oNamedCell as object
Dim iNMCount as integer
Dim i as integer
GetCellUDName = "***"
oRanges = ThisComponent.NamedRanges
iNRCount = oRanges.GetCount
For i = 0 to iNRCount-1
oNamedCell = oRanges.GetByIndex(i)
'Use the XrayTool to display the properties of the objects.
'xray oNamedCell
if oNamedCell.Content = sAbsName then
GetCellUDName = oNamedCell.Name
end if
next i
End Function
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Display cell name
menu:Insert>Names... [Insert All]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Display cell name
Zizi64's short macro code looks promising.
I downloaded the file "MyCellName.ods". Opening it gave a few problems:
1. I am now running Apache OpenOffice 4.1.2
2. Java (Version 8 Update 91).
3. I've lowered the Tools == OpenOffice -- Security -- Macro Security to "Medium"
4. I had to edit the line: xray oNamedCell with a ' to make it a comment like the CODE: window.
Now it works well enough.
I'll ask Zizi64 how to make it work relative instead of sheet and cell absolute in quotes.
Thank you
I'll mark this as "[Solved]"
I downloaded the file "MyCellName.ods". Opening it gave a few problems:
1. I am now running Apache OpenOffice 4.1.2
2. Java (Version 8 Update 91).
3. I've lowered the Tools == OpenOffice -- Security -- Macro Security to "Medium"
4. I had to edit the line: xray oNamedCell with a ' to make it a comment like the CODE: window.
Now it works well enough.
I'll ask Zizi64 how to make it work relative instead of sheet and cell absolute in quotes.
Thank you
I'll mark this as "[Solved]"
Apache OpenOffice 4.1.1
Windows 10 Home
Windows 10 Home
Re: [Solved] Display cell name
My macro works in AOO 4.1.2 too.1. I am now running Apache OpenOffice 4.1.2
The java version is not relevant in this case.2. Java (Version 8 Update 91).
If you copy the macro code into the MyMacros/Standard library, then not need to change the security level, and the macro will available for every spreadsheets. Otherwise the Medium level is safe: you can enable (manually) the running of the macros stored in the documents. You can examine the macro code before it will run.3. I've lowered the Tools == OpenOffice -- Security -- Macro Security to "Medium"
You need install and use an object inspection tool (like the XrayTool or the MRI) if you want work with macros and the API. The object inspection tools can display the properties, methods, interfaces of the used objects in your macro code.4. I had to edit the line: xray oNamedCell with a ' to make it a comment like the CODE: window.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Display cell name
The object "oNamedCell" has a property named "Content". It contains the absolute name of the named cellrange. See the attached picture created by the XrayTool (and by hit Alt-PrtSrcr and Irfan View software)I'll ask Zizi64 how to make it work relative instead of sheet and cell absolute in quotes.
You need "purge" the name string, if you want use the macros with relative references.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.