Inserting formulas into Excel 2

[kabushnell]

I have a spreadsheet that I would like to have a formula automatically copied into a row each time data is entered. To explain, there currently is 50 rows of data with the formula in column C. When someone enters information into row 51, can Excel automatically insert the formula from row 50, using the data from row 51?

I know that you can manually copy the data to say 1000 rows but then when the user scrolls to the end of the sheet there could be a bunch of blank rows.

 

[SkipVought]

kabushnell,

This can be done with VBA using the worksheet_change event.

In this example the formula I place in column B is like

=A2

Here is the code...

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    With Target
        If Cells(.Row, 2).Formula = "" Then
            Cells(.Row, 2).Formula = "=A" & .Row
        End If
    End With
    Application.EnableEvents = True
End Sub

Post your formula if you need help



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[kabushnell]

I thought that maybe with VBA I could do this. My formula is sum(D7:AM7) for example. The formula is contained in column c if that changes anything?

Thanks for your help.

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    With Target
        If Cells(.Row, "C").Formula = "" Then
            Cells(.Row, "C").Formula = "=sum(D" & .Row & ":AM" & .Row & ")" 
        End If
    End With
    Application.EnableEvents = True
End Sub

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[kabushnell]

Sorry, I haven't used VB to extensively. I put the code into a module and I can't make it work. Anything else I should be doing?

 

[dcompto]

Skip,
A star from me, too. Thank you!

 

[KenWright]

Another option is to add data using Data / Form. This will only prompt you for non-formula entries and automatically put the formulas in. If the range is named Database then this has special meaning to Excel and will automatically be extended to encompass the new row.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[kabushnell]

I got the code to work.

Thanks Skip

 

[BossInSearch]

Something else to consider:

Setup your data range and name the left-most cell below the data range. For example, if your data headings are in A11, then I would name cell A2. Assuming, of course, that no data has been entered yet. Assume the name for this cell is 'bottom'.

Setup a partial row farther down, below the data range (or on another sheet), with each cell formatted appropriately and any formulas that are required. Setup protection (lock or unlock) on the appropriate cells, if desired. Name this range 'typical'.

Write a macro to go to 'bottom', turn off protection, insert a new row and copy 'typical' to that new empty row, turn protection back on. You can record most of this macro. Just be careful to differentiate Tools, Protection from Format, Cells, Protection as you record.

This macro makes it very easy to control where data gets entered and how it is formatted. Combine this with a macro to delete a row and you have a nice way to control where data gets typed and how it is formatted.

I sometimes don't like this solution because it always adds data at the bottom of the range. This can be remedied by simply allowing the row insertion to be allowed anywhere in the data range.

I have used this technique many times for clients that have a need to control input and it works out nicely. Toss in another macro to duplicate the cell above and you can speed up data entry considerably.