Hello all! I have a budget I use but it's getting frustrating to constantly have to add lines when I exceed what I've setup. Is there a way to have Excel auto add a line if a line has data in it and is there a way to get a forumla to recognize when a new line has been added and update? Thanks!
Not sure what you mean by "have Excel auto add a line if a line has data in it "? Excel has over a million rows, so to exceed that will take some doing.
What formulas are you using? Perhaps upload a small dummy workbook here showing what you have and what you want?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Yeah that was vague on my part, my apoligies. I don't have access to send the spreadsheet now but let's say I have numbers in cells A1:A4 and it
sums them in A6. I'm wanting it to where if I have data in A4 then excel automatically adds a row below A4 to add another set of Data and it would push the sum formula to A7 which would auto update to calculate for the new row. Thanks!
OK, have you considered putting the totals at the top? That way you can extend the sum() way down below where your data is, and wont need to insert any rows
Im guessing you have data currently in say A1:K20 and a set of totals in A21:K21 and you want to have the totals update automatically if you add in a new row before the totals?
Theres two ways I know of....
1. Stick your totals in say A500:K500 then hide/unhide rows as necessary
2. Set up a dynamic named range, referring to the cell above, do this by setting up a Name, selecting the cell above the one you were in and then removing the $ from the formula....eg put your cursor in A2, define a name which will automatically come up with =Sheet1!$A$2, amend that to =Sheet1!A1 and voila, you have a dynamically named range that you can use anywhere (in Sheet1) ie =SUM(N1:upone) in N20 will sum N1:N19 etc
Last edited by pjwhitfield; 11-16-2014 at 08:51 PM.
If someone has helped you then please add to their Reputation
Both ideas are good but I dig putting the totals at the top. Can't believe I didn't think of that. Thanks!!
The easiest way that I know of to do what you are asking is to have your data in a table. (Insert tab, Table) When you have your data in a table, there is an option to have a Total Row. This is found on the Design Tab, Table Style Options Group. This row is placed at the bottom of the table. Each cell in that row has a drop down menu with various calculations that can be made on the column above that cell. Choose what you want. When you add a new row, the Total Row is automatically pushed down and remains at the bottom of the table.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thanks for that too newdoverman, if I do that and I have a sum total on another page as well, is there a way to get that formula to update when the total cell has been pushed down? Thanks!
It should update automatically
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks