Log In
Register
Home
July 4, 2022
Offline
Hi All, I was asked to post an example file on utube in answer to a problem with running totals. Basically i would like to add a row when a new transaction takes place but I cant do it, instead I have to put a new transaction at the bottom of the page and then sort each time by date. When I apply the changes suggested online I get the result FALSE. Many thanks.
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
The file seems broken, I can’t download it.
Go to the blog section and search for running, you will get some articles to read. Here is one.
https://www.myonlinetraininghu.....al-formula
Br,
Anders
July 4, 2022
Offline
I'll try again.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I have some questions.
Where do you want to add a row?
What is the issue when adding the row where you want it?
What was suggested that does not work?
Why are you not using an Excel table for your data?
Br,
Anders
July 4, 2022
Offline
Hi Anders, I've now tried to make it work by creating a table but I've no experience with this.
For example, Can you tell me why when you add a row to this table, say ROW 183, column F copies down automatically, column G doesn't. I can't spot a difference in the formula?
Also what is the relevance of =6 at the start of the formula? It seems to work whatever number you put it.
Many thanks.
July 4, 2022
Offline
I'm all good now. Thank you.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Issues like the one you have with column G not copying the formula down is often related to some error/issue on the worksheet or in rare cases, the workbook. If you for example copy the first 10 or 20 rows and paste it in on a new worksheet or workbook and make it a table, you will see it will work as intended.
If it wasn't you that added the =IF(ROW()=6... in the formula then I don't know who did.
Apart from these issues, weren't there others you wanted help with?
Br,
Anders
July 4, 2022
Offline
I basically started again, got rid of the table, then created another. It works now (sort of) but it always seems with excel that when you resolve one issue another emerges. I now find I cannot "sort" the data by date without all the references going haywire. However now I can add and delete a row that may not be necessary.
I enjoy these challenges, I'm sure they can all be solved, its just a slow learning process.
July 4, 2022
Offline
I've got so far but have again come up against a brick wall. In the example workbook sheet 2 works okay until you add a row/transaction, then the formula no longer automatically copies down correctly. I've shown an example of what happens on sheet 3 by date 13th July. Many thanks.
Trusted Members
Moderators
Power BI
January 31, 2022
Online
Hi,
I took a close look at your file and concluded that you violated almost every rule in the book when it comes to structuring data and writing consistent formulas. Therefore, I have added another sheet that does work as you intend it. All conditional formatting, colours and borders have been removed and ALL data is now part of a structured table, not just the last few columns.
Now, I should have used structured table references as well but that would have changed the look of the formulas completely. Please see for yourself if I've understood you correctly..
Answers Post
July 4, 2022
Offline
Thank you. At first inspection that seems to work perfectly, exactly what I want. The first row didn't work but I just added an extra row at the top with the starting figure and now it does. I'm extremely grateful. 
1 Guest(s)
