Sum Alternate Cells

Apr 24, 2008

I need to sum the contents of every fourth cell in row A .... ie sum(A1 + E1 + I1 ... BY1)

Can I do this via the formula bar, or do I need to use a VBA routine?

View 9 Replies


Row Range Sum Of Alternate Cells

Apr 18, 2013

I have a row range to sum only alternate cells.

How can i do this job by means of formula.

View 9 Replies View Related

Formula To Add Alternate Cells

Jul 17, 2006

I can't remember any formula to do the task. I have a time series with quarterly sales. The dates are in format given below.

01/01/1990 - first quarter of 1990
02/01/1990 - second quarter of 1990
03/01/1990 - Third quarter of 1990
04/01/1990 - fourth quarter of 1990

I need to add the sales of all first quarters , all second quarters..and so on.
Is there any formula that an elegant way or I have to do it manually?

View 5 Replies View Related

Countif In Alternate Cells

Jun 19, 2007

I have a table of data that is laid out in multiples of two columns. I have attached a simplified example. Essentially all I need to do is count the contents of every second cell when it equals a specific value. If you look at my attached example it will be clearer. how I can do this using formula? (I don't want to use VBA in this instance).

View 7 Replies View Related

How To Protect Alternate Cells Of Column

Apr 16, 2013

Is there any possibility to protect alternate cells of column see below for example. I want to lock cells B1, B6, B11 and B16 only so that no one is able to delete the average rate.

average rate0.0
average rate0.0
average rate0.0
average rate0.0

View 3 Replies View Related

Macro To Populate Alternate Cells In A Row With A Value...

Mar 17, 2009

I have a spreadsheet for work rosters. Each person on the roster has a row with info on what they're doing for a given week. Split into the am and pm session of each day.

All I want to do is be able to fill the row with the same value if they're doing the same thing for the week - eg if they're on leave, I'd like to hit CTRL-L and have the row populated with 'LEAVE' in each alternate cell, rather than typing it manually. Note that it is each alternate cell, not each cell as the alternate cell has different info.

So a row would look like this:

Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |

For the am and pm session of the five working days. And just to complicate matters, each 'cell' on my spreadsheet is actually 4 merged cells, and the alternate 'cell' that I want left alone is two merged cells! This is the macro generated when I do a simple record - it does what I want, but obviously jumps to the original row that I recorded it in whenever I run it - I need it to fill the row that I start it from. I recorded starting in cell I133:L134

View 3 Replies View Related

Fill Alternate Cells Macro, All Sheets

Dec 22, 2005

Would like a script that will go to each sheet in the workbook, and fill in light grey background, each alternate row that contains data, EXCLUDING the 1st row (header row).

View 9 Replies View Related

Find & Copy Cells & Paste To Alternate Columns

Dec 6, 2006

I have a spreadsheet that I would like to loop through column "C" and if criteria is met copy and paste A:C on sheet1 to sheet2 over multiple columns alternately. What I mean by alternately is that I would past the first row in column A6 then the second in E6 then the third in A7 and so on until all items are copied. I purposely left a blank column between both columns of information. I've tried sorting/and advance filtering and couldn't get it to work.

loop through column "C" If I have the Letter "A" copy data to column "A" and "E" alternately back an forth until I no longer meet the criteria. I start putting data on the 6th row due to header information in rows 1-5.
... If column "C" is the letter "B" copy to column I,M,Q,U

Lastly I could always have less rows of information than I do columns. SO the last column could be empty. I always sort my data by column "C" so data will be sequential.

View 9 Replies View Related

Change Colorindex Within A Range Of Multiple Cells [ Alternate Method]

Jan 8, 2010

I have tried to go around the long way to achieve this but came up with pages of pointless code .... I know there is a better way I just dont know enough about VB to do it myself ... And I know this is EASY for many :-)

Cell ranges h11 to as11 are a totals row.
If the total is 0, colorindex is set to vbpatternnone, if >= 1, then colorindex is set to vbpatterngray. Easy right ? I just dont kn ow how to do FROM/IF/DO range loops...

The code in worksheet_SelectionChange will contain the following:

1: From range h11 to as11, variable1 = application.interior.colorindex of the cell.

2: Check if the cell is >=1 or <=0 ....

3: If >=1 then set application.interior.colorindex = vbpatterngray. Go to #5.
' (This inserts a pattern over the original color of the cell)

4: If <=0 then set application.interior.colorindex = vbpatternNONE
ALSO set application.interior.colorindex = variable1
' (This clears the cell pattern and returns it to original color)

5. Repeat steps to clear cell pattern and restore color / or insert pattern for all cells from range H11:AS11

6. End sub

View 4 Replies View Related

Excel 2003 :: Copying Cells Based On Alternate Cell Value?

Sep 26, 2011

I am wondering if it possible to automate the copying of data from particular cells, based on a value in a different cell, into a different format.

So to go from this simplified table:

to something like this:



At the moment I do it all manually, and it takes forever. I am sure there must be a simple way of doing it. I am using Excel 2003, but could work in a newer version if required.

View 9 Replies View Related

Alternate Is Statement

May 12, 2009

