row reference

i would like to sum a series of numbers in columnB and put in cell C1.
which rows to sum depends on hard inputs in cells A1 (13) and A2 (29).  in
other words, i want to sum the numbers from B13 to B29 in this case.  i know
i can do this by setting C1 to:  =sum(b13:b29), but the rows to sum will be
changing frequently.  next time A1 and A2 might be 9 and 36, respectively,
thus summing cells B9:B36.  i do not want to manually change the formula in
C1 every time i change A1 and A2. i also do not want to create a macro.  any
thoughts?  thanks, mike allen


0
6/4/2004 3:56:11 PM
excel 39879 articles. 2 followers. Follow

2 Replies
615 Views

Similar Articles

[PageSpeed] 46

A couple of ways

=SUM(INDEX(B:B,A1):INDEX(B:B,A2))

more functions but not volatile

=SUM(INDIRECT("B"&A1&":B"&A2))

and you can also use OFFSET

-- 
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom


"mike allen" <mikeallen77@charter.net> wrote in message
news:O4JzvxkSEHA.3636@TK2MSFTNGP09.phx.gbl...
> i would like to sum a series of numbers in columnB and put in cell C1.
> which rows to sum depends on hard inputs in cells A1 (13) and A2 (29).  in
> other words, i want to sum the numbers from B13 to B29 in this case.  i
know
> i can do this by setting C1 to:  =sum(b13:b29), but the rows to sum will
be
> changing frequently.  next time A1 and A2 might be 9 and 36, respectively,
> thus summing cells B9:B36.  i do not want to manually change the formula
in
> C1 every time i change A1 and A2. i also do not want to create a macro.
any
> thoughts?  thanks, mike allen
>
>


0
terre08 (1112)
6/4/2004 5:32:56 PM
Hi

=SUM(INDIRECT("B" & A1 & ":B" & A2))
or
=SUM(OFFSET(B1,A1-1,,A2-A1+1,1))


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)


"mike allen" <mikeallen77@charter.net> wrote in message
news:O4JzvxkSEHA.3636@TK2MSFTNGP09.phx.gbl...
> i would like to sum a series of numbers in columnB and put in cell C1.
> which rows to sum depends on hard inputs in cells A1 (13) and A2 (29).  in
> other words, i want to sum the numbers from B13 to B29 in this case.  i
know
> i can do this by setting C1 to:  =sum(b13:b29), but the rows to sum will
be
> changing frequently.  next time A1 and A2 might be 9 and 36, respectively,
> thus summing cells B9:B36.  i do not want to manually change the formula
in
> C1 every time i change A1 and A2. i also do not want to create a macro.
any
> thoughts?  thanks, mike allen
>
>


0
garbage (651)
6/4/2004 5:43:59 PM
Reply:

Similar Artilces:

XML Serialisation & Circular References #2
I have been able to get simple circular references to be serialized in xml by using the ImportTypeMapping method on the SoapReflectionImporter class. But I am unable to serialise circular references when the circular reference is contained with in a collection class, specifically I am using a custom ArrayList object. I keep getting a StackOverFlow Exception from the XmlSerializer class when attempting the serialisation. The classes are: Class A, Class B - this is derived from System.Collection.ArrayList Class C. Class A contains an instance of B Class B contains multiple instances of C Cl...

"Avoid Inserting Rows / Columns"
Hi, Is there anybody to help me. How can i avoid inserting New Rows Columns in an Excel worksheet ?. Thanks in advance. Goku -- Message posted from http://www.ExcelForum.com Your question makes no sense at all. What are you trying to do? What i the problem you are having and what is your reason for not wanting t insert rows or columns? Are you wanting to add some type of protectio to the worksheet? Be detailed in your explanation and someone will tr to help you. Rolli -- Message posted from http://www.ExcelForum.com One easy possibility is to put a value in cell IV65536. Then hi...

