using a name from another file

[fb314]

Hi,

let be in a file F2. If I want to reference the cell A1 of sheet 'sheet1' of file 'F1', I can write

[noranthon]

I've just managed to alter a DDE link from a reference to the cell address to a reference to the name of the cell as follows:
=DDE("soffice";"/home/terry/Work/Tables.ods";"DateCalcs") - DateCalcs being the name of the cell.

On principle, you should not have to use a sheet name or number because a cell/range name is unique in a spreadsheet document.

Like you, I cannot get it to work with the format you are using. I have found that references to named cells do not even work in other sheets in the same document.

[Villeroy]

[noranthon]

I'm not sure I understand the previous post. In the file, I was thinking about, Sheet1 is named "Main" and Sheet2 is named "Data". A cell on Main is named "DateCalcs". (It contains a formula which gets a date a specified number of days from another date.) If I enter on "Data" the formula "=DateCalcs", I get "0" (zero). If I try anything else, such as "=$Main.DateCalcs", I get "#NAME?" On the other hand, a formula which uses a rangename from that sheet produces the desired result.

Error messages also result, as the originator of this subject indicates, when you try to refer to a range by name from another document (unless you use a DDE link). It is not so with other software. The spreadsheet I previously used automatically substituted range names for cell/range addresses in formulas, wherever they occurred.

[Villeroy]

Sorry, I was talking about the definition of names (Ctrl+F3) in Calc, rather
than usage of named ranges in Excel. A Calc-name "can remember it's fixed
star at birth"(merry christmas, meaning: it's relative position to the
currently active cell *and* the relative position of currently active sheet).
For the API-aware readers: I'm talking about com.sun.star.sheet.XNamedRange.getReferencePosition
In Calc you can define range-names like:
$Main.$A$1:C$3 ->Name refers to A1:C3 on sheet Main
Main.$A$1:C$3 ->Name refers to A1:C3 on the (next/previous/Nth offest) sheet if Main was the (next/previous/Nth offset) sheet when the name was created.
$A$1:$C3 -> Name refers to A1:C3 on any sheet.
Sheet-positions can be relative or absolute just like columns and rows.
Relative sheet-positions are not known in Excel.
In Excel version>8 you may refer to a defined name on another sheet. I don't know. My Excel 8 replaces the sheet-name with the workbook-name and "$Sheet1" is an invalid sheet-identifier.
If you really need this kind of named reference in Calc:
=INDIRECT(ADDRESS(ROW(VectorX);COLUMN(VectorX)))
In this example VectorX is an absolute reference to a cellrange in another sheet. The function returnes the value of the first cell from the address of VectorX on *this* sheet.
The same with address of VectorX on a sheet named "Times":
=INDIRECT("Times."&ADDRESS(ROW(VectorX);COLUMN(VectorX)))
Unfortunately Calc's INDIRECT() does not work in array-context, but you can refer to the second cell of VectorX:
=INDIRECT(ADDRESS(ROW(VectorX)+1;COLUMN(VectorX)))
'+1' could be a calculated offset, depending on your needs.
Another approach if you want to drag/copy the reference from $G$12 downward or to the right:
=INDEX(INDIRECT("Times."&ADDRESS(ROW(VectorX);COLUMN(VectorX))&":"&ADDRESS(ROWS(VectorX);COLUMNS(VectorX)));ROW()-ROW($G$12)+1;COLUMN()-COLUMN($G$12)+1)
Unfortunately Excel uses "!" as sheet-separator, but you want to use the
INDIRECT()- addressing in both programs:
Define name "ShSep" referring to =MID(ADDRESS(1;1;1;"Foo");4;1)
(Sheet "Foo" may exist or not)
=INDIRECT("Times"&ShSep&ADDRESS(ROW(VectorX);COLUMN(VectorX)))
This will use 'Times.' as sheet-prefix in Calc and 'Times!' in Excel.

[noranthon]

I believe we're discussing the use of range names in Calc. I've never used Excel and have no wish to do so. The point is that the range name appears to have limited uses, compared to legitimate expectations and, incidentally, compared to other software.

Since any range name is unique to a file, it seems that it should be possible to use the name anywhere in the file and, for that matter, in other files, as a reference to the cell or cells so named.

We are not talking about the cell/range address, which is freely useable so long as 1. the file (if necessary) is identified; 2. the sheet (if necessary) is specified and the column and row numbers are stated. We are simply talking about the use which may be made of a defined range name.

[Villeroy]

[Villeroy]

Just another way of dummy in this file (F2.ods):
Define the names as literal strings within doublequotes.
Name dummyPath ="/tmp/F1.ods"
Name dummySheet ="$sheet1."
Name dummyURLPrefix ="'file:///tmp/F1.ods'#"
Name dummyAddress = "A1"
=DDE("soffice";dummyPath;dummySheet&dummyAddress)
=INDIRECT(dummyURLPrefix&dummySheet&dummyAddress)
INDIRECT() converts a string to a reference