Writing Formulas to Include All Rows in Worksheet

What is the proper technique for writing a formula that includes all 
rows in a worksheet?  The number of rows in the worksheet change 
frequently.  So, for example, if I wanted to sum all of the rows from A1 
through A1000 today, but A1 through A1100 tomorrow (because of new rows 
added), I would have to update my formula to include the new rows.  I 
could write my formulas to include rows with no data in them (e.g., 
=SUM(A1:A1048576)), but is there a better way to write a formula that 
automatically "expands" as new rows are added?

Thank you in advance,

-- 
Sean
0
6/9/2008 2:37:45 PM
excel 39879 articles. 2 followers. Follow

4 Replies
463 Views

Similar Articles

[PageSpeed] 44

 
=SUM(A:A)
-- 
Jim Cone
Portland, Oregon  USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Fao, Sean" 
<enceladus311@yahoo.comI-WANT-NO-SPAM> 
wrote in message 
What is the proper technique for writing a formula that includes all 
rows in a worksheet?  The number of rows in the worksheet change 
frequently.  So, for example, if I wanted to sum all of the rows from A1 
through A1000 today, but A1 through A1100 tomorrow (because of new rows 
added), I would have to update my formula to include the new rows.  I 
could write my formulas to include rows with no data in them (e.g., 
=SUM(A1:A1048576)), but is there a better way to write a formula that 
automatically "expands" as new rows are added?
Thank you in advance,
-- 
Sean
0
6/9/2008 3:00:37 PM
Also, it depends on what you need to do, so for some advanced
techniques:

1. Use the UsedRange property to get the object for an entire range,
from which you can set the value to an array, count the number of row
or columns, or simply use as an object.

2. Use an equation to define a range of values, such that if the
number of rows increases, the size of the range increases, and that
expands the columns included as well:

     =3DOFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

3. To do something similar by row, use the row number, as in the
COUNTA(Data!$1:$1) to count the number of contiguous columns in the
first row.

James Igoe

http://vba.wikidot.com/
http://code.comparative-advantage.com/


On Jun 9, 10:37=A0am, "Fao, Sean" <enceladus...@yahoo.comI-WANT-NO-SPAM>
wrote:
> What is the proper technique for writing a formula that includes all
> rows in a worksheet? =A0The number of rows in the worksheet change
> frequently. =A0So, for example, if I wanted to sum all of the rows from A1=

> through A1000 today, but A1 through A1100 tomorrow (because of new rows
> added), I would have to update my formula to include the new rows. =A0I
> could write my formulas to include rows with no data in them (e.g.,
> =3DSUM(A1:A1048576)), but is there a better way to write a formula that
> automatically "expands" as new rows are added?
>
> Thank you in advance,
>
> --
> Sean

0
james.igoe (28)
6/9/2008 6:44:35 PM
james.igoe@gmail.com wrote:
> Also, it depends on what you need to do, so for some advanced
> techniques:
> 
> 1. Use the UsedRange property to get the object for an entire range,
> from which you can set the value to an array, count the number of row
> or columns, or simply use as an object.
> 
> 2. Use an equation to define a range of values, such that if the
> number of rows increases, the size of the range increases, and that
> expands the columns included as well:
> 
>      =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))
> 
> 3. To do something similar by row, use the row number, as in the
> COUNTA(Data!$1:$1) to count the number of contiguous columns in the
> first row.
> 

Thank you very much for your reply.  This is exactly what I was looking for.

-- 
Sean
0
6/10/2008 1:35:22 PM
"james.i...@gmail.com" <james.i...@gmail.com> wrote...
....
>2. Use an equation to define a range of values, such that if the
>number of rows increases, the size of the range increases, and that
>expands the columns included as well:
>
>     =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))
....

And if there are any blanks in either col A or row 1, this approach
will fail to include the entire used range. The other suggestion,
using A:A will ALWAYS be UNAFFECTED by row insertions or deletions,
and much, much better, it doesn't involve an unnecessary volatile
function call.
0
hrlngrv1 (375)
6/10/2008 7:08:30 PM
Reply:

Similar Artilces:

