Stepped Increment For Reference
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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
Jan 22, 2014
scenario testing and coming up with a simple "multiply by" that I can drag across a table.
View 5 Replies
View Related
Jun 20, 2014
Never tried complicated formulas in Access and at a bit of a loss... What I am trying to do is calculate a utility bill based on stepped rated.
For example:
Usage up to the first 500KHW is billed at .067 per KWH
Usage after the first 500KWH from 501 to 999 is billed at .044 per KWH
Usage from 1000 up is billed at .0318
So if my usage was 1200 KWH...
((500 x .067)+(500 x .044)+(200 x .0318)) = 61.86
I was assuming it would require an complex "if" function to split the 1200 into steps and then calculate charges per step?
View 9 Replies
View Related
Apr 23, 2008
The phone call is charged at 0.57 for the first 60 seconds, thereafter 0.0095 per second. Determine cost if duration (in seconds) is available?
AND
The phone caal is charged at 1.654 for the first 60 seconds, thereafter 0.827 for every 30 seconds.Determine cost if duration (in seconds) is available?
View 7 Replies
View Related
Feb 4, 2014
I have a base rate in A1, the the units in B1 and need a total in C1.
In A3 i have discount rate (%) for units between 0 and 9
In B3 the have the discount rate (%) for units from 9 to 15
In C3 the have the discount rate (%) for units from 9 to 15
In D3 the have the discount rate (%) for units above 16.
How would the formula looklike?
View 3 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 20, 2009
I want to take the date value of a cell, add one day to it and paste it to another sheet's cell. For example if Sheets("x").range("i4").value is 12/12/1999. I want to add a day to it (13/12/1999) and then paste it to Sheets("y").range("d3").value
View 4 Replies
View Related
May 15, 2006
I have a sheet with the column for time the format should be h:mm:ss, the time begin with 0:00:00 and 10 sec periods 0:00:10 ...until 50sec again in the next count wud be 0:01:00 again 10sec periods.. How to write the Macro, that automatically increse the values of minute (mm)if the ss attains 60 and increment the hour(h) value if mm get 60.
View 2 Replies
View Related
Feb 4, 2014
I have encountered some difficulty in modifying a macro I wrote into what I need. I created a macro that searches a column (Column C) for a cell value of, "stop", and then it copies everything above that cell and pastes it onto another sheet. In the sample data set that I was using, "stop" first occurred in cell C541, so the macro copies C1:C540 and pastes it onto another sheet. The problem is that the macro created an absolute reference to C540. What I desire is for the macro to use the 'Find' function to locate the first occurrence of, "stop", offset one cell above that cell, and then reference the active cell (which was positioned by these last two steps) in the range that should be copied. Basically, I'm hoping to have cells C1 through the active cell copied and then pasted onto another sheet.
Code below.
Sub FAIL()
'
' FAIL Macro
'
'
Sheets("Reformatted").Select
Columns("C:C").Select
Selection.Find(What:="stop", After:=ActiveCell, LookIn:=xlValues, LookAt _
[Code] .......
View 4 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
Sep 2, 2009
I want to write formula in cells(many cells!) using concatenate. The desired output:
In Sheet2!D1 - i will write concatenate of Sheet1!C6 and Sheet1F6
in Sheet2!E1 - i will write concatenate of Sheet1!C7 and Sheet1F7
in Sheet2!F1 - i will write concatenate of Sheet1!C8 and Sheet1F
Copy and paste formula would not do so i created a macro stated below. The problem is a can not change the ActiveCell.Formula correction.
View 2 Replies
View Related
Mar 14, 2013
I have a table... One of my cells is called Capacitors and the number of the cell is 202600. This is the default value. How can i increment this value?
For example:
I have capacitors in one cell with this value => 202600.
But I want to put capacitors in another cell and i want the value to be 202600 + 1 => 202601
Another cell with capacitors with 202600 + 2 => 202602
And so on...
Capacitors.JPG
View 6 Replies
View Related
Aug 19, 2014
I currently have a spreadsheet that parses a HL7 message string using "|" as a delimiter. The String that comes before the first "|" becomes the sheet name (Segment). The code executes on each line of the string (Each segment is parsed). The problem is that sometimes there are multiple segments with the same name. So instead of a new sheet being created, all segments are lumped into the same sheet with that name. What I am trying to do is have the code create a new sheet for each segment and if there it is already present, add sheet name with an incremented number.
So given the sample message The MSH, SCH, PID, PV1 etc sheets are fixed. The DoHL7Parsing routine takes the line and parses it out in the existing sheet. The problem I'm having is lines that are the same such as AIP get lumped into one sheet. What I would like to happen is have AIP1,AIP2,AIP3,AIP4,AIP5 and AIP6. Each with the line parsed out. These are parsed by segment not fields. I want to be able to recognize the existing sheets and create new sheets if multiple segments with the same name are being parsed.
Sample Message:
MSH|^~&|SR|500|CL|500|20140804150856-0500||SIU^S14|5009310|P|2.3|||AL|NE|USA
SCH|10262|10262|""|S14^(SCHEDULED)^L|44950^APPENDECTOMY^C4||^^^201408081345-0500^^^^^^2||30|
MIN^MINUTES|^^^201408081345-0500^201408081415-0500|10000000034^ROISTAFF^CHIEF^O||||||||
PID|1|5000|50^^^USVHA&&0363^NI^FACILITY ID&500&L^^20140804~666^^^USSSA&&0363^SS^FACILITY
[Code] ........
Current Code:
Option Explicit
Const HL7_DELIMITER_FIELD = "|"
Const HL7_DELIMITER_SEGMENT = vbLf
Sub DoHL7Parsing(sMessage As String)
Dim vSegments As Variant, vCurSeg As Variant
[Code] .........
View 1 Replies
View Related
Apr 5, 2008
I have a value in cell P1 and is always -ve.
This value is calculated by some tedious calcualtion in some other part of the sheet.
By entering value in Cell P2, the value in cell P1 will progress towards +ve number.
What i want is a formula or Macro to increment the value in Cell P2 by 0.01
until value in Cell P1 becomes +ve...
Below is the example of my manual calculation.
When i have
P2=0 P1=-23.58
P2=0.01 P1=-14.78
P2=0.02 P1= -9.30
P2=0.03 P1= -2.92
P2=.04 P1= 1.05
Since i have achieved P1 to be positive, calculation should stop with value in P2=0.04
I have to do this for 31 column and hence manually doing it takes long time
View 13 Replies
View Related
Feb 24, 2009
For Next Loop increment. simple For-Next Loop:
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
Sep 22, 2011
I have a input worksheet and in this user have to manually enter the sequence no. The sequence no. heading is not always in one cell that can be in any between any cell from D14 to D21.
I need some code which start the sequence no. with 1 and everytime I click add button. It find the sequence no. in range between D14: D21 and add 1 in the relavant adjacent cell (which can be in from E14 to E21).
See Table below
Start withColumn DColumn ERow No. can be any from 14 to 21Sequence No.1Clicked AddDesiredColumn DColumn ERow No. can be any from 14 to 21Sequence No.2DesiredColumn DColumn ERow No. can be any from 14 to 21Sequence No.previous val +1 and so on
View 2 Replies
View Related
Apr 20, 2013
The question that I have is as follows:
For i = 17 To FinalRow Step 14
'Select cells to examine based on For/Next loop Step...
Cells(i, 4).Activate
'Check for "value"...
If ActiveCell = Press Then
[Code]...
The "Cells(i, 4).Activate" statement steps my search criteria forward by the value of "i." The last thing that I want to do with this macro is to increment the value of A4, "Range ("A4").Select" and D6, "Range ("D6").Select" by the value of "i."
View 9 Replies
View Related
May 31, 2007
I want to test a condition and if false skip the rest of my for block and hit the next for:
For something = 1 to 100
if test = "false" ?NEXT FOR?
code
code
code
code
Next
I can set a lable at the Next and use a goto but that seems kinda kludgy.
View 10 Replies
View Related