filter rows rather than columns in Excel 2003?
can this be done? I have 12 entries in a row...need the top 10 averaged, lowest 2 discarded. Lynne, I haven't found a filter row solution yet, but this may help to start with anyways. Select your data and choose to copy it, but instead of just pasting it, do a paste special and choose the checkbox at the bottom to "transpose". That will take your row format and change it to a column format that you can then use autofilter on. When you're done, you can do the same thing in reverse. Copy the data and Paste Special and transpose again. Hope this helps as at least a t...

referring to a cell 7 rows off
Hi, In my spreadsheet I have numbers in column A. In column B I want only a selection of the numbers from column A (every seventh number) Is there a way to ceate formulas in column B like te following: =A1 =A8 =A15 without having to write every formula. Any help is greatly appreciated Sybolt B1: =INDIRECT("A"&(ROW(A1)-1)*7+1) and copy down -- HTH Bob Phillips "sybmathics" <s.hoitinga@hccnet.nl> wrote in message news:43351254$0$161$3a628fcd@reader1.nntp.hccnet.nl... > Hi, > > In my spreadsheet I have numbers in column A. > In column B I wa...

Having a row adjust in height to accommodate text entry
In a form that I'm developing I have 4 columns and 5 rows. I would like for the row height of all rows to automatically adjust to accommodate the text entry in any given colum/cell. Is there an easy way to accomplish this? Will it make a difference whether the worksheet is protected or not? -- John On Thu, 7 Jan 2010 10:50:06 -0800, John wrote: > In a form that I'm developing I have 4 columns and 5 rows. I would like for > the row height of all rows to automatically adjust to accommodate the text > entry in any given colum/cell. Is there an easy way to ...

