Bug 63278

Created attachment 77626 [details]
Example cash ledger spreadsheet showing CF bug

Version 4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3)
Windows 7 Professional 32-bit, HP Probook6545b with AMD Turion 64-bit/4GB RAM

Bug 57661 is fixed on my machine, but a related problem remains, similar to what I reported in Bug 57176 (which was marked as a Duplicate of 57661), and was a duplicate as I had phrased it.

However, the fix for 57661 only solves half of my issues with Conditional Formatting (those that had caused CF to be lost irretrievably when using keyboard's Delete key)

I still have a problem when pasting only numbers (without pasting Formatting)

Simple replication (SHORT VERSION)
1. Open attached CashCheck_test2.ods
2. Select range S3:S14 (deliberately omitting cell S2 as a bug-workaround)
3. Copy their contents (Ctrl-C, Copy icon or Edit/Copy)
4. Select cell K3
5. Try to paste in only the numbers or formulae in the cells without formatting. For example you could right-click/Paste Only |> Number
or you could Edit/Paste Special... hit key shortcuts P,N,F,C,O,M based on the usual defaults to select pasting text, numbers, date & time only with None as the operation) or you could add a button Paste Only Values to your toolbar and click that.
6. Notice that the numbers go into cells K3:K14 but that the Conditional Formatting comparing the values of K2:K13 to N2:N13 is absent except for the cell K2 which we omitted deliberately.
7. Try to Undo. The values change back but the CF is still missing.
8. Now hit Redo and the values return. CF stil missing.
9. Manually type the value 50.00 from cell S2 into cell K2 and hit Enter
10. Recover the missing conditional formatting by hitting Format Paintbrush while K2 is selected then drag it from cell K3 to K13 (ignoring K14, which doesn't include Conditional Formatting)
11. Missing cell S2 and K2 from my copy and paste values and typing it manually then format-painting is my workaround for this bug until it's fixed.

If you want to understand the spreadsheet better, a longer explanation is included below.

LONGER EXPLANATION:
In the attached spreadsheet, CashCheck_test2.ods, range S2:S14 includes an example of the cash in various denominations plus card-payment total that is in a cash register or till at the end of date 080203. (I normally have the contents of range R1:S15 in a separate Spreadsheet file for my records over time, but I've pasted it onto CashCheck_test2.ods to demonstrate the problem to you more simply)

At this stage, K2:K14 still represents the £199.60 float of cash change that was actually put into the till at the start of the day and cells N2:N14 indicate the normal nominal Float (which also amounts to £199.60).

Cell O16 indicates that the amount that started in the till, summed at K15, is equal to the usual float, summed at N15.

The conditional formatting in range K2:K13 indicates whether the amounts in each denomination are more, less than or equal to the nominal float of range N2:N13 and colums O and P show any excess or deficit from nominal float. On this occasion, I started the days with a £2 coin more than usual and £2 less than usual in £1 coins to compensate.

At the end of the day, I'd like to copy the amounts of cash in Today's Takings (range S2:S14) into the contents of cells K2:K14
To work around this bug I actually paste S3:S14 into K3:K14 then manually type S2's value into K2 so that I can Format Paint the Conditional Format that has disappeared into K3:K14

I'd then enter the total (the value in S15, and by that stage also K15) into cell C19 and note that L18 is zero.

After that I'd notionally transfer the Credit Card total, K14, out of Petty Cash and into the bank account by typing it in D20. I'd then delete K14 and note that L18 is zero (and coloured Green)

Then I'd transfer various sums coloured light blue in column K from the till (column K) into the 'Old Till' - the Petty Cash tin for coins or the 'Bag' - a cash bag for notes/bills to get these denominations to the correct amount for the next day's cash float (columns J and I respectively).

I also transfer sums coloured light red or pink from the Petty Cash tin to make up the till to the correct amount for the next day's cash float.

The Hint in column Q indicates the expected amount of cash that will remain in column J or column I once the nominal float is present in the till and there is no Excess or Deficit (columns O and P).

Green sums in column K indicate that the amound in the till matches the nominal float (column N value), so the Hint value (column Q) will match the column J or I value.

Once the till is ready for the next day, cell O16 will say FLOAT amount CORRECT and be coloured bright green.

This spreadsheet acts as a transaction ledger or journal of credits and debits to double-check the cash amounts and help track down errors in recording cash movements as they happen (where Undo/Redo can often reveal the mistake made) rather than allowing them to be discovered days or weeks later when the cash balances don't match up. Cell L18's formatting helps to reveal when the ledger doesn't balance.