microsoft.public.excel.programming - Post any VBA, Macros, Add-Ins,Worksheet Functions; Excel Charting questions here
I have very simple spreadsheet with 5 columns. Last cell in each column is sum of cells above it using =SUM. I thought the formula would update automatically when I insert rows so that the new row cell would be added to the sum but this isn't happening. Automatic calculation is checked. What else do I need to do?
Hi, I have code that inserts a row for additional data that needs to be summed. But the sum formula below this new row does not update to include the newly increased number of rows. The only thing I have found in the forums is someone's suggestion to always leave a blank row just above the sum formula, and insert new rows above it. I have not tried this because it seems most inelegant! It seems there must be a way this can be done. Thanks for any insights you may have. Harold
Say I have a table with a column that is summed. The formula would be something like: =SUM(C4:C20) Assume there are column heads above this range, and that this formula is in C21. I'd imagine this is the most common arrangement used. If I insert a new row above 21, 99.9% of the time, I want the SUM to be updated to include that row. That does happens *UNLESS* I insert the row just above row 21. Unfortunately, that's where new rows want to go most of the time. Even when the table or column is named, it still doesn't expand the formula to include the new row. Rightfully so I suppose, since the new row is not logically within the formula's range. Deleting and moving rows have similar issues. I know there's an "INDIRECT" function, but it seems like a rather indirect way to get the desired result: =SUM(C4:INDIRECT("C"&ROW()-1)) Several things are lost by having to do it this way, e.g.: the highlighting of range references when editing the cell, and the automatic adjustment that Excel does when the formula is copied to another cell. I have taken to inserting a tiny row just above the row containing the SUM, so I can insert new rows that look like they're being added to the end of the range, but in fact the SUM includes the cell in the tiny row, so I can trick Excel into doing what I want. Of course, to actual do the insert, I have to try to select that tiny row! :-) So one alternative that strikes me as useful is a "this cell" reference, such that I could do something like: =SUM(C4:OFFSET(THIS,-1,0)) It seems to me this formula really matches what's visually being done. Is there such a thing (I couldn't find one)? If not, could this be implemented as a "user formula"? Is there a better method for adding, deleting, and moving rows that would allow formulas such as SUM and COUNT to adjust as needed? Thanks tbone
Hello, I have a formula in a column as follows - the details of what it does are for the most part unimportant (this one is at row 50): =IF(D50<>"",D50-SUM(G50:AA50), IF(C50<>"", E49 +C50-SUM(G50:AA50),"")) I frequently end up inserting rows and copying the above formula to the right column for the new row. What happens though is that this formula and all those following get messed up as so: =IF(D51<>"",D51-SUM(G51:AA51), IF(C51<>"", E49 +C51-SUM(G51:AA51),"")) Everything got adjusted except for the reference to E49 which should have become E50 (in the new row). I presume that it tries to keep references to where they were before that this happens but it messes me up in this instance as current rows need to know the immediately prior row and not earlier (like a chain). Is there anything anyone can recommend to me regarding this? It is actually a part of a spreadsheet that helps me plan the budget for my home where I can either specify how much is in my account or how much I expect to see deposited (in which case the amount is added to the prior effective total - that's where the e49 came in). I can then specify different bills for that time on the same row to see what or when I can afford to pay them. Thanks, Ben