There must be an easier way!

[peterbatah]

Hello,

My client put together this worksheet which helps him track his investment portfolios. I made a few transaction entries myself and found the process to be counter intuitive at best. If there is an easier way I would appreciate your assistance. I can provide more info if required. Thank you.

Please see attached files for examples.


peter 2017 08 22 16 08 11 - YouTube

Excel File:

Dropbox - peter.xlsm

Transaction data:

Screenshot by Lightshot

 

[peterbatah]

Nothing happens when I enter 171.4878 into the "With Macro" sheet.
Doing so in the "With Formula" sheet appears to yield the correct result. However, 14.57830 should end up in column D (Unit Price)
Again, can't thank you enough for the time and effort you are putting into helping me out.

In the file in the link below do you get the correct results if you type 171.4878 in D7 in each sheet.

Obviously in the formula version the results will be 1 column over.

https://app.box.com/s/w6v1eatcacqcf4qoa1pb2sjauz1n0exk

 

[MARK858]

a)If nothing happens with the macro sheet and when you type then you haven't enabled the macro's when you downloaded it, just downloaded it again and tested it again and it works fine.

However, 14.57830 should end up in column D (Unit Price)

b) What have I stated multiple times? You can't have a formula result in a cell you type in, You must have it in a different cell so you need another column. Hide Column D afterwards if you don't like it being there.

Because of A & B above I am going to leave you to it now, as I have retested A and am not stating B again.


Other posters for anyone who can't/won't download and just in case anything happens with the file...

The layout and formula are

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Transaction	Deposit/Withdrawal	Unit Price	Units bought/sold	Total Units	Fees	Bonuses/Grants
2
3
4
5
6					166.17717	15773.56971
7	16-Apr-16	PAC	$ 2,500.00	14.57830	171.48776	15945.05747
With Macro

and the macro is

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Range("D3:D" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then
        Application.EnableEvents = False
         With Target
            .FormulaR1C1 = "=ROUND(IFERROR(RC[-1]/" & Target.Value & ",0),5)"
            .Calculate
            .Value = .Value
            .NumberFormat = "0.00000"
        End With
        
        With Target.Offset(, 1)
            .FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],0)"
            .Calculate
            .Value = .Value
        End With
        Target.Offset(, 2) = Target.Offset(-1, 2).Value2 + Target.Offset(, 1).Value2
        Application.EnableEvents = True
    End If

End Sub

 

[MARK858]

Sorry, because of my stupidity the formula didn't appear in the last post, they were

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date	Transaction	Deposit/Withdrawal	Unit Price		Units bought/sold	Total Units	Fees	Bonuses/Grants
2
3
4
5
6						166.37717	15773.56971
7	16-Apr-16	PAC	$ 2,500.00	171.4878	14.57830	171.48776	15945.05747
With Formula

and the layout before the macro was run was

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Transaction	Deposit/Withdrawal	Unit Price	Units bought/sold	Total Units	Fees	Bonuses/Grants
2
3
4
5
6					166.17717	15773.56971
7	16-Apr-16	PAC	$ 2,500.00
With Macro

 

[peterbatah]

Thank you MARK858. Have a great weekend. Your help is immensely appreciated. Peter