Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
setting a formula to apply to all rows in a column
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
setting a formula to apply to all rows in a column
Maybe you mean something like this
In say, L2: =IF(COUNT(E2,K2)<2,"",K2-E2) Copy L2 down to cover the max expected extent of data in cols E and K, say down to L200. Col L will then appear "blank" until numbers are entered into both cols E and K. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
setting a formula to apply to all rows in a column
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
setting a formula to apply to all rows in a column
If you have at least 5 rows already set up, then simply entering data in the
other columns in new rows will automatically fill in the formulas. You do have to have this setting: Tools/Options/Edit/"Extend data range formats and formulas" -- the last needs to be checked. Bob Umlas Excel MVP I'm leading a FREE 1-hour online Webinar on Excel Tips & Tricks on Feb 13 and Feb 26 from 6-7PM est. If interested, go to http://www.iil.com, click on the yellow/orange "Try a free webinar" link on the left side, click the Microsoft Excel Tips & Tricks link, follow instructions to register. You can also order my book on tips & tricks by visiting the site http://www.iil.com/iil/excelmagic Thanks. Bob Umlas "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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
setting a formula to apply to all rows in a column
The usual trick is to pre-fill with blanks
Let's say we want the formula to be =A1+B1 and copy down the column At some point we will run out of values in columns A&B. So instead we use: =IF(A1="","",A1+B1) and copy all the way down Now the formula will return blanks unless there is actual data in column A, etc. -- g.s. gsnu200701 "dave @ stejonda" wrote: 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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
setting a formula to apply to all rows in a column
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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
setting a formula to apply to all rows in a column
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
setting a formula to apply to all rows in a column
Hi Dave
If you have XL2003 then use DataLists 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function/sum function | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions |