Currency format doesn't update

[acounter2]

I have created label print solution, where single row data of 12 cells is converted into 5 rows printable label for collection items.
Printout is 10 labels on A4 sheet.
That means I copy 10 rows and I receive new sheet of 10 printable labels.

Now the problem is currency format.
When in a row the original cell has $ format, then target cell inherits $ format.
But now this target cell remains "forever" $ and when I copy a new row for printout with cell in £ format, it remains $ in target cell, instead of £.

There is a formula in target (label) cell =+L3*1.2.
Any ideas how to force target cell to update currency format?
Shift+F9 doesn't fix this.

 

[jasonb75]

Formulas only convey the value of the source cell, not the format.

At best you might be able to use the CELL function in conditional formatting to change the currency symbol, but that is on the assumption that the formulas are all as simple as the one in your example and that the source cells are valid numbers formatted as currency, not text strings that look like currency amounts.

 

[acounter2]

Thanks for your message.
I can't find conditional formatting option "inherit number (currency) format" from source cell.
There is just fixed option to select predetermined currency format.
Any more hints where to look?

Formula is always simple - just multiplying source cell by 1.2 (VAT value).
But problem is that source cell currency format varies between €, $ and £.

Thanks.

 

[jasonb75]

I can't find conditional formatting option "inherit number (currency) format" from source cell.

There isn't one as such, I had it in mind to use the CELL function to identify the format of the source, but looking in more detail it appears that it is not possible to do it that way. As far as I can see, all currencies symbols give the same result.

I'll have another look later but I think that this might only be possible with vba.

 

[acounter2]

Formulas only convey the value of the source cell, not the format.

That's the problem if there isn't any legal option to copy format.

Interestingly - if cell A1 is £ and I enter into cell A2 "+A1", then from source cell £ currency format is transferred into A2.
But when source A1 currency has been changed later, it doesn't change in target A2.

 

[jasonb75]

That's the problem if there isn't any legal option to copy format.

There is but it would need to be done with vba to copy the format from the source cell.

Note that this may or may not work depending on a number of factors.
To try this method, right click the sheet tab (e.g. the name 'Sheet1' at the bottom of the page) then paste the code below into the vba window.
I've set the code to apply formatting to formulas in F1:F4, this can be changed as needed. Note that it will only work if the formula is on the same sheet as the source data.
There needs to be a change to the sheet to trigger recalculation (change of cell value for example) simply changing the format of the source cells is not sufficient to trigger this, if you have any formulas like =NOW() or =TODAY() then pressing f9 will work.

Private Sub Worksheet_Calculate()
Dim c As Range
On Error Resume Next
For Each c In Range("F1:F4")
    c.NumberFormat = c.Precedents(1).NumberFormat
Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.Calculate
End Sub