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?

ADVERTISEMENT

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.

A B
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1

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.

Example:
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...
--------

Details:
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:

AB1NameLevel2Arthur2a3Briony3c4Catherine3b5David3a6Edward2a7Felicity3c8George3c
to something like this:

FGHI12a3c3b3a2ArthurBrionyCatherineDavid3EdwardFelicity

4
George

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.

EG

=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:

1

2

3

4

5

want to convert to:

1
2

3
4

5
6

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

a
1

b
2

c
3

d
4

[code]....

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
etc..

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

=SUMPRODUCT((Sheet2!$A$4:$A$4898>=VALUE($E$3))*(Sheet2!$A$4:$A$4898

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..)

AND

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

Eg. My data as below:

12345hello
hello12345
123423hehehe
kekeek11

Result: what i want

12345hello
123423hehehe

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.

Example:

Sheet1
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
Sheets("sheet1").Select
Range("b9:b41").copy
Windows("final").Activate
Sheets("ou").Select
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;

Sub SHOW_LEADS()
Rows("10:15").Select
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

Sub HIDE_LEADS()
Rows("10:14").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Shapes("Button 20").Select
Selection.Characters.Text = "SHOW LEADS"
Range("A1").Select
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?)

VB:
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

Adjusting Code To Use Alternate Cell Ranges

Apr 5, 2009

Here's a nice easy one for anybody whose used excel for more thaan a week unlike me, I'm using the following code to access a popup calendar:

View 2 Replies View Related

Colour Alternate Lines With Conditional Formatting

Jul 28, 2006

change the colours of alternate rows in order to make reading line by line easier. I know that I have used this before as a Conditional action ie:- if(mod...... etc

As always, the program I used it in has 'disappeared' so I can't check back with that.

View 5 Replies View Related

Finding Min Cell Values Excluding Zero In Alternate Columns

Nov 4, 2005

I have an array that is 1 row high by 16 columns wide.

Each cell may contain a positive value, or a zero.

I need a formula to find the "Minimum value that is greater than zero" in
columns 1,3,5,7,9,11,13 and 15.

=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
need the minimum value that is greater than zero.

If I use nested IF functions to exclude zeroes I run foul of the max of 7
allowed.

View 9 Replies View Related

Sum Alternate Columns Base On Previous Column Entry

Jun 14, 2007

I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1 - 31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.

View 3 Replies View Related

Search Column Then Update Alternate Sheet With Offset

Jun 20, 2007

Here's another question for you excel junkies to solve, while I try to understand what y'all are doing. I have two worksheets named "MASTER" and "BEDS". What I need to do, in sheet "MASTER" is go down column ("K:K"). "K:K" has unique data in it. then for each row...

"X" .value = offset minus one
"Y".value = no offset

now I need to go to sheet "BEDS" and find "Y" in column "A:A" then

from "Y" offset + 3.value = "TRUE"
"Y" offset +4.value = "X"

there should only be one instance of "Y" in column "A:A"..............

View 3 Replies View Related

Excel 2007 :: Macro For Merging Data Of Alternate Rows?

Jul 7, 2014

Currently I am using Excel 2007. I want to merge data of alternate row of a particular column in Excel.Also i want to remove merged row. E.g.

Name City Pincode
John Mumbai
John 400009
Carol Delhi
Carol 110001
Carol Pune
Carol 411079

I want output to be ----->

Name City
John Mumbai-400009
Carol Delhi-110001
Carol Pune-411079

macro/formula for the above requirement..?

View 11 Replies View Related

Conditional Format: Alternate Row Shading Based On First To Letters In The A Column

Oct 27, 2008

I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.

The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.

For instance:

Column A

CPST4
CPST5
FLADS3EC
FLADS2EC
FIL4

CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.

MacOffice Excel 08 is used so no VB code can be used.

View 9 Replies View Related

Excel Starts With Blue Window When Alternate Startup Folder Is Defined

Jul 10, 2013

I have tried to define an alternative start-up folder (my goal is to have a PUBLIC.XLSB to store Macros every one can use)

BUT: When I do this, Excel does not start with any open workbook anymore. It starts with a blue background, and need to click "New" first for a workbook to open and work in.

How do I avoid this? One solution that would work, but requires the user to have Macros activated, is to add a workbook during the opening of Excel, but I would like to avoid this.

View 7 Replies View Related

Alternate The Background Color (fill Color) Of Rows In A Spreadsheet

Aug 19, 2009

how I can alternate the background color (fill color) of rows in a spreadsheet. Say I wanted every other row to be gray starting at row 10.

View 6 Replies View Related

Formula To Find Specific Text And Replace With Alternate Text?

Mar 4, 2014

I have a worksheet with several columns. I need a formula to search column D only and each time a specific location is identified to replace that location with alternate text. Example (ORIGNAL TEXT):

Column Dtext to text help.xlsx
BIRD
FISH
DOG
BAT
BUG

I need to search that listing and each time the word BIRD is mentioned have it replaced with FEATHERS and each time DOG is listed have it replaced with TAILS Final result would look like:

FEATHERS
FISH
TAILS
BAT
BUG

All other text should stay the same and replacement text should appear in the cell of the text it is replacing. This is a sheet used by multiple people several times a day and so the Find/Replace option really won't work.

Have working on this for at least 6 months and it just isn't going to happen for me. I thought I could use a Conditional format, but that is producing no results either.

View 3 Replies View Related

Alternate Colors In Table With Table Style When String Value In Column A Changes

Feb 25, 2014

When I add a table in Excel, I can choose from Table Styles, different styles that set one color for even rows and another color for odd rows.

Is there a way to say to this Table Style that assign one color for rows that have the same string in column A in consecutive rows and another color when the string in colum A changes?

I mean,
If A1= XYZ, A2=XYZ assign blue to row 1 and 2.
If A3:A9=FTG assign green to rows 3 to 9.
If A10:A13=LLKF assign blue again to rows 10 to 13.
If A14:A22=WUR882 assign green again to rows 14 to 22.

and so on.

View 2 Replies View Related

ADVERTISEMENT