Re: setting a formula to apply to all rows in a column

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Dave

If you have XL2003 then use Data>Lists
This will automatically extend your list for you including any formulae
as you add more rows at the end.
This feature is even further enhanced (and better) in XL2007.

--
Regards

Roger Govier


"dave @ stejonda" <no$spam!delete&abuse%dave@xxxxxxxxxxxxxxxxxxx> wrote
in message news:YcUvcpFNxJqFFA8s@xxxxxxxxxxxxxx

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 <#AN6i4lNHHA.2028@xxxxxxxxxxxxxxxxxxxx>, Bernie Deitrick
<deitbe@xxxxxxxxxxxxxxxxxxxx> 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" <no$spam!delete&abuse%dave@xxxxxxxxxxxxxxxxxxx>
wrote in message
news:jTnRvZBOG3pFFAir@xxxxxxxxxxxxxx

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


.

Relevant Pages

  • Re: how to password protect a validation list?
    ... Have you started with the DV cell unlocked? ... protect Drop Down Validation Lists therefore the value can be changed at ... prevent access to the DV cell and the second asks how to protect the cell ...
    (microsoft.public.excel.misc)
  • Re: VLOOKUP? Need to have value returned when select from drop-down list
    ... Biff, thank you SO much! ... > there is no selection made from the drop down and cell A1 is empty then ... > to make drop-down lists and everything, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: how to password protect a validation list?
    ... Have you started with the DV cell unlocked? ... protect Drop Down Validation Lists therefore the value can be changed at ... prevent access to the DV cell and the second asks how to protect the cell ...
    (microsoft.public.excel.misc)
  • RE: Data table function
    ... Have now cleaned up the Town Names column using TRIMand VLOOKUP works ... Probably now look at the data sheet to see if I can clean it up a bit. ... You would have that type in cell ... the last one must be false for unsorted lists. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Compare 2 lists / conduct gap analysis (XL 2007)
    ... it'll be different after adding the empty cell than multiplying by the cell with ... Be careful with the format stuff. ... I had applied "text" format to my original Client and Recipeint lists (sheet ... My company has a tool that sends out documents to clients based on ...
    (microsoft.public.excel.misc)