Forum Discussion
Running Total Formula Changing on me
I have had that happen one other time, where somehow a correction to a formula in a table doesn't populate with the corrected formula to new rows; it's as if Excel remembers somewhere in the back of its inscrutable mind the original formula and keeps insisting on using it.
In the attached, although I didn't finish your conditional formatting (nor did I repair the graph), I totally deleted the original column N, then inserted a new one, and wrote the correct formula, which now does seem to propagate correctly. You obviously know how to complete the rest of the stuff.
Basic message, you may need to start with an altogether new spreadsheet.
By the way, I think it might be better to keep the transaction history in one sheet, the summary reporting in another. Mixing Input and Output on one sheet (especially as you've done it here, with summary on top of the transaction history) can cause the editing/revising of either one to mess up the other. People often talk of creating a "dashboard" sheet that presents the high level summary, and keeping it separate from (in the same workbook, but on a different tab) the raw data.
You might also consider--here I don't know your clients, the people you're creating this for--creating a simpler transaction history, where the buying of shares of ABC is one row (with date, ticker, quantity, price, total) as one row, and the sale of shares another row. This would be almost essential if your clients do a series of buys of ABC in, say, quantities of 500, then did sales in quantities of 100 after significant capital gains (or losses)..... Your design presumes buying and selling the same quantities (there's only one column for "Shares"); which could be a design flaw.
Since you've gone to the trouble to create a spreadsheet with no confidential data, so as to post the image, would you be so kind as post a copy of that actual spreadsheet so we can look at the real thing, not just an image? Post it on OneDrive or GoogleDrive, with a link here that grants edit access to it.
https://1drv.ms/x/s!ApG1FtftEA3Ggt9zIUBER6OJuC1kWg?e=QE3CU4
The formula in cell N16 should be
=IF(ISBLANK([@[$ Return]]),"",SUM($K$16:K16))You'll note that I made a few other minor changes. I would not recommend "forcing" headings to two lines, as you had with "Sell Price" -- you may think it makes things look nicer, but it makes formulas read strangely. Far better would be to do "Sell_Price" and if you need to keep the column narrow, tilt it in "Format....Cells....Alignment" Then the formulas AND the headers are readily legible
Here, for example, are the headings on a similar tracking sheet I have for my investments in options:
It's very easy to read (that image size is greatly reduced from the actual).
- P.S. It was in the Times Roman font on my Mac.....
- I like that tilt idea, thank you for that. So I downloaded your copy and tested with a new line and it still changed the formula on me. This link is the lines with the new line added, that's all I changed is adding a new line with info. Both the 2nd and 3rd line have K18 in the formula, it's not populating correctly.
https://1drv.ms/x/s!ApG1FtftEA3Ggt91gR3TTFlMfyMFKA?e=FplMcz