[Solved] Display cell name

[wadem101]

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" ?

[acknak]

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

[Zizi64]

You can display the "user defined name" of a cell (or a cellrange) by a short macro code:

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

Example file with the macro:

MyCellName.ods

(11.63 KiB) Downloaded 128 times

Created and tested in LibreOffice 4.4.7

[Villeroy]

menu:Insert>Names... [Insert All]

[wadem101]

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]"

[Zizi64]

1. I am now running Apache OpenOffice 4.1.2

My macro works in AOO 4.1.2 too.

2. Java (Version 8 Update 91).

The java version is not relevant in this case.

3. I've lowered the Tools == OpenOffice -- Security -- Macro Security to "Medium"

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.

4. I had to edit the line: xray oNamedCell with a ' to make it a comment like the CODE: window.

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.

[Zizi64]

I'll ask Zizi64 how to make it work relative instead of sheet and cell absolute in quotes.

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)
You need "purge" the name string, if you want use the macros with relative references.