i have attached a copy of an excel file and if you look at the end i am trying to write an IF statement that reads if J = "2-0" then column M = 12-(K) or 12--4 so answer is 16 and then column N = -column M or -16 and then if J = "2-1" then M = 6-(K) or 6--0 = 6 and then N = -M .....

so if J was "2-1" and (K) -4 then the answer given for column M would be 6--4=10 and column N = -10

View 3 Replies View Related

Sum Alternate Rows

May 6, 2008

I do an analysis that contains 100 or more rows with 1 of 2 row labels in column A; "existing" or "retrofit." There may be numerous successive rows labeled "existing" in a given place where there is no retrofit. Rows labeled "existing" contain existing equipment details such as area, equip description, operating cost. Rows labeled "retrofit" contain recommended efficient replacements with details such as area operating cost and savings. I have to display total cost and savings. Right now I use the awful method, F1+F3+F4+F5... for existing and F2+F6... for retrofit. This is a terrible method, time consuming, prone to error, etc. Is there a way to total rows with specific labels when the row labels are not consistently alternating? Would like to attach an example spreadsheet but... the permissions say I may not, for some strange reason.

View 9 Replies View Related

How To Delete Alternate Rows

Jun 13, 2014

I have data of about 3176 rows,

But after every record one row is blank,

I have to manually delete each blank row.

How to delete it in one Go.

Please Find the attachment....

View 2 Replies View Related

Reference To Alternate Tables

Jun 2, 2009

I want to reference to a range of tables depending on the value of a separate cell. Problem is that I don't know how to insert this into the range part of the lookup formula.


=vlookup(a12,NAMED RANGE AS PER CELL c9,2,false)

Cell a12 is the lookup reference which is fine.

Named range is set-up and working fine.

Cell c9 is the description of the named range - season_indices_Asda_Cream

I want that to be changeable by he user so they can change the name in cell c9 from a drop down list so that the lookup formula redirects to the alternate named range...

View 2 Replies View Related

How To Paste In Alternate Columns

Jul 27, 2003

Is there any way that i can copy a row of data, but paste each individual cell that has been copied into every other column, or every 3rd column, or every x number column.

I want to do this as i have 3 column headers repeated across spreadsheet representing each month. and would like to paste into the relevant column i.e budget actual difference budget actual difference budget actual difference.

So I can paste each value under the actual column.

View 4 Replies View Related

HLookup On Alternate Cell?

Mar 5, 2012

Im wondering if their is a formula to return the results in a Hlookup but adjusted for the cell 1 cell to the right?

For instance if Row 1 Column one contains "Tom Jones" that the forumula knows to return the value in row 2 column 2 instead of row 2 column 1.

View 1 Replies View Related

One Column Into Two Using Alternate Rows?

Nov 29, 2013

I have a single column of data and need to convert it into 2 columns, by alternating each row:






want to convert to:




I've seen a previous post from 2007 where someone asked for a formula to do the reverse of this. 'Domenic' provided a formula =INDEX($A$2:$B$6,INT((ROWS(D$2:D2)-1)/2)+1,MOD(ROWS(D$2:D2)-1,2)+1) which converted






how to reverse this formula?

View 1 Replies View Related

How To Delete Alternate Rows

Nov 26, 2007

I have a huge excel file and every row is repeated, e.g.:

john smith 10 23
john smith 10 23
bob jones 11 22
bob jones 11 22

So I want to delete every second row.

Is there a command to do that?

View 9 Replies View Related

Alternate Way Of Displaying Data

Jan 25, 2009

I have been using a lot of sumproducting lately with multiple conditions to extract data. Lately I have noticed that though it is a good way to extract data there is a lot of calculation time involved in it. The Excel workbooks that i make are in a database like format where there is 1 sheet usually a data dump which has data from one column to the 200th column and rows being filled with data points till the 10000th row. Data headers in the columns are usually like Date(ColumnA), Tenure(ColumnB), Person, Type, etc and then from Column Z onwards there are columns which contain Data in the form of numbers like Number of cases, Number of this and number of that.

Now usually when creating a dashboard of this data for performance management I use the sumpoduct formula to retrieve data. It normally has conditions in it like for some given date ranges, Tenure ranges, People ranges extract x data for me. For Example something like this


View 9 Replies View Related

Select Every Other Row :: Alternate Rows

Apr 5, 2002

Im trying to find a way select several rows at the same time but starting at say row 3 and then alternate rows so rows 3,5,7,9 etc

View 9 Replies View Related

Alternate Formula For Sum Indirect

Feb 14, 2007

I have tried to apply '= SUM(INDIRECT("A2:A10"))' formula to do the SUM at cell A11. But, if I add two more Rows, then my formula moves down to cell A13 but numbers in Cell A11 and A12 does not get added to the total. How can I avoid that? I have reserached this site extensively and could not find an archived solution.

View 6 Replies View Related

Highlighting Alternate Weeks On Spreadsheet?

Dec 9, 2013

I would like to know how I could highlight Alternate Weeks on my Spreadsheet.

I am currently highlighting Weekends with Conditional Formatting. I need to change this to highlight alternating weeks Starting with the first Sunday to next Saturday, then skipping a Sunday-Sat and so on.

