Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel document that contains X columns and N number of rows.

The very last column of a row performs a SUM of the first X-1 columns. The problem I have is, the user of this Excel document progressively adds rows to the document, and because of this, the function does not exist yet in the last column for new rows.

I need a way to have this function exist in new rows dynamically (the user is not Excel-savvy and doesn't have the ability to just drag the function down a row).

share|improve this question
    
It is easy to make it appear as if the function is put into the cell dynamically, but I have no idea how to actually accomplish a dynamic cell update like that. What version of excel are you using? –  soandos Jun 8 '12 at 13:20
    
What version of Excel are you using? From what I observe, the desired behavior is automatic in Excel 2007. –  Excellll Jun 8 '12 at 15:16
1  
I should also ask how the user is entering new data. If the user enters the data manually, one row at a time, Excel will extend the SUM formula to the new row. However, if the new rows are pasted there, then the formula will not extend. –  Excellll Jun 8 '12 at 15:24
1  
Is there anything wrong with educating the user? All he/she would have to do is press Ctrl+D in the last column for new rows. –  Ellesa Jun 9 '12 at 14:20

3 Answers 3

Unfortunately a user that can't do basic stuff is pretty common. You might resort to something that totally rewrites the formula, (which is admittedly, overkill) But see if you can adapt this idea in the Worksheet_SelectionChange event. Or possibly enabling macros will trip them up too.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 LastRow = ActiveSheet.UsedRange.Rows.Count
 Range("D2:D" & LastRow).Formula = "=SUM(INDIRECT(""A""&ROW()&"":C""&ROW()))"
End Sub

for subtotals

Range("D2:D" & LastRow).Formula = "=SUBTOTAL(9,(INDIRECT(""A""&ROW()&"":C""&ROW())))"
share|improve this answer
    
of course this will enter un-necessary formulas if they leave blank rows, and you may want to use the subtotal function instead –  datatoo Jun 8 '12 at 16:28

You could probably make this truly dynamic with VBA, but I think that's overkill for simply adding a formula to a cell. What you could do is make it appear like the cell is empty until data is added to the pertinent cells, then drag that cell down for hundreds or thousands of rows.

For example =IF(LEN(A1)=0,"",A1+B1) will show an empty cell if A1 contains no data but will execute the A1+B1 function if it does. If you drag that down below your final row, the column will appear blank until data is added to the next cell in column A.

There are several other formulas you could use to check for cells in use, such as =ISBLANK(A1) OR =ISTEXT(A1) based on how you want to check for data. I think the LEN function easiest to work with. You can also do things like this =IF(LEN(A1)+LEN(B1)=0,"",A1+B1) which will run the formula if either cell has data, or =IF(OR(LEN(A1)=0,LEN(B1)=0),"",A1+B1) which will only run the formula if both cells have data.

For most formulas, extending this for several thousand rows only adds a few KB to the size of your file. If you have very lengthy formulas, or many of them, that you are dragging down, it can significantly increase the size of the file.

share|improve this answer
    
And you'll need to protect the formulas in those cells, because if your user isn't savvy enough to know how to fill down a formula, they certainly won't understand that they shouldn't delete the formulas from that column. –  Excellll Jun 8 '12 at 16:06

I'd have to see your worksheet but it sounds like you could use a pivot table. The user simply does data entry and the reports are dynamically generated and aggregated. Check out youtube for some instructions and demos on how to use pivot tables.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.