Dave,

I have to be honest - I think the extend formulas feature is a bit flakey,
and I can only really get it to work reliably when entering data into an
otherwise blank row.

Another possible method is to use events: For example, the code below will,
when you enter a value into any column of any row, copy all of the formulas
from the row above. That may not meet your requirements, but it will work
reliably, as long as macros are enabled. To use the event code, copy it,
right-click the sheet tab where you want this feature, select "View Code"
and paste the code into the window that appears.

Also, the formula that you are using:

=SUM(K4-E4)

really only needs to be

=K4-E4

The SUM is superfluous.

HTH,
Bernie
MS EXcel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myFormulas As Range

If Target.Cells.Count 1 Then Exit Sub

Application.EnableEvents = False

On Error GoTo NoFormulas

Set myFormulas = Target.Offset(-1,
0).EntireRow.SpecialCells(xlCellTypeFormulas)

For Each myCell In myFormulas
myCell.Copy myCell(2)
Next myCell

NoFormulas:
Application.EnableEvents = True
End Sub


"dave @ stejonda" wrote in
message ...

Bernie,

The formula I have in column N is (in row 4 for example) =SUM(K4-E4) where
columns K & E contain dates, hence column N displays the length of stay in
days. The check box you mention was already ticked so it seems as if I'd
already achieved what I wanted but just didn't know it.

Thanks to you and Bob. :)


In message , Bernie Deitrick
writes
Dave,

You need to be a little more specific - do you want a formula in each cell
of a column where the
other cells are filled in, or just a formula that is based on any cell in
a column.

If the first, use the option of extending lists: Tools / Options.. Edit
tab, check the box next
to "Extend List formats and formulas"

IF the second, write your formula like

=SUM(A:A)

instead of

=SUM(A2:A100)

HTH,
Bernie
MS Excel MVP


"dave @ stejonda" wrote in
message
...

I want to set a simple (k-e) formula to apply to all rows in a column
rather than to a finite set
of rows. This is because I want people to be able to add an indefinite
number of rows without the
formula suddenly stopping working. Is this possible? (I asked my work IT
helpdesk but they didn't
know how to do it nor whether it was possible! :)

thanks folks,

--
dave @ stejonda



--
dave @ stejonda