If statement to seperate transactions by currency

theCadman

New Member
Joined
Dec 17, 2019
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I am trying to build a spreadsheet to keep an accurate log of transactions, profit and current balance of 3 currencies. However I need help trying to segregate each transaction to only affect the balance of their matching currency. Here is what i have so far.

Ledger.JPG


So the goal is if for example the transaction in row 4 was Euros and I select Euros from the Currency Drop Down and the +/- Profit will be listed under the Euros in the J Column and show the change to the balance for each transaction in their respective currencies only and show the final balance in the L Column. Is this possible or is this too complex? I am not sure how to accomplish this but I assume you could do it through an if/else statement in VBA code. ANy help is appreciated.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Can you give us the sample data in a form that we can copy to test with? My signature block below has help with that.

Also, I think this should be possible with standard formulas without the need for VBA. Would that be acceptable?
 

theCadman

New Member
Joined
Dec 17, 2019
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Welcome to the MrExcel board!

Can you give us the sample data in a form that we can copy to test with? My signature block below has help with that.

Also, I think this should be possible with standard formulas without the need for VBA. Would that be acceptable?
I have the following Sample data however I dont know if you also need the existing VBA code so you can see how it is currently operating so I added it just in case.

Book1
ABCDEFGHIJKL
1Tarkov Trading Spreadsheet
2EXAMPLE Buy PriceSell PriceProfitTrasaction DateCurrency BANK (₽, $, €) Roubles - ₽ Dollars - $ Euros - € Current BANK
3Item - # bought/sold $100,000 $200,000 $100,00012/17/2019 2:49:38 PMRoubles - ₽Log Start 1,000,000 ₽ $1,000,000 € 1,000,000 $900,000
4 $100,000 $50,000 $ -50,00012/17/2019 3:30:50 PMEuros - € € 950,000 € 950,000
5 $300,000 $200,000 $ -100,00012/17/2019 5:53:38 PMDollars - $ $900,000 1,000,000.00 ₽
6 $0
7 $0
Sheet1
Cell Formulas
RangeFormula
D3:D7D3=SUM(C3-B3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D:DCell Value=0textNO
D:DCell Value=0textNO
Cells with Data Validation
CellAllowCriteria
F1:F7List='Currency List'!$A$3:$A$5


Existing VBA Code

VBA Code:
Private Sub Worksheet_change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("C:C"), Target)
xOffsetColumn = 2
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each rng In WorkRng
        If Not VBA.IsEmpty(rng.Value) Then
            rng.Offset(0, xOffsetColumn).Value = Now
            rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If

If Not Intersect(Target, Range("B2:C1000")) Is Nothing Then
If IsNumeric(Target) Then
If Target.Value < 500 Then
    Application.EnableEvents = False
    Target.Value = Target.Value * 1000
    Application.EnableEvents = True
End If
End If
End If
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Well, I'm going to start of suggesting a formula approach (since you already have formulas in your sheet & then it doesn't require any code change).

I have modified the layout slightly as I didn't understand where/how you were signifying a starting bank. It looks like H3:J3 but then the early part of row 3 looks like a transaction row.

I have slightly modified your column D formula so you don't have those 0 values showing until a transaction has finalised.
Formula in H3 is copied across to J3 and down as far as you might ever want.
Formula in L3 is copied down to L5

Columns H:J and cells L3:L5 can be formatted to show the appropriate currency symbol.

Book1
BCDEFGHIJKLM
1BANK (₽, $, €)Roubles - ₽Dollars - $Euros - €
2Buy PriceSell PriceProfitTrasaction DateCurrencyLog Start1,000,0001,000,0001,000,000Current BANK
3$100,000 $200,000 $100,000 12/17/2019 2:49:38 PMRoubles - ₽1,100,000  1,100,000Roubles - ₽
4$100,000 $50,000 ($50,000)12/17/2019 3:30:50 PMEuros - €  950,000900,000Dollars - $
5$300,000 $200,000 ($100,000)12/17/2019 5:53:38 PMDollars - $ 900,000 950,000Euros - €
6    
7    
Sheet1
Cell Formulas
RangeFormula
H3:J7H3=IF($F3=H$1,LOOKUP(9.99E+307,H$2:H2)+$D3,"")
L3:L5L3=LOOKUP(9.99E+307,INDEX(H$2:J$1000,0,ROWS(L$3:L3)))
D3:D7D3=IF(C3="","",SUM(C3-B3))
Cells with Data Validation
CellAllowCriteria
F3:F7List='Currency List'!$A$3:$A$5
 

theCadman

New Member
Joined
Dec 17, 2019
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
So the goal was for me to put in buy/sell amount and select the currency in the dropdown boxes in the F column and it go in and check if the currency selected in the F Column match any of the currencies in the H1-J1 Cells and if it did add the adjusted balance according to the transactions +/- profit in the transactions currency cell. so kind of like a check book but for multiple currencies and the current bank balance currencies to stay the same but adjust to whatever the corresponding currency's last transaction balance was. Does that make sense? I know its complicated.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Does that make sense?
Yes, I think I understand that. I also think that my suggested formulas - together with your existing code - will do exactly that.
Did you try it?

Here is the sheet again and the only change I have made is to add values in B6:C7 and use the drop-down in F6 & F7
Because I already had the other formulas populated as described in my last post, the values in H6:J7 just appeared and the values in L3:L5 updated automatically.

There is a slight issue with my display as my regional date format is d/m/y whereas I suspect yours is m/d/y.

Have I misunderstood what you want?

Book1
BCDEFGHIJKLM
1BANK (₽, $, €)Roubles - ₽Dollars - $Euros - €
2Buy PriceSell PriceProfitTrasaction DateCurrencyLog Start1,000,0001,000,0001,000,000Current BANK
3$100,000 $200,000 $100,000 12/17/2019 2:49:38 PMRoubles - ₽1,100,000  1,100,000Roubles - ₽
4$100,000 $50,000 ($50,000)12/17/2019 3:30:50 PMEuros - €  950,000880,000Dollars - $
5$300,000 $200,000 ($100,000)12/17/2019 5:53:38 PMDollars - $ 900,000 1,005,000Euros - €
6$400,000 $455,000 $55,000 18/12/2019 3:59:27 PMEuros - €  1,005,000
7$60,000 $40,000 ($20,000)18/12/2019 4:00:10 PMDollars - $ 880,000 
Sheet1
Cell Formulas
RangeFormula
H3:J7H3=IF($F3=H$1,LOOKUP(9.99E+307,H$2:H2)+$D3,"")
L3:L5L3=LOOKUP(9.99E+307,INDEX(H$2:J$1000,0,ROWS(L$3:L3)))
D3:D7D3=IF(C3="","",SUM(C3-B3))
Cells with Data Validation
CellAllowCriteria
F3:F7List='Currency List'!$A$3:$A$5
 
Last edited:

theCadman

New Member
Joined
Dec 17, 2019
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
OMG. Yaaaassss! Thank you so much. You have no idea how long ive been working ont his with a buddy. we were struggling. This works perfectly. We were having so much trouble trying to figure this out that I was assuming it would be easier to just build it with VBA code. Thank you so much.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows
You're very welcome. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,136,314
Messages
5,675,024
Members
419,544
Latest member
MVPDoug

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top