How to get a cell formula to automatically paste values

[adboy14]

Hi all,

I am looking for some assistance with my limited Excel knowledge to find a way to hard paste values after a cell calculates a formula.

The problem:

I have an expense sheet with expenses in various currencies. For tracking I have a formula that converts into one currency. Naturally the price changes along throughout the year and will cause accounting issues down the line.

I am looking for a way to do the calculation when the expense is inputted but paste the value in another column so it is not affect by the change in currency price.

I have attached an example to help with understanding.

Thanks in advance,

Adam

Calculation help.xlsx

[Glenn Kennedy]

Using a formula.... no. If you had a table of exchange rate and date, yes. otherwise, no.
Using VBA... yes.

An individual cell can hold either a value... or a formula, but not both.

If VBA is OK, let me know and I will move the thread.

[adboy14]

Thanks for the reply.

VBA knowledge is very limited but happy to give it ago.

[Glenn Kennedy]

Mine is non-existent! Someone else will help. Thread moved.

[JEC.]

Put this code behind your worksheetmodule. Alt + F11 to open VBA editor --> select your sheet and past this code.

Private Sub Worksheet_Change(ByVal Target As Range)
 With Target
    If .Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(.Offset, Range("F3", Range("F" & Rows.Count).End(xlUp))) Is Nothing Then .Offset(, 3).Value = .Offset(, 2).Value
    Application.EnableEvents = True
 End With
End Sub

[adboy14]

Perfect,

Thanks a lot for the help!