I have data in a row - A1 B1 C1 D1 etc.
And a SUM function in row 2 - =SUM(A1:C1)
I want to be able to drag this function to calculate SUM in blocks of 3 e.g.
=SUM(A1:C1), =SUM(D1:F1), =SUM(G1:I1)
I have set up a formula to sum data from specific columns in multiple sheets in the same file. The simple formula had worked perfectly until I needed the data from the last sheet. In all of the other sheets the data that I need to pull is all in consecutive rows but in the last sheet the data that I need pulled and added is in every 4th row. The sheet looks like this:
All of the columns I've filled in work fine. The ones blank are the ones I am running into issues with. When I drag down the formulas excel pulls the next consecutive rows, which is perfect for the first four sheets I am summing, but for the fifth sheet (I'll ref the Test1 column) I need it to pull 'Wayne 2014'!C6 and then 'Wayne 2014'!C10 in the next row and so forth.
Date
All Test
Test1
Test2
Test3
Test4
$ Amt.
Jan.16
=SUM(C2+D2+E2+F2)
[Code] ........
The first four sheets look like this, which is why they work fine:
Date
All Test
Test1
Test2
Test3
$ Amt.
Jan. 16
=SUM(C2:E2)
[Code] ........
The fifth sheet looks like this: I only want the formulas from the first sheet to pull the data from the dated rows in this sheet.
Date
All Test
Test1
Test2
Test3
$ Amt.
Jan. 16
=SUM(C2:E2)
=SUM(C3:C5)
[Code] ......
Is there any way in Excel 2010 to get it do what I want? (I am not sure how that one cell became outlined and I do not know how to fix it.)
I am trying to link the cells up in a new workbook the problem is the data I need is setup in a way the formula needs to skip every other row.
For example:
Attached is a sample worksheet. I have data in column A that I want to drag fill in column C. In column C, once the first 2 results in column A are entered, I would like a blank cell and so on.
testdv.xlsx
I have the following formulae in seperate cells were A1, A2 and A3 are the cells
A1 =MAX(Data!C2:C3)
A2 =MAX(Data!C4:C5)
A3 =MAX(Data!C6:C7)
I would like to drag the cells so that automatically the cells below A3 are updated as follows
A4 =MAX(Data!C8:C9)
A5 =MAX(Data!C10:C11)
etc up to 600 rows
Unfortunately it is not being updated in that order. Do you have any suggestions how it can be done.
i need a macro that will simulate the little corner crosshair that you drag to fill in and increment formulas. i need to fill down on sheet 2, columns a thru m a number of rows equal to sheet 1,column b, count -1.
View 9 Replies View RelatedI would like to be able to auto fill weekdays (skipping Saturday & Sunday) in a month. For example, my spreadsheet would look like this if cell A1 contains12/01/09:
DATE WORKDAYS TASK
1-Dec 1 AP CLOSE - CAPITAL & POS ITEMS
1-Dec 1 POS MONTH END
1-Dec 1 HEALTH INSURANCE
2-Dec 2 DEPR SYSTEM RUN; INCL-SOFTWARE AMORT
2-Dec 2 SPECIAL EQUIPMENT
4-Dec 4 PRELIMINARY ENERGY REPORT
7-Dec 5 AP CLOSE - ALL OTHER ITEMS
The DATE column needs to return the date for WORKDAYS listed...Monday through Friday only. The first workday in Dec 2009 is 12/1, the fifth workday is 12/7, and so on.
is there any way where instead of selecting a cell one by one and run a macro, i can just drag the cell with the macro across other cells like how a formula runs?
View 5 Replies View RelatedI have a spreadsheet where a column has many cells being empty and others with values. I need to use copy-paste skip blanks to another column so it only overwrites cells that contains values. BUT The cells in the column appears to be empty, not blank, when I try use the copy-paste skip blanks it doesnt work. However, when I press delete in every empty cell the copy-paste skip blanks works for those cells.
Do you got a fast method to make all the empty cells blank?
Drag & Drop Example.xlsx
In the example attachment on the weekly tab, you will see I have daily totals and a sum for weekly. On the summary tab I have the columns going down with a link to the weekly totals.
I have over 5 years of data in this format and don't want to link each cell in one sheet to another. If I drag and drop, obviously the weekly doesn't come down correctly.
Is there another way as I don't want to have to do this for over 250 weekly totals (and counting) manually.
I am trying to link from one spreadsheet to another and drag the cells down to copy the forumula, however I want to drag vertically on Sheet 1, and Copy the values horizontally from sheet 2.
For example, in sheet 1 I link cell A1 to equal cell A1 in Sheet 2. If I drag down the formula in sheet 1 A1:A10 then it will copy the values in cells A1:A10 in sheet 2.
Now what I want it to do is for me to drag the formula in cell A1 down to A10 in sheet 1, but for this to return the values of A1:J1.
I would like to copy a large selection of hyperlniks down a column of 200 plus cells and would the hyperlink to increment as I drag the cells downwards.
Is there a simple way to copy and paste down a large selection of cells, so that I do not have to edit each hyperlink individually
Have enclosed example.
I have data in every cell within a column. I want to pull data from every 7th Cell.
Ex.
Cell CP7 needs to reference AG10
Cell CP8 needs to reference AG11
I want to put in cell I3 the value from cell C1, to I4 the value of C49 and so on. How can I do it?
View 5 Replies View RelatedI have a spreadsheet where I often have to exchange pairs of cells. I currently do this by dragging Cell A to a blank portion of the sheet, dragging Cell B to Cell A's former location, then dragging Cell A to Cell B's former location. I was just thinking that I might be able to use VBA to allow me to drag Cell A to Cell B and have them "trade places".
I don't even know what events are involved in the dragging process so I don't know where to start.
I am making a sheet that has many colums of data that I want to sumup colum wise. I am doing is using this macro ..
=============================================
Sub Add_Totals()
For Each NumRange In Columns("C").SpecialCells(xlConstants, xlNumbers).Areas
SumAddr = NumRange.Address(False, False)
NumRange.Offset(NumRange.Count, 0).Resize(1, 1).Formula = "=SUM(" & SumAddr & ")"
NumRange.Offset(NumRange.Count, -1).Resize(1, 1).Value = "TOTAL="
Next NumRange
End Sub...........
Any way to have the date auto increase 7 days when we drag/copy down the cells. For example, I have the following:
03/31/13
04/07/13
04/14/13
So, when I copy down (let say hundred cells down), the day should show 7 days apart. Is there a formula?
I Have the following macro conducting web queries. As the list of websites is spread out I need to get the macro to skip over blanks?
VB:
Sub Top5Investors()
'
' Top5investors Macro
'
'
Dim Erw, Frw, Lrw
Frw = 1
Lrw = Range("A" & Rows.Count).End(xlUp).Row
For Erw = Frw To Lrw
[Code]...
The spreadsheet that i use includes a table that some of the cells are displayed as blanks( include an IFERROR function ) I need to calculate the XIRR for a range of cells in a row that includes some of these "Blank" cells
View 2 Replies View RelatedI'm looking to create a formula that will skip past any blanks until it finds the latest and most up-to-date value.
Some context: I wish to return a latest estimate value to a cell (A5), and this value is updated quarterly. Let's say that the quater 1 value sits in cell A1, Q2 in A2, Q3 in A3 and Q4 in A4. I wish cell A5 to display the latest estimate as soon as a user updates it on a quarterly basis, but default to the previous quarter's estimate if that latest view is unavailable.
I've tried using some IF(ISBLANK...) combinations but am getting nowhere !
NB want to try and avoid Macros across this worksheet so a formula solution would be best.
I have this code to sort numbers from smallest to largest but i need it to ignore blank cells.
Sub sort1neg()
'
' sort1pos Macro
'
'
Range("a4:aq174").Select
ActiveWorkbook.Worksheets("sort").Sort.SortFields.Clear
[Code] ........
I have a range that I need to copy to a new worksheet. It basically looks like steps going across the sheet. Whenever I skip blank cells during the paste to the new sheet Excel does not skip anything. Any ideas on how I would shift all data to be on the left side of the without copying the blank cells.
View 5 Replies View Relatedhow can i skip blanks or zero cells
I have a column with data like this: ....
I`m trying to do is paste a formula accross multiple columns whilst skipping the empty cells. I tried using the "skip blanks" tick box but it still filled in the empty cells??
View 2 Replies View RelatedI have a spreadsheet with temperature data, all in one column (D), that was recorded every 10 minutes for several months. In two adjacent columns (E & F), I would like to record the daily max and min temperatures. I can manually do this with MAX and MIN formulas in column E and F respectively, MAX(D1:D144). But when I copy this formula to the next row, I get MAX(D2:D145) when what I really want is MAX(D145:D288)....and so on...
View 1 Replies View RelatedI have a column of data that I want to display as a chart. However, there are some blank cells in the column. When I use a simple line chart, the chart drops the line all the way down to zero for the blank cells. If the blank cell is B4 in column B, is it possible to make excel ignore that cell and connect B3 and B5 with a straight line?
View 6 Replies View RelatedI am facing a problem in pasting.
Col A Col B
aa
bb
cc
dd
e
ef
fg
h
g
h
my data is having blank cells. when I am trying to paste it in other column it is getting pasted as it is. I don't want to copy the blank cells. It should be like Col B.
I am trying to run an action on a series of cells that inserts rows whenever there are blank cells (resulting from the formula) in column.
The problem: When I have a situation where there are not blank cells, the the .SpecialCells action does not work. I have tried the On Error Goto, but I have multiple equations that can have this occur and I have only been able to use that feature once in a macro.
LR = ActiveSheet.Range("B65536").End(xlUp).Row
Set Rng = Range("A2:A" & LR)
With Rng
.FormulaR1C1 = "=IF(OR(AND(RC[16]="""",R[-1]C[16]=1),AND(R[1]C[16]="""",RC[16]=1)),"""",1)"
End With
Set Rng = Range("A:A")
With Rng
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Insert 'stopped here
End With
I need to compare cell value with information from main source cell.
And I need it to skip blank, return "-" if "-" was entered.
I attached example file for your reference.
I've been trying to paste formula throughout a range of cells in the same column. Trouble is, I protect some sub- total cells and don't want the formula to paste over those sub-total formulas.
If I protect the sheet and paste the formula throughout the column including the protected cells, I would get an error message.
How can I paste the new formulas without getting into trouble of the protected cells?