How to auto hide an empty row?
Is it possible to have Excel hide a row automatically if there is no entry there? My worksheet Subtotals data on 7 rows, then creates a Total on the 8th row. If there is no data on any row, can I hide them so the Total line appears right below the last entry? For example: Subtotal 1 (SUMIF function used) Subtotal 2 (SUMIF function used) Subtotal 3 (SUMIF function used) Subtotal 4 (SUMIF function used) Empty Subtotal 5 (SUMIF function used) Empty Subtotal 6 (SUMIF function used) Empty Subtotal 7 (SUMIF function used) TOTAL (SUM of all above) --- Message posted from http://www.ExcelForum.co...

Timesheet formula
I'm wondering if anyone can give me a hand with writing a formula to calculate hours on a timesheet? I need the formula to calculate the number of hours of ordinary time, where ordinary time is paid for the first 4 hours of work or until 12 noon (ie if I have worked 11am - 1 pm there would be only 1 hour of ordinary time, 7am-3pm is 4 hours etc). I am not using 24 hour clock or am/pm but hoping that the formula will recognise whether the number written is in the morning or afternoon depending on whether start time is bigger than finish time. -- kddd ------------------------------------...

Formula question
I need help with getting a formula to work. I have a spreadsheet that add time accured and subtracts time used... what I need is for it to stop and not continue to add accured time if the max is reached. Thanks for any help offered. If the accurals are in column A and the used are in column B: =MIN(SUM(A1:A100)-SUM(B1:B100),1234) where 1234 is the max -- Gary''s Student - gsnu200909 "Roberta" wrote: > I need help with getting a formula to work. > > I have a spreadsheet that add time accured and subtracts time used... what I > need i...

Is there a way for Windows Service to Read/Write HKCU Reg key ?
Hi: I'm writing a Windows Service program which needs to read/write the HKEY_CURRENT_USER registry key for the current logged on user. I know that for a Windows service does not have concept of HKCU since it is always running in SYSTEM's context. But is there a way (trick?) for a service to access the HKCU registry key? Thanks In Advance ! Polaris Hello Try, from a user application (the one that is logged on, or the current user), to send the services the user's SID. Then access the HKCU through HKEY_USERS\users_sid\............ Hope that helps, Elias "Polaris&qu...