My Dates are in row C2:AH2 and days of the week C3:AH3.

I would like it to Highlight from row 2 to 52 on the appropriate days

View 9 Replies View Related

Delete Every Alternate Rows In Excel

Jun 25, 2014

How to delete every alternate rows in excel.

Eg. I need to delete even rows in my spreadsheet, (row 2, 4, 6 etc..)


How to delete rows that contain both text and number but start with text then number.

Eg. My data as below:


Result: what i want


View 3 Replies View Related

Limitation Of 7 Nested IF Statements - Alternate

Apr 27, 2007

I have a workbook with two worksheets, Sheet1 and Sheet2. Sheet2 contains a table of values that need to be input into a cell on Sheet1, pending the results of comparing two other cells on Sheet1. I have 8 possible variations resulting from that comparison and I cannot make this work as the IF statement limits you to 7 deep.


A1 (text string value) = LOWER
B1 (text string value) = L1
C1 (currency with no decimals) = Sheet2!Somecell (decision of which cell to use depends on combination of A1 and B1)

A1 can be either the string "LOWER" or "MIDDLE". B1 can be the strings "L1", "L2", "L3", or "L4". The strings in B1 are not cell references, but simple text. This leads to four variations for a row that has "LOWER" in it's A column, and the same for "MIDDLE" - totaling 8 possible combinations.

Depending on the combination, I need to input a number from Sheet2 and that number is different for each unique combination of the eight possibilities. There is no mathematical calculation taking place on Sheet2 - just an "if x and y then z" decision on Sheet1. I will use the value of Sheet1!C1 in other math functions on Sheet1.

View 9 Replies View Related

Copy & Paste In Alternate Columns

Jun 23, 2006

I'm trying to copy and paste range in alternate columns from one worksheet to another. I can record this macro, but I believe it'll be really long because I have 21 alternate columns to copy and paste. What I'm trying to do:

-copy range B9:B41 in workbook 'Channel OU template' then paste values only in range BI9:BI41 in workbook 'final'
-copy range D9:D41 to range BK9:BK41
-F9:F41 to BM9:BM41
..and so on until the last column AP9:AP41 to CW9:CW41

Basically it's just simple copying and pasting from alternate columns. This is the really basic code that I have just for one column:

Sub copy()
Windows("Channel OU template").Activate
Range("bi9").PasteSpecial xlPasteValues
End Sub

View 8 Replies View Related

Fill Alternate Rows With A Color

Jul 18, 2006

is there any easy way to fill alternate rows of a worksheet with a particular color? I have a worksheet with 175 rows and alternate rows are to be filled with green!

View 4 Replies View Related

Alternate Button Macro & Caption Between 2

Apr 23, 2008

I have a worksheet with 2 buttons labelled "Hide" and "Show". As the names imply, they allow the user to hide or show parts of the worksheet. I would like to combine them into one button and have the button label and the associated macro change with each press of the button. Here is what I have so far;

Selection.EntireRow.Hidden = False
ActiveSheet.Shapes("Button 20").Select
Selection.Characters.Text = "HIDE LEADS"
Range("A1").Select 'is there a better way to remove the focus from the button than selecting a cell off the button?
End Sub

Selection.EntireRow.Hidden = True
ActiveSheet.Shapes("Button 20").Select
Selection.Characters.Text = "SHOW LEADS"
End Sub

These macros change the label fine after hiding or unhding the rows but I can't find the proper terms to use to change the macro associated with the button (if there is one?)

View 4 Replies View Related

If Row Contains Non Empty Cell Copy To Alternate Worksheet

Sep 11, 2013

When the worksheet is changed;

-For each row between A3 and A5000
-If cell in column L is empty
-Do Nothing and move on to next row
-Otherwise if cell in column L is not empty
-Copy entire row to alternate sheet, and delete row from original sheet.
-After all rows between A3 and A5000 have been checked, sort alternate sheet in ascending order based on the contents of column A.

The issue is that the code keeps skipping some rows that should be copied, possibly due to the 'for each' command not liking how I'm deleting rows (maybe?)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim lRow As Long
lRow = Range("A3:A5000").Rows.Count

[Code] .....

View 2 Replies View Related

Alternate Colors On Stacked Columns Plot?

Feb 19, 2014

I'm trying to find a way to alternate between two colors (white, grey, for example) in a stacked columns plot. Each stacked column should begin with a grey layer then white, then grey and so on. These plots will be created and changed frequently so I cannot manually do this every time.

View 1 Replies View Related

Shading Alternate Unhidden Rows In Excel?

Mar 5, 2014

I have a file that contains multiple rows of data. I built a macro that will then hide rows with a $0 balance. When the macro is completed, my spreadsheet is only showing the lines with a $ amount. (The $0 rows are hidden)

What I'd like to do is then format the unhidden rows so that each alternate row is color coded. This will separate the rows visually so I can more easily follow the rows across to view $'s per line.

I've attached a sample spreadsheet (it does contain the macro to hide the $0 rows). How can I then format the remaining rows with alternate shading?

View 4 Replies View Related

Copyrights 2005-15, All rights reserved