Increment Column Address While Copying Formula Down?
Apr 27, 2014
I am creating a training document for work; the sheet I'm creating is a summary sheet which works out how many people answered questions correctly, incorrectly (bringing in from another sheet), number of questions answered and percentage of correct answers.
Looks like:
Correct: =COUNTIF(sheet1!E2:E36,"correct")
Incorrect: =COUNTIF(sheet1!E2:E36,"incorrect")
Number of questions answered: =SUM(C4+C5) (correct+incorrect)
Percentage right: =SUM(C4/C6) (correct/number answered)
I need to drag these 4 formulas down into another 400+rows, however in the correct and incorrect formulas I need it to keep the 2:36 but change the e to f, g, h, etc.
View 14 Replies
ADVERTISEMENT
Feb 26, 2014
I'm having to copy formula on a tabulation sheet that compiles information from other worksheets, and I need to change the formulas in each row to refer to the successive worksheets. Right now I don't know any other way than changing the formula in each cell, and this is taking way too long.
Example:
One cell's formula: =COUNTIF('5'!$F$21:$T$50,TABULATION!C$5) In the next row down, I need it be: =COUNTIF('6'!$F$21:$T$50,TABULATION!C$5), then =COUNTIF('7'!$F$21:$T$50,TABULATION!C$5), and so on.
[URL]
View 4 Replies
View Related
Mar 16, 2009
to copying a formula from one column to the next adjacent column and also incrementing the 2 numeric values inside the formula. Please see attached sample.
One value increments with each new column whereas the other increments with each new row. Please see attached sample.
And in the last row there is the average formula which must copy the new column and average the new column.
I tried to create a Macro in attached but it did not work with formulas.
View 8 Replies
View Related
Sep 22, 2007
As per this screenshot,
I am trying to work out a method which will allow me to drag the forumula which is in cell B13 from B13:B17, and have in increase the date serial number by one for each row by which it descends.
In other words I want to drag the formula down to, say B14, and have it increase the date serial to = that which is in cell A14.
[the obvious benefit of being able to do this is that I don't have to manually adjust the date serial in each cell on each new row]
View 9 Replies
View Related
Oct 11, 2007
I try to select a range with a counter, but cannot find how to do it.
My aim is in the following loop to select the range ("B,i:V,i").
B and V are the colum, i is the counter that marks the row number, counting from 8 to 16.
Sub Copy()
For i = 8 To 16
Sheets("DB2").Select
If Cells(i, 7).Value <> 0 Then
Sheets("DB3").Select
i = i - 1
Cells(i, 2).Select
'this is the cell that should be a range ("B8:V8") to ("B16:V16"), as the counter goes from 8 to 16
i = i + 1
Selection.Copy
Cells(i, 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Else
End If
Next i
End Sub
the Sub works with a cell, can somebody please tell me how to turn the cell in the range?
View 9 Replies
View Related
Jun 21, 2014
Have table with five columns and five rows. The columns(C4:H4) hold Systems, Systems is a defined range, and rows (B5:B9) hold staffer names, Staffer is a defined range. Cell A5 holds period, by quarters, ie, Q1-Q4.
At present am using =COUNTIFS(Staffer,$B$5,System,C4,Q,$A$5). This works fine but as you can see I must change the Staffer starting reference $B$5, with each new row.
formula that changes column and row automatically as I drag down and across.
View 6 Replies
View Related
Jan 17, 2008
I'm using a macro to update individual formulas in a sheet that contains data from external documents. (i.e. ='C:September[Book2.xls]Sheet1'!$AZ$1 + 'C:September[Book3.xls]Sheet1'!$BA$1 ) Every quarter I have to change the column references in this formula (i.e. $AZ$ will become $BD$ and $BA$ will become $BE$ and so on). Also I have to change the September reference to December etc (but I've mastered this already). My current code works well, but requires manual updating:
Dim ColumnReference1
ColumnReference1 = "BD"
Dim ColumnReference2
ColumnReference2 = "BE"
Worksheets("Sheet1").Range("H48").Formula = "='C:September[Book2.xls]Sheet1'!$" & ColumnReference1 & "$1 + 'C:September[Book3.xls]Sheet1'!$" & ColumnReference2 & "$1"
My problem with this method is that I need to automate the updating of ColumnReference1 = "BD" to ColumnReference1 = "BH" to ColumnReference1 = "BL" and so on and so forth. It's handy that the reference shifts across 5 columns, which remains constant. Ideally, I'd like to tell the macro to look at "AZ" then add 5 columns.
View 9 Replies
View Related
Jan 18, 2008
I'm trying to write a formula that will keep the Column ID static while allowing the Row ID to be reference. Each week my worksheet adds one line and the formula in question is moved one cell to the right and thus the formula cell makes the same move (1, -1). I need the formula to keep the column the same from week to week, while allowing the row to shift downward with the formula. I've attached a sample spreadsheet with the formula I derived. Check out the highlighted formula to familiarize yourself before running.
View 2 Replies
View Related
Dec 3, 2007
COPYING indirect formula. When I copy, the lookup reference does not change. My formula is =INDIRECT("'"&$A247&"'!j106"). When I copy horizontally across cells, I want J106 to increase, ie j107, j108 etc. At the moment it remains at J106 and i have to manually increase the numbers by 1.
How do I change my formula so that the numbers increase automatically?
View 6 Replies
View Related
Mar 21, 2008
I have a formula that I want to insert using a macro, so how do I iterate the * values in this line?
ActiveCell.FormulaR1C1 = "='Sheet1'!R[*]C[*]"
View 9 Replies
View Related
Apr 24, 2013
I have a list for addresses in excel in single column as shown below - aanandhi narayanan 3430 chemin de riviere sanjose,CA95148
DOMINIC ABANO 3838 GLENGROVE WAY SAN JOSE, CA 95121
abdi abdi 5390 monterey rd #6 sanjose,CA95111
Sheribel Abinsay 3212 Gateland CT San Jose, Ca 95148
I need the result to be in a way like -
3430 Chemin de riviere
San Jose
CA
95148
3838 Glengrove way
San Jose
CA
95121
5390 monterey rd#6
San Jose
CA
95111
3212 Gateland CT
San Jose
CA
95148
I have around 12000 records with the same format.
View 9 Replies
View Related
Jul 17, 2007
I want to copy a D1-C1 formula all the way down column E. If I don't have any data in column D1 and C1, however, I don't want anything to appear in Column E.
Is there a way to hide the formula once I have built it for the all of Column E? The reason I need this to happen is because I can't have any zeros popping up in Column E, because I am calculating a running average of Column E and "zero" values would throw off my average.
View 9 Replies
View Related
Feb 13, 2009
How do you make a formula continue all the way down a column, so i haven't got to drag or copy it down all the time?
Lets say I have a formula in row Z, which is =SUM(A1:T1) If I want that formual to be in every cell of row Z, I would have to copy it down. However, if I do that, the spreadsheet becomes huge. I just want that formula (in fact my formula is much more complicated) to always be in row Z, for evermore, as the spreadsheet in time will have more and more rows in it.
View 9 Replies
View Related
Feb 22, 2009
i made a macro that uses a form with a "browse" button to open a text file and put every word in this textfile in a single cell but i had a problem when copying the address of the file to a textfield, here's the code :
View 5 Replies
View Related
Feb 2, 2014
I have an array formula that I need to copy down the entire column. The formula is:
=SUM(IF(IF(K$2>=V3:V5,V3:V5-V2:V4,$K$2-V2:V4+1)>0,IF(K$2>=V3:V5,V3:V5-V2:V4,$K$2-V2:V4+1),0)*W2:W4)
I need the Row values to change, not the "array formula parts" (because I don't know what they really are called). So, as I copy down I need the new formula to read:
=SUM(IF(IF(K$4>=V3:V5,V3:V5-V2:V4,$K$4-V2:V4+1)>0,IF(K$4>=V3:V5,V3:V5-V2:V4,$K$4-V2:V4+1),0)*W2:W4)
The only way I've been able to do that is to rewrite the whole formula in the new row cell.
View 4 Replies
View Related
Jun 28, 2014
How to express what I am trying to do in a sentence but basically I have this formula
[Code] ..........
I need B# to change automatally like it does when you copy the formula but i need the N1 and N2 to stay N1 and N2, how do i do this?
View 3 Replies
View Related
Feb 2, 2014
I 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 Related
Sep 15, 2008
I have a formula that I'd like to have increment by one, but I can't figure out how to do it.
Here's the formula:
=IF(INDIRECT("'Supervisor'!B"&ROW()-4)="Y",E12-D12,IF(INDIRECT("'Supervisor'!B"&ROW()-4)="N","0",IF(INDIRECT('"Supervisor'!B&ROW()-4)="","0")))
I'd like to have the -4 in the equation increment to -5 when I paste it to the next row without having to fix each one of them.
View 14 Replies
View Related
Jun 28, 2009
Here is what I try to do:
At
A1 = Power(C1,B1)
A2 = Power(C2, B1)
A3 = Power(C3, B1)
....
An = Power(Cn, B1)
How can I increment column C while keeping B constant at B1 by using right-bottom mouse pointer dragging on column A? Or is there another way?
View 2 Replies
View Related
Apr 15, 2013
I have a number of strings in adjacent columns in the same row, like so.
Coulmn1 Column2 "blank" Column3
If the cell is blank I want to hide the respective column .
I'm guessing the code will roughly take the form of the code below with an if statement nested in a for statement.
Sub (test)
With Range("BD22")
For i = 1 To 10
if range (i,22) = "" then hide
else don't hide
.Offset(1, 0).Formula
Next i
End With
View 1 Replies
View Related
Jun 14, 2013
I have a workbook that has a few data sheets and a summary sheet. On this summary sheet I have to grab data from the data sheets which is in the same row number but different column (always 5 to the right of the previous one).
Eg: Person's Name is in Row 10, Col E (E10), the next name is in J10, then O10, etc. This carries on past Column Z. For each person there is a bunch of other data which is in the same format (set row but 5 over for column).
Since row stays the same i thought i could absolute reference that and then leave column as relative and drag down. This works but only if I drag to the right (across columns) and only if i need to increment by 1.
View 2 Replies
View Related
Aug 7, 2012
How to increment a formula with an If statement that transforms 2 values in time format and then as long as these two values keep beeing added in the cells the formula keeps active in the column.
I tried to apply the code for all the but this makes my file huge like 20MB!
You can check this on the file, go the "records" sheet, add the time in the "shift start" and "shift end" fields, then the difference value goes to "hours" in the sheet.
[URL] .....
View 4 Replies
View Related
Jan 24, 2009
I have a worksheet (Sheet1) with column A labeled MissionNumber. I have also created a Form which I can use to enter the next mission number into that column. However, instead of manually entering the number...when opening the form I would like it to check column A for the last entry...and automatically increment the mission number by 1 and have it displayed on the form.
View 10 Replies
View Related
Dec 24, 2012
I have a workbook with 70+ worksheets that pulls data from a single input sheet and I need to increment the cell references in each worksheet by 1. Is there a way to do that without having to manually open each worksheet and change the cell reference.
VB:
'Row 27 on worksheet 1 cell B27' =+Input!$D3
'Row 27 on worksheet 2 cell B27' =+Input!$D4
'Row 27 on worksheet 3 cell B27' =+Input!$D5
Thru worksheet 70
'Row 27 on worksheet 70 cell B27' =+Input!$D72
View 5 Replies
View Related
Mar 5, 2009
I have a sheet which needs to look up one reference and then fill a table with the rest of them.
EG:
Cell A1 contains '0091 911'!$E$2 (cell E2 contains value 100)
Cell A2 contains =indirect(A1) and displays value 100
I need a formula which will auto fill the remaining cells in the table.
eg:
Cell A3 fills to contain '0091 911'!$E$3 (row +1)
Cell B2 fills to contain '0091 911'!$F$2 (column +1)
so it needs to fill the Indirect reference and not =indirect(A1),=indirect(A2)....
View 14 Replies
View Related
Jul 22, 2006
I am needing a macro to modify a cell reference contained in a formula. For example: I have "=B2" in a cell of my worksheet, and I need some code to select and increment that by 1 so it becomes "=B3". I know how to use the ActiveCell.Offset command, as shown below, to select a specific cell so that it can then be modified, but I can't figure out how to make the actual change. ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Select
View 3 Replies
View Related
Aug 31, 2006
If I type the formula below into cell A1 and then drag it to cell A2 it correctly changes $A6 to $A7
CELL A1 =Planner!$A6*(BS8/20)
CELL A2 =Planner!$A7*(BS9/20)
I would like it to change $A6 to $A14 and then to $A22 as below. In other words adding 8 to the reference each time I drag it down.
CELL A1 =Planner!$A6*(BS8/20)
CELL A2 =Planner!$A14*(BS9/20)
CELL A3 =Planner!$A22*(BS10/20)
CELL A4 ==Planner!$A30*(BS11/20)
View 9 Replies
View Related
Jun 1, 2008
I am trying to link cells between two worksheets and am having some difficulty. For instance, in E17, I want the cell to reference cell H41 on sheet 2 and for F17 I want it to reference H42 on sheet 2, etc. However, using the formula =Sheet2!H41 when I cut and paste the formula it changes it to =Sheet2!I41 (ie it changes the column and keeps the row constant). I want to find a way to fill a large amount of cells this way and would prefer to not have to manually change each cell individually. I assume I would use the indirect( and the column( commands but can't seem to figure that one out.
View 2 Replies
View Related
Mar 7, 2014
I am working on a sheet that has dynamic data saved. For any new value, a new row is inserted. I want to add a counter or more like an index so that I can see the number of the rows. I know that excel has is on the side but, I want to be able to make calculations. Like average of the numbers. Lets say I have 512 rows added. I want to be able (automatically via vba) to set the average with a sum of the values/ 512 which will be the number of the code I am asking. So I want a code that will start with the number 1 at (lets say) cell B2 and it will go like B3=2, B4=3.... any time a new row with data is added.
View 9 Replies
View Related
Oct 29, 2009
I have the following formula. How can I change it so thst when copy/drag the column number automatically increments by 1
IF(ISNA(VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE)),0,VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE))
View 7 Replies
View Related
