Forum Discussion

Jrow3568's avatar
Jrow3568
Copper Contributor
Jan 20, 2025

Formula

I am using a spread sheet program to keep track of my stock.

First col is the ticker. Second col is the number of shares I own. Third col is the dividend that the stock pays per share. The fourth column is the total amount the stock paid. (Col 2 *Col 3). These figures/formulae are all on the same line. The dividend per share changes so I delete the figure in the third col. after the stock pays. I then enter it again when the new dividend is known. . When I do this I Inose the formula in the fourth col. 

Is there a way to hold this formula?

Formulas and Functions

3 Replies

Sort By
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jan 27, 2025

    The most robust way would be to create a new table for dividend per share.  So I would have 

    Date,  Company,  Dividend/Share

    Then you can do a lookup based on max date <= given date for given company to find the correct Dividend/Share value

    while you are at it and assuming the # of shares you own doesn't change often then you could also have a separate table for the # shares you own and do a lookup for that value too so you don't have to manually enter that each time.

  • pranav trikha's avatar
    pranav trikha
    Copper Contributor
    Jan 26, 2025

    Greetings!

    Sir,

    Manual solution proposed

    Formula holds in Excel Mobile App, 

    Even when I delete Div/Share and enter new Div/Share, 

    Can use "Camera tool" to capture previous values,otherwise VBA

    Col A.       Col B.      Col C.        Col D

    Share.        No.         DPSh.        Total

    Msft.           2.             7.              14

    ⬆️                                       Formula

    Ticker from stocks              =B3*C3

    Thanks,

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 24, 2025

    The simplest solution would be to do this manual:

    Before clearing column 3, select column 4 and use Copy > Paste Values to preserve the results.

    Once the new dividend is known, reapply the formula to column 4.

     

    If to do this manual isn’t an option, you can use VBA.

    The VBA approach ensures the formula is automatically managed and results are preserved without manual intervention.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = Me ' Refers to the current worksheet

    ' Check if the changed cell is in column C (Dividend per share)
    If Not Intersect(Target, ws.Columns("C")) Is Nothing Then
        Dim rng As Range
        For Each rng In Target
            If IsEmpty(rng.Value) Then
                ' If the cell in column C is cleared, keep the current value in column D
                ws.Cells(rng.Row, "D").Value = ws.Cells(rng.Row, "D").Value
            Else
                ' If a new value is entered, update the formula in column D
                ws.Cells(rng.Row, "D").Formula = "=B" & rng.Row & "*C" & rng.Row
            End If
        Next rng
    End If
End Sub

    Note: VBA code is untested, backup your file first.

    The VBA macro listens for changes in column 3 (dividend per share).

    If you delete a value in column 3, the macro saves the current value in column 4 (total paid) as a static value (no longer linked to the formula).

    If you enter a new value in column 3, the macro re-applies the formula in column 4.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

Resources