Increment Formula Row Reference But Not Column
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
ADVERTISEMENT
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
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
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
Jan 15, 2008
I have a spreadsheet containing quite a few lookups etc. I am trying to copy a cell across about 10 columns. This has to be done for about 50 different rows on about 20 different sheets, so I am looking for an alteration to the formula to help me rather than typing in the formula over and over;
='Basic Labour'!AD6*Rates!$E$526
Say this is in AD6, then in AE6 I would require
='Basic Labour'!AE6*Rates!$E$527
and so on across the region to be autofilled...
(It is multiplying the same cell in a different sheet against the 'next row down' in a rates lookup sheet).
View 4 Replies
View Related
May 26, 2008
In my data validation I have the following formula as the Source: =INDIRECT(SUBSTITUTE($F$2," ","_")). I want to be able to copy the cell and paste it into another cell and have the formula update to reference the new cell. Currently the validation is for cell G2 and references F2. I want to be able to copy G2 and paste it in G3-G6 having the formula update so it refrences F3-F6.
View 3 Replies
View Related
Mar 25, 2009
I am creating a spreadsheet which creates a bulk of data from a front sheet.
The question is:
Is there a way to automatically copy a fixed formula for 100 cells using one fixed cell reference such as $A$1 and then automatically after 100 cells replace $A$1 with $A$2, after another 100 with $A$3 and so on?
Explanation with Example:
For example, Sheet1 contains the words "Green Tree" in Cell A1 and Sheet2 will then place "Green Tree" into 100 different sentences such as:
Plant a Green Tree
Grow a Green Tree today
This would be created with the formula ="Grow a "&'Sheet1'!$A$1& " today"
After 100 different variations using the formula I want to change that formula to reference cell A2 on Sheet1.
I know if I place "Red Tree" in Cell A2 and use the formula ="Grow a "&'Sheet1'!$A$2& " today" I can do this manually using find and replace for the 100 cells, but I want to do this for 100 different variations of Green Tree to create a 10,000 different sentences so I'd need to find and replace 100 times!
View 7 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
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
View Related
Mar 6, 2008
A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.
When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")
ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
View 2 Replies
View Related
Mar 3, 2009
If I ask anyone in my office they can easily tell me what the next line should be in this list. I can't figure out how to do it in Excel
=A5
=A10
=A15
?
So, obviously the cells are referencing column A and increasing by 5. That's it, nothing fancy. I have 5,000 cells to fill and it's increasing by 13 instead of 5 so I can't just do it by hand. If I highlight the first 3 and then drag down the next cell would say A8. Not even sure how they get that.
View 9 Replies
View Related
Nov 3, 2009
I've got a worksheet here that i need to update every week. The problem i'm having is that i have a table of values that's drawing values in from another larger table. For example in B9 the cell is referenceing the I2 cell in another sheet. When i pull down the formula in B9 i want the cell below to reference J2 and so on. In other words, I only want the alpha characters to increment. Tried messing around with dollar signs but it doesn't seem to be doing what i want.
View 2 Replies
View Related
Nov 17, 2008
I'm setting up a workbook where users will select a saved workbook from a network drive (using Application.Dialogs(xlDialogOpen).Show); the macro will then open the selected file and pull data from select columns in that workbook and paste it into the summary workbook which holds my code.
The column into which the data will be pasted will vary by source workbook, so I'm using application.inputbox to have the user provide the first column into which data should be pasted for the selected workbook. Each workbook will have data that needs to go into two side-by-side columns, so rather than asking the user to input another alpha column header, I want to just increment the original input to represent the next column, so I can use that in my code.
Since the data is coming in as text, does anyone have an eloquent way to increment it, taking into account the rollover each time the final character is 'Z'? I thought about defining the inputbox variable as a column and using offset, but that isn't an option in the dim/as autocode. This code will primarily be used in Excel2007, but ideally would also work in 2003.
View 3 Replies
View Related
Oct 28, 2009
I have two sheets in my workbook, and I am trying to reference one chart's value into the other. (attached)
I have referenced successfully by manually typing in the reference on row 2 for all values on Sheet 1:
Sheet 1 - C2 needs to reference Sheet 2 - B2. (Sheet2!B2) - no problem all the way across to F2. All those values are correct b/c I manually typed in the reference.
The problem comes when I just want to drag over that same formula to increment for the other cells. It's not incrementing the formula like I need it to.
So, Sheet 1, Row 2 shows how the correct values I need to pull over but was done in a manual way. Sheet 1, Row 5 shows how it increments incorrectly when I try to just drag/copy the formula over. What I need is for Sheet 1 - D5 to reference Sheet 2 - C3, but instead if I try and drag to copy the formula instead of manually typing it in, it increments when I drag reference D2 instead of C3.
View 2 Replies
View Related
Nov 28, 2013
I've got 2 worksheets in the same workbook.
Sheet 1 contains huge amounts of data - thousands of rows and multiple columns
Sheet 2 - I want to extract the data from sheet 1 column A into sheet 2 column A but only the data from every 21st row.
I want to be able to copy the formula automatically down, otherwise it will take hours to do it manually. So far I can only get the cell reference to increment by 1 each time after copying.
So what I'm trying to achieve is :-
Sheet 2 A1 = Sheet 1 A1
Sheet 2 A2 = Sheet 1 A22
Sheet 2 A3 = Sheet 1 A43
View 3 Replies
View Related
Aug 14, 2009
I'm trying to revise a salary payments workbook that isn't set up so well and I'm struggling with finding a good way to enter payments from an old sheet onto a new sheet. The values (for example) I want to appear on the new sheet are in range C2:AQ2 on the old sheet. Instead of going left-to-right, I want them to appear from top-to-bottom. The first formula I wrote was just a simple:
View 5 Replies
View Related
Jan 5, 2009
Easy scroll to down ways, should I copy rows to a column? See the attachment for a more clearance.
View 4 Replies
View Related
Aug 7, 2013
I have a array as
01-12-2008 pump-1 25
01-12-2008 pump-2 28
02-12-2008 pump-1 24
03-12-2008 pump-1 26
03-12-2008 pump-2 38
03-12-2008 pump-3 35
I need sum value as
03-12-2008 pump-1 75(=26+24+25)
02-12-2008 pump-2 if no answer blank
03-12-2008 pump-2 66(=38+28)
View 9 Replies
View Related
Aug 22, 2008
I need to copy a range of values from sheet 3 to sheet1. When I recorded macro, I got the below code. But, instead of RC in the 4th line <ActiveCell.FormulaR1C1 = "=Sheet2!RC"> I need to pass values like A1,A20 etc.
Since the values range & column to be copied would be varying dynamically, (say for first iteration it would be A1: A20 & for second iteration, I need to copy C1: C20.) how to pass these variables to the macro and use it instead of the static "RC". Whatever column I specify, it should copy from that <column>StartRow to that <column> EndRow.
Sub Updtval(StartRow As Integer, EndRow As Integer, f As Integer)
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault
End Sub
View 3 Replies
View Related
Feb 7, 2008
In Sheet1,
C14 = Sheet2!D2
C15 = Sheet2!D3
C16 = Sheet2!D5
What i want to do is to change "D" to "E". Next time i run it, it will change from "E" to "G".
I want a macro to do it, so that I don't have to change it manually one by one. For some reason, when i try to use .formulaR1C1, the reference change, and I couldn't get it to work.
View 5 Replies
View Related
May 22, 2014
I'm dealing with forecasting invoices. The main column 'BALANCE' has a formula referencing the previous cell and then adding or subtracting invoices (ENTRY). The problem I have is that I'll put values in ENTRY that I expect to come in at some point but as they arrive I need to change the order of the cells. So I'm constantly changing the order and then it changes the formula in BALANCE. I tried absolute cells (and even found a work around way to flow absolute cells by flowing then viewing formulas then using the replace function but that didn't work.
So my formula in BALANCE is:
=$I$173+$E$174-$G$174
but if I move G174 to another cell I'd like to keep the BALANCE formula to stay G174 instead of following the move.
View 2 Replies
View Related
Jun 17, 2009
i have the table of holiday days with 7 columns(7 countries).Each column consists of 10(or more rows (holiday days).
I have a formula for calculating start day for a deposit which has to be renewed.
formula is =workdays(end date of deposit,-2,A1:A10).
-2 in formula is because all the trades in the market are traded Today+2days.
A1:A10 is column of holiday days.
This formula calculates the start of deposit excluding weekends,holiday days.
BUT I have more then 7 countries and I would like to have a macro so when in cell F10 I type SWEDEN,my formula automatically use the column with holiday days B1:B10 for sweden.
When I type Finland, the formula uses column C1:C10 with holiday days from Finland respectively.
View 6 Replies
View Related
Sep 8, 2012
I'm new to Excel (4th day in class) and I can't find the answer to what I'm looking for on the net. Part of the problem is that I don't know what to call it, so I have a hard time looking it up.
Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example number 50,60,70,80 are put in cells A6 to D6 (horizontal)
What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6
View 1 Replies
View Related
Apr 8, 2014
I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:
Sheets("Sold Articles Database").Select
Range("U3").Formula = " =VLOOKUP(LEFT(K3,2),'Input Variables'!$A$48:$B$52,2,FALSE)"
Range("V3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$C$10000,3,FALSE)"
Range("W3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$D$10000,4,FALSE)"
[Code] ........
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
May 26, 2009
I'm working on setting up a chart. The formula I'm using for cell B21 is this: =ROUND(F3,0) & " " & IF(E3<F3 ...
View 2 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
