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

 

[My Aswer Is This]

I do not like clicking on links. Never know what could happen.
I would like to help if you would explain in words what your attempting to do.
And post any code you already have.

Please provide specific details

 

[peterbatah]

The links are safe. As a matter of fact one of them points to my Dropbox and they other to YouTube.

 

[MARK858]

The links are safe. As a matter of fact one of them points to my Dropbox and they other to YouTube.

Just because it points to your dropbox doesn't mean the file is safe especially when it is an xlsm (in your case it is) but just to help out as you are a relatively new member below is a screenshot of the file in case anyone wants to help who won't download it (I can't see at the moment any VBA which makes me wonder why it was a xlsm) ...

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	4-Jan-16	GMWB Fee		$ 13.68650	(120.26740)	15072.67889	-1646.04
3	5-Jan-16	PAC	$ 2,500.00	$ 13.69780	182.51106	15255.18995
4	6-Jan-16	PAC	$ 2,500.00	$ 14.71350	182.51106	15437.70101
5	7-Jan-16	PAC	$ 2,500.00	$ 14.75000	169.49153	15607.19254
6	8-Jan-16	PAC	$ 2,500.00	$ 15.02610	166.37717	15773.56971
7					0.00000	15773.56971
8					0.00000	15773.56971
9					0.00000
10					0.00000
11					0.00000
12					0.00000
13					0.00000
14					0.00000
15					0.00000
16					0.00000
17
Client

I can provide more info if required.

I would as even your video doesn't describe exactly what your issues are

 

[peterbatah]

@MARK858 Thank you so much for chiming in. All of the other files that are currently in use include some VBA. Habit I guess. I apologize if the YouTube video of me entering data appears somewhat vague. As for the test file that I have made available, the issue is that I feel that in order to yield the desired results there could possibly be a way to cut out some of the redundancy.

As can be seen in the YT video data is entered in the following order.

Once the date, transaction, and deposit/withdrawal are entered we move on to 1) Number of units for a given month are entered into cell D7 for eg. 112.94330 Hit F9. - 2) we then take the result 22.13500 in cell E7 and head back to cell D7 where we manually enter that figure followed by F9 where cell E7 now shows 112.94330 - 3) While in cell E7 we cursor up to cell E6 copy down the formula we now see 166.37717 in cell E7. Head back to cell E7 hit the enter key followed by F9 where we get the resulting figure of 112.94330 in cell E7. We are done. It just seems like a whole lot of moving around and definitely opens up the possibility for error.

I hope that I was able to provide some clarification without too much confusion. If you enter the data as described above into the excel file that I provided you should get the same results.

Note: the formulas used in row E is =IFERROR(C7/D7,0) - row F =F6+E7

 

[MARK858]

You are having to keep clicking F9 because your calculation will be set to manual, is this deliberate?
On a simple level you can just change your formulas to the below and drag down way past where you expect your data to go down to.

<b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E3</td><td >=IF(A3<>"",IFERROR<span style=' color:008000; '>(C3/D3,0)</span>,"")</td></tr><tr><td >F3</td><td >=IF(A3<>"",F2+E3,"")</td></tr></table></td></tr></table>

Or put the code below in the worksheet module (right click your sheet tab, click view code and paste the code in the window that appears).
The code will work out columns E & F when a value is entered in column D if the date has been filled in column A.
Test on a copy of your workbook.

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
        Target.Offset(, 1) = Evaluate("IFERROR(" & Target.Offset(, -1).Address & "/" & Target.Address & ",0)")
        Target.Offset(, 2) = Evaluate("" & Target.Offset(-1, 2).Address & " + " & Target.Offset(, 1).Address & "")
        Application.EnableEvents = True
    End If

End Sub

 

[peterbatah]

Thank you MAR858. Your time and assistance is greatly appreciated. Re: Manual Calc - I was under the impression that setting calc to manual would help improve the workbooks performance. Each of our client workbooks come in at at approx 70MB. I am sure that there are other factors that contribute to improved performance. However, this is the hand that I was dealt.

I inserted your code. When I input the total number of units in column D I am to manually enter the results produced in column E back into column D, is that correct?

I did a small A/B comparison and these are the results. There is a small discrepancy in the resulting figures. This may have been due to an error in the initial data entry. Still looking into that. Sorry about the image link. I don't know how to insert an excel table into the forum post.

Screenshot by Lightshot

 

[peterbatah]

Thank you MARK858. Your time and assistance is greatly appreciated. Re: Manual calculation - I was under the impression that setting calculation to manual would help improve the workbooks performance. Each of our client workbooks come in at at approx 70MB. I am sure that there are other factors that contribute to improved performance. However, this is the hand that I was dealt.

I inserted your code. When I input the total number of units in column D, I then have to manually enter the results produced in column E back into column D, is that correct?

I did a small A/B comparison and these are the results. There is a small discrepancy in the resulting figures. This may have been due to an error in the initial data entry. Still looking into that. Sorry about the image link. I don't know how to insert an excel table into the forum post.

Screenshot by Lightshot

 

[MARK858]

Columns A to D looked like the columns you were filling manually.

Fill them in in that order and E & F fill in the totals automatically.

I don't understand why you need to go back to D?

I am afraid an image isn't much good to me as we can't copy and paste from it.

To post usable screenshots see my signature block below and install the Excel Jeanie addin ( avoid the MrExcel HTML maker link as it goes to the old version at the moment).

 

[peterbatah]

Yes columns A thru D are filled in manually. Except, in order to achieve desired results, I would have to enter total units in column D first, take the number in col E then manually punch it in to column D. This using your code..

By the way I am using Microsoft Excel 2016 MSO (16.0.7726.1049) 64-bit. Office 365 subscription

I receive this error after installing Excel Jeanie

Screenshot by Lightshot

And this is the excel file with your code:

Dropbox - yourcode.xlsm

Columns A to D looked like the columns you were filling manually.

Fill them in in that order and E & F fill in the totals automatically.

I don't understand why you need to go back to D?

I am afraid an image isn't much good to me as we can't copy and paste from it.

To post usable screenshots see my signature block below and install the Excel Jeanie addin ( avoid the MrExcel HTML maker link as it goes to the old version at the moment).