1

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).

Improve this question
4
  • 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, 2012 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, 2012 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, 2012 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, 2012 at 14:20

3 Answers 3

Reset to default
1

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())))"
Improve this answer
1
  • 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, 2012 at 16:28
0

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.

Improve this answer
1
  • 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, 2012 at 16:06
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .