If statement to seperate transactions by currency

[theCadman]

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.

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.

 

[Peter_SSs]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	Tarkov Trading Spreadsheet
2	EXAMPLE	Buy Price	Sell Price	Profit	Trasaction Date	Currency	BANK (₽, $, €)	Roubles - ₽	Dollars - $	Euros - €		Current BANK
3	Item - # bought/sold	$100,000	$200,000	$100,000	12/17/2019 2:49:38 PM	Roubles - ₽	Log Start	1,000,000 ₽	$1,000,000	€ 1,000,000		$900,000
4		$100,000	$50,000	$ -50,000	12/17/2019 3:30:50 PM	Euros - €				€ 950,000		€ 950,000
5		$300,000	$200,000	$ -100,000	12/17/2019 5:53:38 PM	Dollars - $			$900,000			1,000,000.00 ₽
6				$0
7				$0
Sheet1

Cell Formulas
Range	Formula
D3:D7	D3	=SUM(C3-B3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D	Cell Value	=0	text	NO
D:D	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F1:F7	List	='Currency List'!$A$3:$A$5

Existing 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]

Thanks, but what about

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

 

[Peter_SSs]

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
	B	C	D	E	F	G	H	I	J	K	L	M
1						BANK (₽, $, €)	Roubles - ₽	Dollars - $	Euros - €
2	Buy Price	Sell Price	Profit	Trasaction Date	Currency	Log Start	1,000,000	1,000,000	1,000,000		Current BANK
3	$100,000	$200,000	$100,000	12/17/2019 2:49:38 PM	Roubles - ₽		1,100,000				1,100,000	Roubles - ₽
4	$100,000	$50,000	($50,000)	12/17/2019 3:30:50 PM	Euros - €				950,000		900,000	Dollars - $
5	$300,000	$200,000	($100,000)	12/17/2019 5:53:38 PM	Dollars - $			900,000			950,000	Euros - €
6
7
Sheet1

Cell Formulas
Range	Formula
H3:J7	H3	=IF($F3=H$1,LOOKUP(9.99E+307,H$2:H2)+$D3,"")
L3:L5	L3	=LOOKUP(9.99E+307,INDEX(H$2:J$1000,0,ROWS(L$3:L3)))
D3:D7	D3	=IF(C3="","",SUM(C3-B3))

Cells with Data Validation
Cell	Allow	Criteria
F3:F7	List	='Currency List'!$A$3:$A$5

 

[theCadman]

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]

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
	B	C	D	E	F	G	H	I	J	K	L	M
1						BANK (₽, $, €)	Roubles - ₽	Dollars - $	Euros - €
2	Buy Price	Sell Price	Profit	Trasaction Date	Currency	Log Start	1,000,000	1,000,000	1,000,000		Current BANK
3	$100,000	$200,000	$100,000	12/17/2019 2:49:38 PM	Roubles - ₽		1,100,000				1,100,000	Roubles - ₽
4	$100,000	$50,000	($50,000)	12/17/2019 3:30:50 PM	Euros - €				950,000		880,000	Dollars - $
5	$300,000	$200,000	($100,000)	12/17/2019 5:53:38 PM	Dollars - $			900,000			1,005,000	Euros - €
6	$400,000	$455,000	$55,000	18/12/2019 3:59:27 PM	Euros - €				1,005,000
7	$60,000	$40,000	($20,000)	18/12/2019 4:00:10 PM	Dollars - $			880,000
Sheet1

Cell Formulas
Range	Formula
H3:J7	H3	=IF($F3=H$1,LOOKUP(9.99E+307,H$2:H2)+$D3,"")
L3:L5	L3	=LOOKUP(9.99E+307,INDEX(H$2:J$1000,0,ROWS(L$3:L3)))
D3:D7	D3	=IF(C3="","",SUM(C3-B3))

Cells with Data Validation
Cell	Allow	Criteria
F3:F7	List	='Currency List'!$A$3:$A$5

 

[theCadman]

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]

You're very welcome. Thanks for the follow-up.