Comparing and Deleting rows
I have two spreadsheets. One is like this: _Computer_|_Date_ The other is like this: Computer | Last Used[ The problem is that the two lists are different, but some computer exist in both. The first list is a complete list, the second one is subset of the first. How do I extract rows that are common into a separate spreadsheet -- Message posted from http://www.ExcelForum.com what do you mean by extract. from which file. do what after extraction. why 2 files instead of 1 file-2 sheets. >-----Original Message----- >I have two spreadsheets. > >One is like this: > >...

Worksheets automatic references
I need a way to automatize the change of a worksheet name in a formule, for exemple: =Sheet1!A2*1,2% (formule 1) This formula is done manualy, but, if I itroduce a new sheet in my file, I'd like to put a name in a celule and get reference to the new sheet, for example: The name "Sheet10" is taped in the celule A1, the celule for reference. In the celule B3 I have the formule 1, and I want that the formule is changed to "=Sheet10!A2*1,2%" automaticaly. Is it possible? Can I do it without a macro? Thanks, CHK =INDIRECT("'"&A1&"'...

How do you select two cells in different rows and columns with ou.
I was taking an assessment quiz and the question was "Move the cursor to cell F5 without deselecting cell C2." This was to see how well I knew keyboard shortcuts so I have to do this without using the mouse. I got the question wrong, but I can't figure out the right answer. Hi hold down the SHIFT key -- Regards Frank Kabel Frankfurt, Germany M.G wrote: > I was taking an assessment quiz and the question was "Move the cursor > to cell F5 without deselecting cell C2." This was to see how well I > knew keyboard shortcuts so I have to do this without using...

Add a row to the bottom of a worksheet.
I have a macro that summaries data on sheet1, I then want it to append this data in the form of a new row to the bottom of sheet2. Is there a formula that I can use to locate the next blank row, or what is the best way to achieve this please. Maybe you could just copy and paste that row in your macro. Option Explicit sub testme() dim DestCell as range dim RngToCopy as range 'your code to get the summaries 'whatever row holds the set rngtocopy = worksheets("sheet1").range("a999").entirerow with worksheets("sheet2") set destcell = .c...

Macro help
Hello I have recorded a macro to automatically import a file and then run a number of filters through it and to display the results in a separate worksheet. I do not have any Visual Basic knowledge, so am just using the "record macro" function. So far so good, but now I've run into a problem. How do I edit my macro so that it can automatically replace the contents of cells in a particular column in as many rows as are required? EXAMPLE: Let's say the macro runs a custom filter in Column X to show all the rows which have a value of either "A" orf "B" i...

How to move rows in a Sheet?
Hi, I'm trying to do the following: If a cell in a row has a certain value (always the same), it should move to the bottom of the sheet (after the last row). If it has another value, it should move on the top of the row. How to do this? Thanks! -- Ekser ------------------------------------------------------------------------ Ekser's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11400 View this thread: http://www.excelforum.com/showthread.php?threadid=469096 Why not simply add a helper column with a simple formula to return TRUE or FALSE if that valu...

Delete a Row if the Cell is empty
Hi i am new to excel but i am required to make a sheet and i ned to know how to remove a row from the page if a certain cell value is 0 or blank. I need this to happen just before i print the worksheet. would i use a macro for this or something else? Thanks Ian Look at Auto-Filtering; It will allow you to condense data down to only what you want to see (and Print). "Ian Holm" <ianholm@cogeco.ca> wrote in message news:qU9od.11369$hp3.1543938@read2.cgocable.net... > Hi i am new to excel but i am required to make a sheet and i ned to know how > to remove a row fro...

Addition of rows in Excel sheet
Is this possible to add more than 65536 rows in Excel sheet? No "Neeraja" <Neeraja@discussions.microsoft.com> wrote in message news:5D996527-2454-4683-A89C-1C1B8BDD9435@microsoft.com... > Is this possible to add more than 65536 rows in Excel sheet? No, not possible -- Regards Ron de Bruin http://www.rondebruin.nl "Neeraja" <Neeraja@discussions.microsoft.com> wrote in message news:5D996527-2454-4683-A89C-1C1B8BDD9435@microsoft.com... > Is this possible to add more than 65536 rows in Excel sheet? No. Use a new worksheet or use Access. -- K...

allowing rows to break across pages
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel how can i get a row to break across two pages? i can do it in word, and i knew how to do it in excel 04, but i can't figure out how to get it to work in excel 08. what i'm trying to do is the equivalent of the &quot;allow rows to break across pages&quot; function for tables in word (table&amp;gt;table properties&amp;gt;row). there is no similar function in excel. essentially, what i am trying to do is prevent excel from moving a row to a new page once it reaches a certain length. in other wo...

rank by number of rows
Hello, using sql 2008 With the ranking functions can you Rank by number of rows declared by a parameter? For example: To break up a select result set in batches and do something with a column based on the batch number? Thanks gv Here is example, sorry I didn't include: DECLARE @RANKTABLE TABLE (RowID INT IDENTITY(1,1)NOT NULL,Dept INT NOT NULL,FirstName VARCHAR(50)NOT NULL,LastName VARCHAR(50)NOT NULL) INSERT INTO @RANKTABLE VALUES (1,'Jo','Smith'),(1,'Carol','Snuck'),(1,'Charlie','Bo'),(1,'Dave',&...

Tree selected needs to be identified in related row/unit in row fo
Within the Row Format, when you link a tree format in the related row/unit column, it only displays the unit and not the tree format that you selected. If you have multiple different, but similar, tree formats it is difficult to quickly identify which tree was chosen without doing a drop down of the field. However, if one row had mistakenly used the wrong tree format, it would be difficult to go through each row to verify. We would like to see the Tree Format being used prior to the actual unit description that is currently entered. Thanks! ---------------- This post is a suggestion...

Logical IF return a cell reference
I want to use a logical IF formula to return a cell range SUM, or at least a cell reference. Can't seem to figure it out. It is always better to post the formula you tried explaining how the data is arranged. 'The below formula returns the value from cell B1 if A1 = 1 or otherwise returns blank =IF(A1=1,B1,"") The below formula will return the sum of cells B1:B10 if there is a text "Yes" in cell A1; or otherwise returns blank =IF(A1="YES",SUM(B1:B10),"") -- Jacob "Larry G" wrote: > I want to use a logic...

Inserting rows #5
I have a spreadsheet that is approx. 200 rows long and I need to insert a new row after each existing row. Please help! Thanks for your time! ASAP Utilities has a feature that does this nicely...........ASAP Utilities is a Excel add-in, and is available free at www.ASAP-utilities.com Vaya con Dios, Chuck, CABGx3 "Linda T" <Linda T@discussions.microsoft.com> wrote in message news:E60F5C65-5C65-4127-BFA0-1F07967D4D31@microsoft.com... > I have a spreadsheet that is approx. 200 rows long and I need to insert a new > row after each existing row. Please help! > >...

Validating sum of 2 cells, same row
Hi- I have created a row with 2 categories and 2 numerical selection lists The numerical selection list represents a % allocation and goes fro 0-100 in increments of 5 and the goal is to apply a percent of tim spent on activities in each category. The sum of the two %allocatio cells should equal 100 - is there a way to confirm/validate this befor the user leaves the row?? eg Row 1 Category 1 %allocation Category2 %allocation Row 2 Activity 1 0-100 (selection list) Activity 2 0-100(selection list) Thanks -Ji -- jgkoci ------------------------------------...

Averaging only the non-zero entries in a row?
Office Excel 2007 Is there a way to find the average of column of figures EXCLUDING any row that has a zero in it? I have a column of figures. Each row corresponds to a day, and each day a new row is added. Some rows have a zero value entered, while the others have a non-zero number. Finding the average of the entire row is, I know, easy. =AVERAGE(A1:A100), for example. However, is there an easy way to exclude cells within the A1:A100 range that have a zero, so that the I get the sum of the non-zero rows, divided by the number of rows with non-zero entries? Many thanks. Ken Isaacson...

Adding Fix row to query result
Hi all I would like to do following: I have a query which returns rows out of a table, nothing fancy, just a normal select statement. i now would like to add a row at the beginning which would always be fix (for example a "*") my query result: 1111 2222 3333 4444 what i want: * 1111 2222 3333 4444 I was wondering if that is possible or not, otherwise i would do it with VBA. Thanks for any Input. Carlo "carlo" wrote: > > I would like to do following: > > I have a query which returns rows out of a table, nothing fancy, just > a normal select statement....

Hide A row based on a value in that row?
I would like to hide Rows that are not going to be needed. These rows are going to have a #DIV/0! in a cell. Is it possible to hide rows that contain these cell values? How about applying Data|filter|Autofilter to your range (or column???). then you could use a custom filter to show the values that don't equal #Div/0! Rusty Shackelford wrote: > > I would like to hide Rows that are not going to be needed. These rows are > going to have a #DIV/0! in a cell. Is it possible to hide rows that contain > these cell values? -- Dave Peterson ec35720@msn.com SWEET THANKS! &qu...

Refering to subforms on tabbed controls
Hi I have a main from on one tabbed page and a "daughter" form on the other. If I select a record on the main form, can I then switch to the second tab page and add records so that they are linked to the record on the first tab? Thanks I'm not sure I understand you. You can't have a "main form on a tabbed page". Any form embedded in another form, whether it is on a tab control page or not, is a SUBFORM. Do you mean that you have a tab control on your MAIN form, where one page contains data fields from your main form's Recordset and another page contains...

How to Delete Rows Based on Conditions? #2
Dear All My data's supposed to look like this... No|Status|Count 1|Used|12 2|Unused|24 3|Available|23 4|Unused|0 5|Used|45 I only need those that are "Unused" and >0 which in this case, th macro should delete all rows except for No.2 because it is unused an >0 (24). Can you help me with the code please...? I was not able t write a code that would work... cheer -- Message posted from http://www.ExcelForum.com Do an autofilter on those 2 conditions, and delete the visible rows. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove not...

Noob-Q: does a .Update to a row with no changes do anything?
This is a little more complex than it might sound at first glance. I have two tables that are identical, one is tblOrders and the other is tempOrders. To edit a record in tblOrders I first copy it over to tempOrders and do all the work there. That way if the user Cancels out or there is some sort of error, the original data is untouched. If the user does decide to Save their changes, I open a recordset back into tblOrders (I have the original ID in temp), and then loop over the fields in tempOrders and copy them back into tblOrders. Then I call .Update on the tblOrders recordset. But ...