Trying To Sum Every Eighth Cell In A Column

Jun 4, 2009

I have a contract expenditure spreadsheet that I use for work (a much shorter example of which is attached) in which I need to add up the values in every 8th cell of a column for a bottomline total. There are too many values to use the SUM function (it's over 30). Both the SUM function and basic addition present the same problem of having to make sure that each cell is included by hand (seriously, my deputy office manager and I go blind trying to make sure it's all correct). It would make my life SO much easier if there was a way to automate it to add every 8th cell so that if I were to add a new contract block, I could just expand a range or something to include the new values.

View 5 Replies


ADVERTISEMENT

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D

Jan 31, 2008

I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

View 9 Replies View Related

Search Column E For Value - Copy Value In Corresponding Column A Cell To Another Cell

Aug 30, 2013

search column "E" for a specific text value (let's say "it"). When found, copy the value in column "A" for the corresponding row where "it" was found to another cell "F2". When copying to cell "F2", if a value already exists in cell "F2", then no nothing else copy the value. If no "it" was found in the column, do nothing. I will continue to search, but since I am new to VBA it takes me a bit to find what I specifically need.

View 1 Replies View Related

Multiply Cell In Column A If Cell In Column B Is Equal To 5?

Mar 25, 2014

How can I multiply let's say A1 to 10 if B1 = 5

or multiply next cell like A2 to 15 if B2 = 10

View 3 Replies View Related

Copy A Column Cell If V Column Cell = YES Then Repeat

Jan 26, 2010

Sub Printstuff()
Application.ScreenUpdating = False
For Each cell In Range("V168:V246")
If cell.Value = "YES" Then

'enter the name that is in The A (of the row with Yes) column in A2
'Run Sub Individual
'Print
'Run Sub View All

End If

'Next "Yes"

End Sub

View 9 Replies View Related

Multiply Every Nth Cell By Another Column Whith Merged Cells, THEN Find Column Total

Aug 6, 2009

Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".

In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.

If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.

View 8 Replies View Related

Conditionally Format Each Group Of Rows And Each Column And Color The Cell In Each Column

Nov 27, 2009

I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.

0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94

I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.

View 9 Replies View Related

Formula To Make A Cell From Column A Meet Closest Value In Column B

May 11, 2014

I've got a table of different values in column A.

I'd like the cell D2 ("order") to show up at column B next to the closest value of 35.00 of column A.

Consequently, how to make "order" meet the closest several value of 35.00?

I've attached a xlxs file with more detailed description.

Ex_closest_11_05.xlsx‎

View 14 Replies View Related

Macro - If Cell In Column Is Blank Then Input Text Into Same Row In Another Column

Sep 19, 2008

I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1.

A B C D E
1 X X X X
2 X X X X
3 X X X X X
4 X X X X
5 X X X X

View 9 Replies View Related

If Cell In Column T Is Empty Use Column U (but Make It Negative)

Mar 13, 2014

Formula for this? If a cell in column T is empty, use column U (but make it negative)??

View 2 Replies View Related

Every N-th Cell Value From A Column And Create Another Column Consisting Of Every 4th Number

Nov 5, 2008

I was wondering, is there a function that would take, lets say, every 4th cell value from a column and create another column consisting of every 4th number ?

View 2 Replies View Related

Copy Color Of Column B And Put In Respective Cell In Column A

Feb 27, 2012

I need a code to copy the color of column B and put in the respective cell in Column A. I just need the color to copy from column B to Column A.

View 6 Replies View Related

Dual Column Dropdown Where Only One Column Populates The Cell

Apr 19, 2012

I've been trying to solve with a 2 column drop down. What I am hoping to do is use two columns to aid the user in selecting the correct entry. So the first column will be a number and the second column would contain what the number means

If:
Col1 | Col2
001 | Horse
002 | Cow
003 | Dog

Both columns show when the list is selected, but when the user selects 003 | Dog, the cell would be populated with only the 003.

View 1 Replies View Related

Formula To Find A Value In Column A Corresponding To First Blank Cell In Column B?

Aug 7, 2013

A
B

1
Name
Action

2
Joe
Created

3
Bob
Approved

4
Cindy

5
Jane

6
Dave

7

View 1 Replies View Related

Column Number Of Last Column In A Row Where Cell Contents Greater Than 0

Jun 24, 2014

I have a spreadsheet with rows of data. I need a formula that will return the column number of the last column in a row where there is a value >0.

Let's say that cells A1:F1 contain values. Some have values of 0 while others have values >0. I need a formula in cell G1 that will tell me the column number of the last value >0.

A B C D E F
0 2 5 0 6 0

So the formula in G1 would return a value of 5, which corresponds to column E.

View 3 Replies View Related

Automatically Copy Column To Last Cell In Another Column Upon Open

Aug 21, 2008

I have this code that I have been using fine until today when it just stop working for me... It's a simple copy and paste using VBA Code. The code finds the last row in Column A and copies the cell to L1. Below you will find the code I'm using and the line in yellow is where I'm getting the error.

Private Sub Workbook_Open()

Range("A1").End(xlDown).Select
Selection.Copy
Range("L1").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
UserForm.Show

End Sub

This codes runs as soon as the worksheet opens and it has worked for over a year. I'm using MS Excel 2007. Please let me know what you guys think I have tried pretty much everything I can think of but i dont really know what i'm doing when it comes to VBA.

View 6 Replies View Related

Automatically Fill In Column When Certain Cell In Column Changed

Jun 18, 2009

I am trying to have some automation to my WS and what I want to do, is if a certain column (column 104) is changed, it will fill out that column with the data entered as long as the account is the same (column 5). Here is the code I tried, but realized very quickly, it puts me into a loop. This is being called by a change worksheet fuction

If Target.Column = 104 Then
If IsEmpty(Target) Then
Else
testacct = Cells(Target.Row, 5)
temptest = testacct
s_TempSwitch = Target.Value
Testoffset = 0
Do While temptest = testacct
temptest = Cells(Target.Row + Testoffset, 5)
Cells(Target.Row + Testoffset, 104).Value = s_TempSwitch
Testoffset = Testoffset + 1
Loop
End If
End If

View 3 Replies View Related

Check All Dates In Column B Then Add All Corresponding Cell In Column C

Jun 22, 2014

I am trying to check column b which hold dates then add up column c if the month is the same

B C
02-may-14 0.05
02-jul-14 1
11-Jun-14 14
20-Jun-14 0.34
26-Jun-14 15
26-Jul-14 21

This what come up with

=SUMIF(B2:B140,"02-jun-14",C2:C140)

But this will only add up dates that are 02-jun-13 I would like it to add up all dates in June.

View 2 Replies View Related

Highlight Cell In Column When Compared To Each Value Of Another Column

Jun 25, 2014

Formula to highlight a cell in a column when compared to each value in another value. If I have the columns

A B
1 1
1 2
2 3
3 5
4 7
7 8

I want the values in column A to be highlighted if the excist in column B, both of the number 1:s.

View 4 Replies View Related

Convert Column Number Into Another Column Cell?

Dec 27, 2013

A column:
18
42
55
11
65
72
80
48
..

I want to put/write to B column the some numbers (42,11,72,48..) in A column.

B column:
=A2
=A4
=A6
=A8
..

How can I achieve it?

View 12 Replies View Related

Hide Column Based On Cell Value For That Column

Jul 4, 2012

I have a Worksheet were I have a formula in cells from D5 to ALO5. That read "Hide" or "Unhide".

I would like the column to each hide individually hide if that column has "Hide" in in it. If it has "Unhide" in the column then it becomes visible again. The range of the columns match the formula cells which are "D" through "ALO".

View 1 Replies View Related

Copy Cell From Column And Paste It To Next Column And Up One Row?

Nov 15, 2013

I want to copy a cell from a column and paste it to next column and up one row. My current data looks like this:

DATA
1234

I want to make it look like: DATA 1234

View 5 Replies View Related

Postcode In A Cell In Column A Appears In Column B

Jan 30, 2009

I want to see if a value in column A appears in any of the cells in Column B and to give the answer in column C.

I am trying to match postcodes, so I want to see if a postcode in a cell in column A appears in column B. I am aware the IF function will see if it appears in the cell in the ajoining row but I need to select the whole column dataset.

View 9 Replies View Related

If Column A = 0 Then Adjacent Cell In Column B Divide By .3

Feb 27, 2009

Im wondering if this can be done in excel..

2 columns

if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30%

this is the way I was trying to do this...

=IF((O2:O6722)=0,DIVIDE(L2:L6722).3)

I have tried it several different ways and cannot get it to work - is it my formula or can this even be done because I can't reference just one cell?

View 9 Replies View Related

Find Value In Column & Return Cell On Same Row- Different Column

May 24, 2008

I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()?

Is it as simple as:

Function DocumentType()

If(...........)

End Function

View 4 Replies View Related

Compare Column Number Of Cell With Last Cell Column Number In Loop

Jan 11, 2014

I am trying to save an excel sheet to .csv format with the following macro:

[Code] ......

The following part of the code needs to be modified so that the commas appear correctly in .csv file even for null values for any given column.

[Code] .....

I have attached the Sample Sheets. Source sheet and the Result sheet. The Result sheet doesn't provide the required result. Some data are missing and unwanted commas have been added. How to correct the logic in the above piece of code.

Attached File :

Result Sheet.csv‎
Source.xlsm‎

View 14 Replies View Related

Copy The Cell Value Of Column C To Column B?

Oct 8, 2012

I wanted to copy the cell value of column C to column B how should i do it in macro. result in Sheet2.

View 8 Replies View Related

Copy Every 9th Cell Value From A Column Into A New Column

Oct 12, 2009

I am really desperate about this. I have a column of which I only want every 9th cell value listed seperately one below the other in a seperate column.

I tried defining the first two cells of the new column (e.g. first cell input is =B1 and second one is =B10, third one would be =B19 and so on...) and using the usual pull down.

In short: I want to copy values from A1,A10,A19,A28...(always +9) into B1 (from A1), B2 (from

PS: I have access to the 2008 Mac OSX version as well as to the 2007 Windows version!!!

View 6 Replies View Related

Formula To Add Column A If Column B = Cell C1?

Jun 6, 2006

a b c d
1 .2 yes yes .3
2 .5 no no .5
3 .1 yes maybe 0

So, as in the above example, I need to put a formula in D1 so that it will
take the sum of column a if column b = c1. I hope this makes sense. And
keep in mind I will be continually adding rows to this and will need it to
cover the entire row, not just part of a row.

View 9 Replies View Related

Excel 2010 :: Repeat Cell Data Based On Another Cell Column Dragging Down

Apr 24, 2014

Formula that will repeat a cell number as it drags down and as soon as the number changes. I am using helper column that shows the cell number. I need to drag this down about 1000 rows.

Excel 2010
A
B
C
1
Helper Column
Desired Result

[Code].....

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved