Getting #Value! error in Calc

I am working on OpenOffice.org Calc DDE Links and getting a #Value!
error when trying to display a negative value formatted with a currency
SAR using DDE link on a another sheet, I have tested same thing in Excel
it works fine.

What exactly I am trying is I have 2 Calc Spreadsheets File1 and File2
, in sheet1 of File1 I have collection of Data from A1:A20, In cell A21
I am using a simple SUM formula which is =SUM(A1:A20). I have also
formatted this Cell with a SAR currency format so the amount is
displayed as SAR 11,111,111.00 .  I copy this cell from sheet1 of File1
and then goto sheet1 of File2 and then after selecting cell A5, I
right-click and select Paste Special to create a link by selecting
"Paste All" and "Link" option. Finally I get a result in
File2 which is
SAR 11,1111,111.00 a positive value and a DDE link is displays in
formula bar as DDE("soffice";"C:\Documents and
Settings\Username\Desktop\File1.ods";"Sheet1.A21") but in File2 I
want
to display the same SAR 11,111,111.00 as a negative value such as in
brackets (SAR 11,111,111.00) or - SAR 11,111,111.00 so to get a
negative vale I add "-" sign infront of -DDE and the complete formula
looks like =-DDE("soffice";"C:\Documents and
Settings\Username\Desktop\File1.ods";"Sheet1.A21") but I get a
!Value#
error.

When I remove the currency format from the cell A21 in File1 the value
displays as 11,111,111.00 without SAR currency then in File2 formula
works fine and displays -11,111,111.00. 

It seems in Calc we cannot use Currency format when using DDE link to
link data and want to display as negative value but without currency
the value does not make any sense if different currencies are present
in the same file then how do we differentiate. So please help me to fix
this problem .


Thanks,
Syed Hussain


-- 
sadathusain
------------------------------------------------------------------------
sadathusain's Profile: http://forums.novell.com/member.php?userid53
View this thread: http://forums.novell.com/showthread.php?t=321372