Incrementing a cell selection

How can I increment a cell selection in a formula? For example, I would like to count a group of cells, 30 at a time, going down a column.

ie if i have this formula: '=count(L2:L31)' and I want to skip down to the end of this range and count the next 30 cells, how do I do it?

I know that this will not work
 '=count(L2+30:L31+30)'

Any assistance is much appreciated, thanks,


0
milbrowne (2)
3/30/2012 7:27:51 AM
excel 39879 articles. 2 followers. Follow

2 Replies
1073 Views

Similar Articles

[PageSpeed] 14

On Fri, 30 Mar 2012 07:27:51 GMT, Mil Browne wrote in
microsoft.public.excel:

>How can I increment a cell selection in a formula? For example, I would like to count a group of cells, 30 at a time, going down a column.
>
>ie if i have this formula: '=count(L2:L31)' and I want to skip down to the end of this range and count the next 30 cells, how do I do it?
>
>I know that this will not work
> '=count(L2+30:L31+30)'

Try: =Count(Indirect("L"&2+30&":L"&31+30))

-- 
Michael Bednarek                           "ONWARD"
0
3/31/2012 1:58:43 AM
hi Mil,

place the formula on line 2 of the column of your choice and copy this formula down

=COUNTA(INDIRECT("L"&(ROW()*30)-28&":L"&(ROW()*30)+1))

-- 
isabelle



Le 2012-03-30 03:27, Mil Browne a �crit :
> How can I increment a cell selection in a formula? For example, I would like to count a group of cells, 30 at a time, going down a column.
>
> ie if i have this formula: '=count(L2:L31)' and I want to skip down to the end of this range and count the next 30 cells, how do I do it?
>
> I know that this will not work
>   '=count(L2+30:L31+30)'
>
> Any assistance is much appreciated, thanks,
>
>
0
isabelle
4/1/2012 2:47:24 AM
Reply:

Similar Artilces:

Linking cell to another excel file
How do you link the data from one cell on file1.xls to another cell on file2.xls? I need a cell to populate data from a 2nd excel file without having to open the 2nd file. Is this possible? How would I do this? Thanks, Jasper Have both files open, and in File1.xls Sheet1 Cell A1 type: = then go to WINDOW->File2.xls (this will switch to the second workbook) and select Sheet1 Cell A1 in File2.xls and hit enter. Fairly simple. That will link File1.xls Sheet1 Cell A1 to File2.xls Sheet1 cell A1 ryanb. "Jasper Recto" <jrecto99@yahoo.com> wrote in message news:#HReNk$...

Copying cells
Hi I would like to copy the contents of every 10th cell in a row in a contineous column on a separate sheet. Is there a quick way of writing the address that allows you to drag down and add a value of 10 to the cell address? Many thanks Yours Andrew J Scotland Hi Andrew one way: =INDEX('Sheet 1'!$1:$1,1,1+(ROW()-1)*10) copy down. This will get you the 1st, 11th, 21st,... column in Row 1 HTH Frank ajohns@freeuk.com wrote: > Hi > > I would like to copy the contents of every 10th cell in a row in a > contineous column on a separate sheet. Is there a quick way of writ...

Incrementing Numbers ?
Hello, I have a column with, e.g., two numbers in it. Say a 2 and the next one down is a 4. How do I select one or both so that I can simply just drag down and have a long column with: 2 4 6 8 10 etc. And, if there are already a lot of numbers in the column, must one delete them first for the scheme to work ? I tried a few methods, but just can't seem to get it to work. Also, couldn't find anything in the HELP menu for this. Thanks, Bob Select both cells then drag the bottom right corner down. The cursor turns into a small black cross when you hover over the bottom right corner ...

Adding Hyperlink to multiple values within a cell #2
What about this: Can I create one comment and assign it to multiple cells at once instead of clicking on each cell and pasting the same comment? I've decided to just use a separate cell for each numeric value. Thank -- t2tru ----------------------------------------------------------------------- t2true's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=387 View this thread: http://www.excelforum.com/showthread.php?threadid=27489 Good choice about separating your data. You'll find that you can do lots more lots easier! I don't think you'll be ...

Delete Cell Value Based on Another Cell T or F
I have cell A1 for instance that's always TRUE or FALSE from a Check Box. If the value of cell A1 is FALSE, I want it to delete any data that's in cell A3, or if it's TRUE to leave cell A3 as is. It should be easy but for some reason I'm hung up on it...thanks Does A3 contain a formula? If it does, you can use an IF() function. Suppose the formula in A3 is =B5*B6 You could write an IF() in A3: =IF(A1=True,B5*B6,"") No, A3 is simply a number input from the user. It can be anything greater than 0. If A1 is FALSE I want any data in A3 to be deleted or A3 set...

