| View previous topic :: View next topic |
| Author |
Message |
ceq2
Newbie
Joined: 04 Apr 2007
Posts: 2
|
 Posted: Tue Apr 10, 2007 1:27 am Post subject: Linked file Formula in Excel |
 |
|
I want to open my excel file using OOo Calc. I use linked file formula in my excel, and when I open it with openoffice, the cell that use the formula is being error. Is there anyone can tell me how to solve this problem, instead of I have to change the formula in each error cell.
Even I have convert excel document into OOc document, but it is still show error on the cell.
Note:
In Excel : INDIRECT("'SheetName'!"&ADDRESS(CellName))
In OOo Calc: 'file:///Path & File Name'#$SheetName.CellName
Regards, |
|
| Back to top |
|
 |
Mark B
Super User
Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK
|
| Posted: Tue Apr 10, 2007 2:29 am Post subject: |
|
|
Hi
Which version of OOo are you using? I had a similar problem in 2.0.4, but an upgrade to 2.2 solved it.
Mark
_________________
Mark B's Articles |
|
| Back to top |
|
|
TerryE
Super User
Joined: 16 Jul 2006
Posts: 550
Location: UK
|
| Posted: Tue Apr 10, 2007 4:46 am Post subject: |
|
|
I'm a little confused about this one, and why you would do sheet addressing this way.
ADDRESS has 4 arguments in Calc and 5 in Excel (the 4th is ignored by the XLS import filter):
ADDRESS(row; column; abs;sheet)
row represents the row number for the cell reference
column represents the column number for the cell reference (the number, not the letter)
abs determines the type of reference:
1: absolute ($A$1)
2: row reference type is absolute; column reference is relative (A$1)
3: row (relative); column (absolute) ($A1)
4: relative (A1)
sheet represents the name of the sheet. It must be placed in double quotes. Argument 3A in Excel is the type: A1 or RC format.
Specifying the sheet name as an argument to ADDRESS avoids the whole issue of the syntaxt differences of the sheet name prefix ! or .
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now. |
|
| Back to top |
|
|
nkvasan
General User
Joined: 02 Apr 2007
Posts: 36
Location: Chennai, India
|
| Posted: Tue Apr 10, 2007 5:18 am Post subject: |
|
|
Please try the following:
Go to Option - Openoffice.org Calc - updating and choose "on request".
This will eliminate any error occuring due to linked files not available
In case you want to update any specific link go to Edit - Link and choose the links you want to update.
This should solve a number of error cells you get.
_________________
Krithivasan |
|
| Back to top |
|
|
ceq2
Newbie
Joined: 04 Apr 2007
Posts: 2
|
| Posted: Tue Apr 10, 2007 7:11 pm Post subject: |
|
|
[quote="Mark B"]Hi
Which version of OOo are you using? I had a similar problem in 2.0.4, but an upgrade to 2.2 solved it.
Mark[/quote
I'm using 2.2, but it is still error with linked formula. Can you give me your sample files in Excel and in OOo Calc?And also some explanation for them..  |
|
| Back to top |
|
|
TerryE
Super User
Joined: 16 Jul 2006
Posts: 550
Location: UK
|
| Posted: Wed Apr 11, 2007 12:50 am Post subject: |
|
|
Before we do that why don't you give us an exact quote of the formula that you are trying to use. And you still haven't explained why you want to do the sheet referencing in this way which will case migration problems.
See also http://www.oooforum.org/forum/viewtopic.phtml?p=218648
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now. |
|
| Back to top |
|
|
|
