Issue 127305

Summary: Cannot change cell format when it refers from another cell (e.g., C2 is "$", then D2 is also "$" and cannot be changed)
Product: Calc Reporter: Jim Michaels <jmichae3>
Component: formattingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P5 (lowest) CC: oooforum, petko
Version: 4.1.3   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
test-D2.ods
none
test-D2 file as excel 2003 xml none

Description Jim Michaels 2017-01-31 06:15:04 UTC
put currency SUM(C3:C183) in C2 (it's actually a SUM of a range).
put =C2/100.1 in D2
put some numbers in c3 through c5 as 100.1
format D2 as number,general
format D2 as all,general
right click, default format

actual:neither forced formatting works, default formatting neither, nothing varies from currency format. when I choose a format like general, nothing happens when I click OK (or was it Enter) - neither makes any difference, maybe the function that handles the OK button is missing a function call to actually do the formatting.

expected: default general format, maybe default format same as C2 (what about other cells, like if they are general? could wind up currency, but that may or may not be too helpful). 
ability to choose a format and it do something.
Comment 1 Jim Michaels 2017-01-31 06:15:56 UTC
xp media center 2005 32-bit dual-core box.
Comment 2 mroe 2017-01-31 09:12:07 UTC
> format D2 as number,general
> format D2 as all,general
> right click, default format

This is always the same! Use an explicit number format if you don't want the default format.

This isn't a help desk. Please discuss problems first in a user forum!
https://forum.openoffice.org/
Comment 3 Jim Michaels 2017-02-03 18:09:25 UTC
it's a formula... can't use a number format.
Comment 4 Jim Michaels 2017-02-03 18:15:28 UTC
comment 2: huh? define "explicit number format if one of those 3 was not one of them.

if this is a "please insert a $ sign in front of the number" etc, note that this is a formula I am trying to change the format of. you can't do that with a formula tht I know of without affecting the way the formula works ($A2 means A2 with A unchanged when copied or moved).
Comment 5 Marcus 2017-02-03 19:10:32 UTC
Why don't you simply add the spreadsheet to this issue? Then we can have a quick look and tell you what the problem is. ;-) Thanks
Comment 6 oooforum (fr) 2017-02-06 11:14:35 UTC
Feel free to provide requested document.

Once done, please set status back to UNCONFIRMED.
Comment 7 Jim Michaels 2017-02-07 04:02:25 UTC
Created attachment 85956 [details]
test-D2.ods

test file
Comment 8 Jim Michaels 2017-02-07 04:03:36 UTC
Created attachment 85957 [details]
test-D2 file as excel 2003 xml

different format
Comment 9 Jim Michaels 2017-02-07 04:40:10 UTC
I want D2 to be either general or fixed-point non-currency format. I am unable to change the format of D2 once formula entered with dependency on C2. D2 retains C2's format forcefully, which it should not. this is not a feature, it's like the computer saying "No, I don't want to and you can't make me" (blah)

again, the problem is calc is being too helpful with its formatting. 
extected: it should allow a change to format after entering formula with dependency (sure, it's probably a good idea to help the user ONCE by copying the dependent format, but:
- how many dependency cells are you going to do this with?
- how would you propose to merge the changes>
- you should not repeat the format merge copy "experience" lest it cause user hostility and frustration and wasted, frustrating time for consumer support.
Comment 10 Marcus 2017-02-07 19:15:23 UTC
@Jim:
Thanks for your sample document.

I can see the same problem on Linux. So, doesn't seem to be Windows-specific.

As workaround you can do:

a) First define the calculations, and then define the cell format for specific cells (e.g., define formula in C2 + D2, and then define the cell format for both)

b) Define a cell as header (e.g., write "US-$" into C1)

Then I don't have a problem to define a cell format for D1 different to C1.

However, I've never seen this problem in my daily work with spreadsheets. So, I don't know what here the special thing is.
Comment 11 Peter 2017-02-09 03:53:58 UTC
I do not see this as a defect.
OpenOffice is following exactly the rules to calculate with Units here. Which virtually the complete world uses.

If you spend 1000 $ on 100 Boxes, and you want the price of 1 piece you would recieve 10$ a peice. That is the Definition we follow, and it is considered as default assumption.

Also If you have a budget of 1000$ and you spend 100$ you can calculate 100$/1000$ and you will recieve 1/10 (note the missing of $)
And OO will also comply on this behaviour (with some limitations see below.).

I do not know what you are doing, but all Office Product work this way. (just confirmed it on Google docs, and I know the same from Excel.) And someone who does a lot of finacial calculations will expect exact this behaviour.

I suggest to put your 100.1 into an own Cell and format that Cell to currency.
This way you will have in your formula in D2 your expected result.

Maybe we should give a way to indicate that a number in a formula is a currency.
I.E. if you type (100.1) then it is automaticly concidered finencial number.

Since Open Office does not differ between any currency I think it does not matter much. I mean if you calculate 100$/100€ you will recieve 1, and Office will not ask you on change rate.

Jim is there an issue with my argumentation? Why do you want the different behaviour at all?