Paste Special drops cells from Word and Excell
Our church is using Publisher 2003 to do our bulletin and newsletter. When we try and use Paste Special to paste the calendar from Word 2003, it drops the last row of cells bottom of calendar (they do not show up in Publisher). If we go back to Word 2003, new document, and paste, everything is there. The same thing happens when we try to Paste Special from Excel 2003. It drops the last column of the Excel sheet. Any suggestions on how to fix this? The calendar and Excel sheet are put together by another individual ( not the secretary) and emailed to the church office. The secre...

think cell program
is anyone familiar with a program called think cell? Any thoughts? (powerpoint v 2003). Is this an add-in? thanks Sara It's very good. You can "try for free" from their website: http://www.think-cell.com/ Recent interview with one of the founders on Indezine: http://blog.indezine.com/2009/12/think-cell-conversation-with-markus.html -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/3...

Referencing Cells in a different workbooks
Hi All, I have a spreadsheet in workbook 1 with the names of staff and performance scores as % values entered weekly. In workbook 2 there are separate worksheets for each individual staff member. I am trying to create a formula for workbook 2 whereby the information entered in workbook 1 will automatically update in workbook 2. I can do it for an individual cell in workbook 2 by entering = and clicking on the cell in workbook 1 that I want to reference and hitting enter, but when I click on the cell in workbook 2 with the formula I have just entered and drag it across the spreadsheet (s...

incrementing numbers
Can anyone give me a tip on setting up a macro that will increment numeric cell each time it is run. i.e. an invoicing sheet that wil print a new number each time. probably easy heh!, I must be missing something. Thanks in advance ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Hi Public Sub AddIntoA1() Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1 End Sub -- (When sending e-mail, use address ar...

Added value to cell
Cell u4 has a set date in it I would like cell u8 to register this date plus 1 but if cell u4 is blank then u8 is to be blank. ie U4 = 20/10/05 U8 = 21/10/05 if U4 blank U8 is blank Sorry forgot to add that this will be used in mutiple cells in column U "bbc1" wrote: > Cell u4 has a set date in it I would like cell u8 to register this date plus > 1 but if cell u4 is blank then u8 is to be blank. > ie U4 = 20/10/05 > U8 = 21/10/05 > if U4 blank > U8 is blank =if(u4="","",u4+1) Format the cell as a date. bbc1 wrote: > > Cell u4 has...

Automatically resizing cells
Hi, I am trying to figure out a way that would automatically resize cell height once I enter additional information and part of the text disappears from the screen. Currently it appears that the only way to do that is to double click on the bottom border of the cell I am on. I am using MS Excel 2000. Appreciate you feedback, Jason "JJ" <Iam@cyberspace.net> wrote in message news:Ow7YDMfLEHA.624@TK2MSFTNGP11.phx.gbl... > Hi, > > I am trying to figure out a way that would automatically resize cell height > once I enter additional information and part of the text...

Fill other cells based on cell selected
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C79C40.C0C75F40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable How can I have other cells fill in with data from another worksheet = based on the cell that I select in the active worksheet? i.e. I have a list of names in column B when I select a cell containing = a name I would like that persons phone # and other info to appear in = other cells. As I scroll down the phone # and other info would change = according to the name in the active cell. The phone # and oth...

percentage of green cells in list
Hi i have a list of dates coloured either red or green in column A and i want to express the green cells as a percentage of the total list ie 12/02/10 14/02/10 20/0210 etc this list is constantly being added to thanks for any help rslc There is no built-in excel function to do this. But you can use a UDF that ...

A Named cell to be in a formula for a code
Hi! I have a named cell -> combo_chk (cell J3) This combo_chk changes because of FOR..NEXT from code but the style i always like this : 4,5,12,40,45 I have 5 columns with values in the rows A B C D E 1 12 40 41 45 5 6 10 11 40 I want to find if the 1 on column A is including in the combo_chk then if the 12 on column B is including in the combo_chk e.t.c. If it is found return '1' otherwise return '0'. So for the above example i would get for the first row -> 0 1 1 0 1 for the second row -> 1 0 0 0 1 because the values from the column B,C and E are incl...

EXCEL: Separating data from 1 cell into multiple cells
I have thousands of cells in a report with information that I need to separate into different cells. Ex. one cell: Arch | South | All Points | Extreme I need an automated way to get each of the 4 names separated by "|" into its own cell. Any suggestions? Thanks, Heather (heatherjoslyn@yahoo.com) Hi, Highlight the column, Text to columns, Delimited, next, check only the Other box and copy your | in the box beside it, next, finish "HeatherJ" wrote: > I have thousands of cells in a report with information that I need to > separate in...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Excel 2007 changes links in cells
Hello guys, when I enter link to TXT file in the cell (e.g. D:\aa\ss\ko-hs-048-332-09\11ss.txt), for some reason it changes to C:\Users\Eddie\AppData\Roaming\Microsoft\Excel\ko-hs-048-332-09\11ss.txt after some time. It seems to me it's something like Temporary Internet Files in IE... Any ideas how to keep the original link assuming that I haven't changed the location of the file? Thanks, Milan -- Milan Bortel MCP, MCAS, MCTS GOPAS Computer Training Center Brno, Czech Republic Saved from a previous post: A few people have said that this has stopped a s...

Too Many Different Cell Format
I got error message " Too Many Different Cell Format". Anyone know what caused it? Thank you. for further assistance on this particular issue, click on https://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&tid=1d4df007-3dc4-41f0-8182-aafe32548d4f&cat=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1 -- learn - n - enjoy ------------------------------------- click yes below, if it works for you "Bamboo" wrote: > I got error message " Too Many Different Cell Format". Anyone know ...

Changing ruler increments?
Is there a way to change the ruler increments, thereby changing the snap to increments or does it just default to the 16th of an inch only? I'm trying to make all text carrying boxes the same size and can come close but can't snap them into the same size. It's really close enough but I was just wondering if I can make the snaps and nudge increments smaller than they ar Thanks Hi Catt (anonymous@discussions.microsoft.com), in the newsgroups you posted: || Is there a way to change the ruler increments, thereby changing the || snap to increments or does it just default to the 16th...

separate contents of one cell into multiple cells
Hi ! Is there a way to separate a content in one cell into mutiple cels?? I have this: Portugal,KM25,2,euros,2002 what I=B4m looking for is separate all this between comma like Portugal in one cell km25 in other cell 2 in other and so. Thanks Horacio Try Text to column under Data (2003) using coma as the delimiter. HTH Regards, Howard "Horacio" <h.anastacio@gmail.com> wrote in message news:61d06312-3aee-45da-9bb0-72ef25430fe7@a32g2000yqm.googlegroups.com... Hi ! Is there a way to separate a content in one cell into mutiple cels?? I have t...

Blinking cells
Is there a way to have text or background blink (like in Word)? Hi though this is possible with some VBA code (using the OnTime method) I really would recommend NOT to use such things: - it will slow down Excel (due to the use of the OnTime method) - personally I think blinking cells shouldn't be a part of a spreadsheet If you need this you may search the Google archives for "Excel blinking cells" -- Regards Frank Kabel Frankfurt, Germany Pointer wrote: > Is there a way to have text or background blink (like in Word)? Hi Pointer! Much requested but much derided in ter...

Hyperlink many cells
I am trying to link many cells on one worksheet to corresponding cells on another. I have two worksheets, one with item price information and another with physical item information. Both sheets share a common "number" field. Each item has unique number that is referenced on both sheets. I want to set up a hyperlink so that when the when any cell in the number field in the price file is clicked, it lgoes to the cell with that same value in the number field on the other sheet. I can do this by one cell at a time by right clicking the cell, adding the hyperlink and setting...

Referencing cells
Hi, I posted earlier but got no response. I think my explanation then was too difficult to understand so I'm trying again. I hope this time it is more easily understandable In Workbook 1 "Exp_06.xls", I have a column of numbers starting on row 23 as shown. 23 1000 24 500 etc.etc. Each cell in the column is named, for example cell "A23" is named "AutoInsurance" and cell "A24" is named "AutoGasoline". The formula bar for each cell contains only the number in the cell and does not reference the cells name. In Workbook ...

my formated column is displaying one cell incorrectly
I have a spreadsheet with 4 columns; the 4th columns is a formula going across the 3 columns a+b-c = d with d preferably equally zero. When I format column d almost all of the columns that equal zero display as a dash (preference). However periodically one or two of the rows will display as 0.00 and I can't seem to get the formatting to change to the dash. I have tried to reformat all columns all other cells and to no avail. Has anyone else run into this issue and if so how have u solved it. The only way I can solve it is to actually put a zero in the cell. If the numb...

Replacing Cell Contents
Hello Folks Is there any way via formula to replace the contents of Cell A1 with a formula written in B1 or any other cell? Thanks Hi no way to achieve this with formulas. Formulas can only return values but not change other cells -- Regards Frank Kabel Frankfurt, Germany Bob Leathers wrote: > Hello Folks > > Is there any way via formula to replace the contents of Cell A1 with a > formula written in B1 or any other cell? > > Thanks Thanks for your reply. Is there then a way, via macro, to continually check to see if the value in cell A1 has changed? If so change th...