There must be an easier way!

peterbatah

New Member
Joined
Jul 17, 2017
Messages
25
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
 
Last edited:
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
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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. :banghead:

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. :outtahere:


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
ABCDEFGH
1DateTransactionDeposit/WithdrawalUnit PriceUnits bought/soldTotal UnitsFeesBonuses/Grants
2
3
4
5
6166.1771715773.56971
716-Apr-16PAC$ 2,500.0014.57830171.4877615945.05747
With Macro


and the macro is

Code:
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
 
Last edited:
Upvote 0
Sorry, because of my stupidity the formula didn't appear in the last post, they were

Excel Workbook
ABCDEFGHI
1DateTransactionDeposit/WithdrawalUnit PriceUnits bought/soldTotal UnitsFeesBonuses/Grants
2
3
4
5
6166.3771715773.56971
716-Apr-16PAC$ 2,500.00171.487814.57830171.4877615945.05747
With Formula


and the layout before the macro was run was

Excel Workbook
ABCDEFGH
1DateTransactionDeposit/WithdrawalUnit PriceUnits bought/soldTotal UnitsFeesBonuses/Grants
2
3
4
5
6166.1771715773.56971
716-Apr-16PAC$ 2,500.00
With Macro
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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
Back
Top