[Solved] Display cell name

Discuss the spreadsheet application

[Solved] Display cell name

Postby wadem101 » Sat Jun 11, 2016 12:05 am

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" ?
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
wadem101
 
Posts: 2
Joined: Fri Jun 10, 2016 11:09 pm

Re: Display cell name

Postby acknak » Sat Jun 11, 2016 2:20 am

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
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Display cell name

Postby Zizi64 » Sat Jun 11, 2016 10:25 am

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

Code: Select all   Expand viewCollapse view
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 49 times


Created and tested in LibreOffice 4.4.7
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
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.
User avatar
Zizi64
Volunteer
 
Posts: 9882
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display cell name

Postby Villeroy » Sat Jun 11, 2016 1:11 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29876
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Display cell name

Postby wadem101 » Wed Jun 15, 2016 4:42 am

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]"
Apache OpenOffice 4.1.1
Windows 10 Home
wadem101
 
Posts: 2
Joined: Fri Jun 10, 2016 11:09 pm

Re: [Solved] Display cell name

Postby Zizi64 » Wed Jun 15, 2016 6:42 am

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
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.
User avatar
Zizi64
Volunteer
 
Posts: 9882
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Display cell name

Postby Zizi64 » Wed Jun 15, 2016 6:49 am

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.

Xray an object.png
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
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.
User avatar
Zizi64
Volunteer
 
Posts: 9882
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: No registered users and 9 guests