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.
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 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:
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 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 am not able to drag & fill series only in one particular sheet, in other sheets this function is working fine.
View 2 Replies View Relatedthe function I'm referring to is when you have a value in a cell and you hover your mouse cursor over the edge and get the black cross with arrows on all corners. Once you drag the cell into another location, it copies the the data into the other cell.
I need a way to stop this from happening, because this causes reference errors on other sheets when users do this by accident. However, I still need to allow the user to be able to type in that cell, use the autofill option, and the Ctrl+C option. Is there a way to program a macro that can be put in the sheets to do this for specified cells or ranges?
I have a sheet where users enter a date and other details on each row.
The number of rows where data is entered can be upto 20 at any one time.
I wish to allow users to drag the cell date down for the number of rows they wish, without the date incrementing.
I've created a macro to disable cut-copy-paste and drag-and-drop (below), but when the user exits Excel, the drag-and-drop option remains turned off.
The user has to click on the Office button, go to Excel Options, Advanced tab, and re-enable the fill handle manually. How can I have my macro do this automatically when the workbook is closed?
'*** In a standard module ***
Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial
'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False ....................................
I have an excel file that contains a list of all clients and want to add an "Active Client" tab within the workbook. The list indicates some active, some inactive. The "Active client" column shows an x at 1st row or if "Inactive" is left blank. I want to auto populate all "Active Clients" to a different Worksheet without having blank rows or FALSE as the result of the current formula ...
=IF('Lead Data'!B17="√",'Lead Data'!H17)
I have class monday, tuesday, wednesday & friday but no class thursday. How can I make automatically fill in dates for weekdays only and skip thursday using excel 2013's "Fill" function then "series" ??
View 6 Replies View RelatedI need to produce a grid displaying data pulled from local references within the workbook but Excel will not auto fill the references in the right way.
i need
='Account (1)'!$C$5
='Account (2)'!$C$5
='Account (3)'!$C$5
down to
='Account (498)'!$C$5
='Account (499)'!$C$5
='Account (500)'!$C$5
is there a way to imput these references using VBA script?
I would like to have the following fill down a column. =B5 =B7 =B9 etc. Is there a way to do this without doing it manually?
View 3 Replies View RelatedOn the 'Total' sheet I'm wanting subsequent sheets to fill in on the rows. Do I have to redo the formulas for each row because it will correlate to a different sheet? I only know how to drag the formula down, but that doesn't seem to work right!
View 11 Replies View Related=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A1:C2)
If I simply drag the above formula down I end up with
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A2:C3)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A3:C4)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A4:C5)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A5:C6)
I am trying to drag the above formula so that it copies in the following manner:
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A3:C4)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A5:C6)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A7:C8)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A9:C10)
How can I achieve this result?
I have tried absolute and relative on the formula in A5 of this sample data below to get A6 to equal D2 and so forth down the sheet. Nothing works except typing in the formula for each cell....
View 9 Replies View Relatedi have a formula that needs to be dragged down 65,000 rows in excel is there a quicker way of do it than with the mouse?
View 3 Replies View RelatedI have sheets with over 40000 rows. I grouped them in 60 and I need the average for every group on the 61st (which is blank). For example, when collapsed I have visible the following blank cells:
A61
A122
A183
A244
and so on.
What I want is to enter a formula in A61 for the average of A1:A60 then drag down and have the correct values in all the rest (average of A62:A121 in A122, etc.).
I want the range to stay as i put it without it +1 when i drag the formula down my spreadsheet, below is the formula im using (for a creche in case your wondering)
=IF(G5<A8,"Baby",IF(G5<A16,"Wobbler", IF(G5<A24,"Toddler",IF(G5<A30,"Playschool","Please use DD/MM/YYYY FORMAT"))))
G5, A16, is taken from a list of dates down the side so i need these to remain constant otherwise my formula goes out.
i wanna to put check boxes in colums d4:d41 with a formula inside which beginning with this formula (=Sheet1!$D$4) in cell d4.how to copy and drag down this formula into d5:d41?
View 8 Replies View RelatedI 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
This is my formula:
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A1:C2)
My question concentrates on this part of the formula:...Criteria!A1:C2
When I drag down the formula, it changes, as usually, adding 1 to the cells address, like this:
Criteria!A1:C2
Criteria!A2:C3
Criteria!A3:C4
BUT, I want the formula dragged down, adding 2 instead of 1, changing like this:
Criteria!A1:C2
Criteria!A3:C4
Criteria!A5:C6
How can I achieve this result?
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 RelatedIs there an easy way to drag all the references to a cell in a formula quickly e.g If i have a formula like this is;
=IF(A1>0, A1+1,A1-1)
and want to drag all the references to the cell A1 to another cell in one go as opposed to one at a time?
In my worksheet i have pairs of data from A2:A18. cell A2 is paired with A3, cell A5 to A6.
In cell C2, I have the formula =IF(A2>A3,A2,A3). then in C3 I have =IF(A5>A6,A5,A6).
I would then like to be able to drag these formulas down to C7 and take the appropriate data.
So another example, cell C4 should be =IF(A8>A9,A8,A9).
I have seen many formulas for how to drag skipping a row, but couldn't find anything on pairs of data while skipping a row as well.
The worksheet is attached : TEST.xlsx
1. I have data A1, A2, A3....down a column.
2. I have one value on C2 ( constant value)
3. the formula is a very simple multiplication formula. I want to multiply every single value of column A to C2. But it needs to be in across a row that starts in
D4. So,formula needs to be in (D4, E4, F4) there are other datas in the worksheet
4. So when I input =$A1*$C$2 in D4 it calculates it fine but when i try to drag the formula across the row so [E4=$A2*$C$2, F4=$A3*$C$2...]
it repeates the same fomula as D4 and does not adjust for the increase in column number.
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...........
i noticed that when i have a formula that refers to a table, such as:
= sumifs(table1[heading4],table1[heading1],"A",table1[heading2],true)
i can copy-paste it, i also can fill (right or left) a selected range with it,
but, i cannot copy it (right or left) by dragging.
In the attached workbook, see the "P & L" tab, cells I30 to I48. The idea of the formula I have placed into these cells is to drag out the figures from column I in the "Trial Balance" tab of the workbook. A similar formula is used succesfully in the "balance sheet" tab in Column B. I don't understand why the formula doesn't work in the cells i've specified in in the " P & L" tab it seems to work for I8 and the rows immediately below it "revenue".
UK Trial Balance May 2014 V2.xlsx
I am trying to drag down a formula (see below). the cell reference A6 and E6 is not updating to A7 and E7 etc etc when i drag the formula down using the corner thing. it remains constant at A6 and E6.
=HYPERLINK("#INDEX('Shipping Tracker Week X'!$F$4:$F$3000,MATCH(1,('Shipping Tracker Week X'!$B$4:$B$3000=A6)*('Shipping Tracker Week X'!$D$4:$D$3000=E6),0))","Link 1")
im pretty sure the problem comes down to the hashtag and quotation marks that i added, as the formula properly increased the cell references when i drag down without the hashtag and quotations. however, i sort of need it because adding those seems to be the only way i can get the hyperlink to link me over to the other sheet and to the correct cell containing the hyperlink.
part 2 of problem: the hashtag and quotations is currently a workaround method for me. the original intention was for the hyperlink to directly link me to a website, which is referenced from a separate sheet. however, without the hashtag and quotations, i get a "Cannot open specified file" error.