Help: Macro that runs various if statements, inserting rows etc
I am a newcomer to Macros and excel programming and was hoping someone could assist / provide the code to do the following: I have a +50,000 line spreadsheet, that needs to be formatted. Given the size I would like to automate this. There are 3 key formatting changes required and detailed below: n = 1 1. If Cell A(n) not equal Cell A(n+1) then insert a new row below A(n), make Cell F(insertedrow)=1, make Cell G(insertedrow)=A(n+1), make Cell H(insertedrow)=B(n+1) 2. If Cell C(n) not equal Cell C(n+1) then insert a new row below C(n), make Cell F(insertedrow)=2, make Cell G(inserte...

"real" formula
I have a large spreadsheet model to audit. There is a lot of "semiformulas" meaning formula like =4569 + (7891*0,15) and so on. I need to find a macro picking up these type of formula. I am not interested in "real" formulas created by user by means of FurmulaWizzard ( formulas concerning function names like SUM, VLOOKUP etc.) Thank you for your assistance. Jiri Cihar hi, you the keyboard shortcut ctrl+ ~ this will show formulas as formulas instead of calculations. press ctrl+~ again returns the view back to calculations. if =4569 + (7891*0.15) is calculating a value the...

Write
I am trying to change the default margins. I am requested to use the Write mode to do this. What is Write, and how do I access it? Are you using voice recognition? Margins are simply changed in the Arrange Menu in Publisher. Are you in the right newsgroup? -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "CBC" <anonymous@discussions.microsoft.com> wrote in message news:183bb01c44a40$107bd5d0$a501280a@phx.gbl... > I am trying to change the default margins. I am requested > to use the Write mode to do this. W...

excel formula 12-28-09
I have rows of inventory numbers and I need to add a single number to the number already in the cell, make sence? I am adding additional inventory to the number in the cell and I don't want to have to add the numbers. It would be clear if you can post an example. -- Jacob "Shelley" wrote: > I have rows of inventory numbers and I need to add a single number to the > number already in the cell, make sence? I am adding additional inventory to > the number in the cell and I don't want to have to add the numbers. store 001 004 005 006 ...

ClassWizard does not include some controls' ID
Dear all, I designed a dialog with controls in VC++6 MFC. I wanted to add viarials to them, but I could not find some IDs of them in ClassWizard, even I could see all IDs in Resource Symbols. Also, I add new controls into dialog, they can not be shown in ClassWizard as well. Does anyone know what reason it is. Many thanks, Yuchun Xu wrote: > Dear all, > > I designed a dialog with controls in VC++6 MFC. I wanted to add viarials to > them, but I could not find some IDs of them in ClassWizard, even I could see > all IDs in Resource Symbols. Also, I add new controls into...

Using names in a formula
Hi, I used Excel a lot some years ago and now need to use it again (Excel2000) and can't figure out how to do something that was really useful. The situation: 'Budget' worksheet has month names in the top row and account numbers in the left column. I have selected all cells and Created Names with TopRow and Left Column selected. 'Reports' worksheet has month names in the top row and account names in the left column, but not necessarily in the same relative position as in the Budget worksheet. A formula such as =May Bank_Charges (where May and Bank_Charges are names) ret...

Writing formula for excel worksheet
Item Rate1 Rate2 Rate3 A 3 4 6 B 8 5 23 C 56 5 78 Above is my excel sheet. I want to write some formula so that in every row cell having minimum rate should come bold. Like For Item A - Rate1(3) for Item B - Rate2(5) For Item C - Rate2 (5) There may be a neater way but this works. Somewhere in you sheet in a cell type =min(A1:a4), I used L1 Change this to watever range you are trying to evaluate. Then select this range and click format - conditional format - cell value - is equal to =($L$1) and apply your bold font. The lowest value in the range will then become bold. Mike "Gaur...

Formula in word 2003
create a formula in a word form. I have a situation where I ned to provide an auto response based on the number d in a bokmarked cell "Risk1" I have tried the following but it brings up an error {if { risk1 } <="4""YES"}{if {risk1}>"4"<"10""Stop"}{if{risk1}>"9"<"17""Report"} The basic requirement is to provide a different response based on a number value in form field bookmark "Risk1" which can range between 1-4, 5-9, 10-16 and 17-25 each of the above ranges need a...

write protect/write redirect/kios mode
Hey, I LOVE snapshots in virtualmachines and think it would be awesome to have something like this for my main windows install. Anyone know of any hardware/software to redirect writes to another drive/file/memory/hardware? Or hardware to write protect a drive for kios's (i found some once befor the reverted all changes on shutdown, but cant find it anymore ><) I know VSS(virtual shadow copy service) has a function to redirect on write and wondering if i might be able to use that :/ It will just make everything cleaner, and give alot of flexiblity to setting up ...

XL2003 format of row- and column headers
Hello, how can I change the font of the headers of rows and columns in a sheet? Thanks for any help Wolfgang On Oct 3, 3:26=A0am, "Dr. Wolfgang Hintze" <w...@snafu.de> wrote: > Hello, > > how can I change the font of the headers of rows and columns in a > sheet? > > Thanks for any help > > Wolfgang Format > Style... > Normal > Modify ...

Formula for calculating Variance
I need help with formulas to calculate the percentage of variance between planned miles and actual miles driven by drivers at several different locations. I am given the miles per location daily. I need to calculate weekly percentage per location. Then YTD percentage per local, then total for all locals, then YTD for all locals. Can someone help me? -- Lori Lynn ------------------------------------------------------------------------ Lori Lynn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25565 View this thread: http://www.excelforum.com/showthread.php?th...

How do I run a pivottable report w/ multiple worksheets?
How do I run a pivottable report w/ multiple worksheets as my data sources. All worksheets are from same excel file. I would be pleased if someone can advise. Thank you. If you create a Pivot Table from multiple consolidation ranges, you won't get the same pivot table layout that you'd get from a single range. There's an example here: http://www.contextures.com/xlPivot08.html If possible, store your data in a single worksheet, or in a database, and you'll have more flexibility in creating the pivot table. May-Yen wrote: > How do I run a pivottable report w/ ...

Cannot write to memory location
I can not (all of the sudden) run Publisher in the last week. When starting to run it, I get that it cannot write to a certain memory locaiton, so it is shutting down. I need to get this program working. I have read through this forum and tried the different fixes as well as the knowledge base (emptied the temp file, disabled anti-virus, etc.) but it still won't work. An example is: "The instruction at "0x77f585c0" referenced memory at "0x00000000". the memory could not be "written". Click on OK to terminate the program." The header read...

Need Formula To Identify Lowest Price in Column
I need a formula to identify which vendor has the lowest price for each item *Vendor A Vendor B Vendor C* *Item 1* $10.00 $11.00 $9.00 *Item 2* $20.00 $18.00 $21.00 *Item 3* $4.25 $4.90 $4.65 Thanks / MS +-------------------------------------------------------------------+ |Filename: ExcelSample.JPG | |Download: http://www.excelforum.com/attachment.php?postid=3520 | +-------------------------------------------------------------------+ -- ms10 ------------------------------------------------------------------------ ms...

Help with Converting Rows to Column
I have Excel 97 and have a huge text list of 1000 or so companies tha need to look like a professional spreadsheet: Here is an example of th problem I have, when I paste the large text file in to Excel, this i what I get: A B Name: Target Contact Names: Greg, Pam Established: 1997 Name: CISCO Contact Name: Fred Established: 1941 Now how could I make this list, look something like A B C Name Contact Names Established Target Greg, Pam 1997 CIS...

delete formulas but retain answer
I have used this formula SUM($E$3*E8) to give answer in cell F8 It is part of a contract document which I have to return to them In cells e3 to e8 are my rates and other info used to arrive at a total. Now I only want to return this document with the column that is now in F8 minus E3 to E8 If I delete E3 to E8 I lose the total that is in F8 (cause I will have deleted the formulas) Is there a way to retain the info in F8 but delete columns E3 to E8 thanks Am Sun, 11 Jul 2010 13:23:22 +0100 schrieb SS: > Is there a way to retain the info in F8 but delete columns E3 to E8...

how do i write checkbox to.....
This is a multi-part message in MIME format. --------------050607000103070200020407 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit how do i write checkbox1 boolean to xml in vb6 i can only do cstr( checkbox1.value) but in vb.net can't write to xml. i am doing irc chat similar to mirc or pirch chat. rw.WriteConfigInfo("User Modes", chkInvisible.Text, chkInvisible.Checked, "PirateChat.xml") in procedure events: ' Both the Key and the Value are filled ' Find the key ...

Moving rows to other sheets
I was wondering if anyone could be of assistance with my dilemma. I want to run a macro that does the following: Sheet 1 has records with column headers such as name, address, and date. If the date field is populated with a date, I want to take the entire row and move it to Sheet 2, then delete the source row in Sheet 1. However, if the date field is populated by a word such as "Dead Deal" I want to take the entire row and move it to Sheet 3, then delete the source row in Sheet 1. Thanks in advance. Edwin Assuming you are only looking for a way to distingush between the two re...

Write access to a LDAP server.
Is there any way to enable write access in Outlook to my LDAP server? The LDAP server allows this functionality, Outlook doesn't. Any plugins that can do this? Thanks. Derek ...

Pivot Table formula
I would like to produce a Pivot Table, but without using the Pivot table function. I would like to use the SUMIF but I am aware that this can only be done for a single criteria. I have used the Condition Sum Wizard but this cannot be copies down or across without returning #Value. How can I find the formula behind a Pivot table so I can type his in myself. Thanks for any assistance. Andy --- Message posted from http://www.ExcelForum.com/ As a pivot table convert I cannot understand why you would want to do things the hard way <<grin>>. Or do you mean that you want to make a ...

MULTI STORE ORDER WRITING-SIMPLIFY
Hi- We have 7 stores and need to simplify the order writing process. Is there any way to write POs for all of our stores without having to do each order separately? We would like for all orders to come up with the items we want to order, being able to change the quantities according to the store. Thank you in advance for your help. This is a multi-part message in MIME format. ------=_NextPart_000_0599_01C6CAFF.B4705610 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable J, Have you tried the 340 PO Planner worksheet? --=20 * Get Secure! - ...