Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Number Sequence: Add +1 To The Previous Cell


If I want to create a column of numbers, say 1 2 3 4 5, I can simply add +1 to the previous cell and then use "fill down" to generate my number sequence. How would one generate a column of numbers that repeat once? e.g.: 1 1 2 2 3 3 4 4 5 5, etc


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Add Worksheet Named As Cell. Number Sequence
I'm trying to set up a worksheet so that when i enter a number in say cell J5 it will automatically create a new worksheet with the next number in order so if cell J5 was 1500 it would create a worksheet named 1501.

View Replies!   View Related
Next Number In Sequence
I have Excel 2003. I am trying to find the next number in a sequence of numbers. The number range is 1-59, and the sequence is 89 numbers that go like:

1
5
8
3
7
10
6
2
5
3
8
11
41,...(to the 89th number)

View Replies!   View Related
Get The Next Number In A Sequence
I have code that adds the content of a userform directly onto the next available row starting at column B, using:

View Replies!   View Related
Choose The Next Number In Sequence
"A1" has a number containing "1000"
I want column "B2" to choose the next number in sequence,(1001) so long as "A2" contains these letters "EHT". if it does not contain "EHT" i dont want excel to insert the number "1001"
But if cell A3 contains, "EHT" I want the next number in sequence to be instered automatically... (1001, 1002, 1003")


View Replies!   View Related
To Count The Number Of Rows In Each Sequence
I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,
with numbers in them, (as an result of formulas)
I need to count the number of rows in each sequence that have numbers
of 35 or less , but only up to the first number of 35 or higher. eg:-

AU result
row 39 32 3
40 14
41 19
42 107
43 11
44 21
45 #value!
46 40
47 45
48 16
49 15
50 #value!
51 29 2
52 11
53 146
54 14
55 17
56 11
57 #value!

and so on down to 500 rows

View Replies!   View Related
Add Next Number In Sequence & TextBox Text
I have worksheet with a column of client codes. These are made up of:
(a) a "base code", being a minimum four to five digit, i.e.

0000
0001
0002, etc

(b) plus a decimal point and a "sub code" (i.e.

.00
.01
.02
.03 through to .99)

The worksheet also contains the client name (i.e.

1031.00 John Smith
1031.01 Mary Smith
1032.00 Jack Brown
1033.00 My Company etc

As the supplier codes list has been manually done, there are numerous "gaps" in base code numbers as well as the subcodes.

I am looking to have a userform that a user can:

find the next unused base code and apply the 00 subcode (with client name).

View Replies!   View Related
Copy Worksheet & Name In Number Sequence
Add Worksheet Named As Cell. Number Sequence

how would I set it up so that it copies the page not just creating a blank page.

View Replies!   View Related
Retain Auto Number Sequence After Row Insert
I'm using a simple auto numbering command.

eg: cell A1: 1
cell A2: =(A1+1)

then shift, copy/paste cell A2 until A600,

This creates a sequential number column from 1-600. My problem lies when a new row is inserted, which is left blank. How can i maintain this auto function upon a new Row insertion?

View Replies!   View Related
Changing The Sequence Of Characters Appearing In A Cell.
Suppose a name is typed in a cell as the First name in the front and the Last name following it e.g. Jupiter Jones.

Can it be reversed in the same cell so that it appears as Jones Jupiter i.e. with the last name in the front and the first name following it.

View Replies!   View Related
Run Macro Sequence On Set Cell Changed By Drop-Down
I have found a macro that should work with a change of a specific cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$7" Then
Call Button
End If
End Sub

However, the code doesn t work as with a change of A7 nothing happens.
A7 equals to result from a drop down list --> A7=A5(which is a drop down list value). The "Button" macro is a combination of 4 macros that get rid of zero values and names in pie charts (maybe this plays any role). The master macro is assigned to an object. The code is:

Sub Button1()
Sheets("report").Select
ActiveSheet.ChartObjects("Chart 140").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "...................

View Replies!   View Related
Place In Sequence Increasing The Numbers Of One I Creak In A Cell
I would like to place in sequence increasing the numbers of one I creak in a cell.
In the formula I determine it I creak and the corresponding frame number to the placed ones.
The problem is that he is accumulated only the greater and not sequencia it.

1803456781101112
1134567810111280

but,80-80

Function ordenar2(Myrange As Range, num As Integer) As String
Dim Myorder As Double
Dim X2 As String
Dim n As Integer
n = 1
Do While n

View Replies!   View Related
Automaticly Populate Dates In Sequence Based Off Of One Cell
How do i get cells to automaticly populate dates in sequence based off of one cell. I.E C1= 4/Jul/09 I want A2-A7 to populate the dates in sequence Jul 4 - Jul 11. Is it a formate issue?

View Replies!   View Related
Summing Sequence Of Numbers Seperated By Comma Within Single Cell
I'm a basic excel user really and cannot find a function that will allow me to complete the following:

CELL A1 includes the following sequence of numbers: 1,2,1,0,2,3
I would like to know how to put a formula/function into CELL B1 that sums up the sequence of numbers in CELL A1 (i.e 1+2+1+0+2+3)

View Replies!   View Related
Populating A Cell With A Calendar Month Based On The Previous Date In Another Cell.
Trying to word this right. I have one cell with a date of 01/01/2010. I have other cells that I want to be equal to this cell plus 1 or more months.

For example A1=01/01/2010

I want A2 to = 02/01/2010 based on one calendar month entered into A1. So if A1 changes 03/01/2010, A2 will = 04/01/2010.

View Replies!   View Related
Copy Formula From Previous Cell To Next Cell When I Enter Something
I want to copy formula from previous row to next cell when i enter something in perticular cell.
i.e

--Colomn A --- Colomn B -- --------Colomn C
1 01-09-07 ----- John ----------=vlookup(b2,$s$1:$t$10,2,false)
2 01-09-07 ----- Smith -------- =vlookup(b3,$s$1:$t$10,2,false)
3
4
5

Now if i enter date in cell A3 then cell C3 should be automatically filled/copy formula as celll C2. and so on......
then if i enter data to A4 then cell C4 should be automatically filled/copy formula from cell C3.

I have also attached example file.

View Replies!   View Related
Recording Previous Value In A Cell
I have a DDE-based worksheet, that is obtaining data from Townsend Analytics' RealTick Server software. One of my cells is a DDE formula that retrieves the current BID ask price, for a given stock.

When a new BID price comes in to my designated cell, I need to somehow capture the existing BID price, before this new one updates in the requisite cell. I want to be able to store this "old" value in another cell.

Unfortunately, there is no "pre-cell" event trapping. I've tried to work with the worksheet_change event, which passes an argument of the effected cells, but the cell value has already changed, by the time this event has been called.

This problem sounds simple, but I am at a loss for a solution. Does anybody out there in EXCEL-land have a solution ?

View Replies!   View Related
Go Back To Previous Cell
With "edit directly in cell" turned off, if you double click through in a cell containing a formula to the cell being refferenced, is there a quick way of getting back to the original cell you clicked on?

View Replies!   View Related
Insert Next Row Based On Previous Cell Value
My current code works, but there's got to be a shorter version to insert rows based on a cell value. Currently my code works on a series of If statements. If the value in the current cell is "2" then goto the next row and insert one line. If the value is "3" then go to the next row, insert, next row insert etc. I'm currently written up to a value of 10, but the coding is getting longer and longer. Anybody got a shorter loop that I could use.


View Replies!   View Related
If Previous Filled Cell Is Equal To
How could i change this formula so

If the previous cell "filled with a number" in column D is equal to C17 Then C18 otherwise c17

This is the formula
This formula is in D38
=IF(DAY(A38)=DAY($C$13),$C$17,)

This formula is in D65
I also need to apply the same to this formula
=IF(MOD(ROW(A38)-MATCH($C$13,A:A),7)=1,$C$17,)

View Replies!   View Related
Comparing Previous Values Of A Cell
I have a cell (F2) that is auto updated with total sales units for today (Its a simple counter of individual sales). I want to detect when there is a "batch" of sales >= 10. ie. F2 may start at zero for the day and increment by 1,2,5 or 10 units at a time. If the increment is 10 units or more I would like to detect this and store the result in cell G4.

Typical values in C2: 0, 1, 6, 7, 9, 19, 21, 22 etc as new orders arrive. The larger order between 9-19 (viz 10 or more), I need to detect and store in G4. I know that I need to use both the current value of F2 (say 19) and the previous value of F2 (say 9) to perform this, but the solution eludes me.

View Replies!   View Related
Loop And Return To Previous Cell
I am trying to take a column of data located on one sheet and move into two different locations on another sheet. My range is from A1:A10. The below code works, but as it loops, it replaces G1 and H1 with the other values from the original range. Once the first pos and neg values are placed in G1 and H1 respectively, I want the next cells to be evaluated and placed in G2 and H2 etc. What am I missing here? How can I get it to stop pasting over itself?

Sub test()
Dim bcell As Range
For Each bcell In Range("a1:a10")
If bcell > 0 Then
bcell.Copy Destination:=Worksheets("Sheet2").Range("G1")
Else: If bcell < 0 Then bcell.Copy Destination:=Worksheets("sheet2").Range("H1")
End If
Next bcell
End Sub

View Replies!   View Related
Previous Active Cell Address
I need to locate the address of the previous active cell without triggering a selectionchange event (as this is where my code is activated from). Here is what I am currently using unsuccessfully:

strPrevAddress = ActiveCell.Previous.Address

View Replies!   View Related
Find Previous Non-empty Cell
I'm trying to use the find() function (or another if it exists) to find a previous non-empty cell.

Basically, the active cell will be ain a column with mostly empty cells, and I need it to move up the column looking for the previous cell that is not blank. I could do it with a loop, but I'd rather not if possible.

View Replies!   View Related
If Value, Append Data To Previous Cell
if cell in column E contains 'YTD', append value of cell in column D at the end with a space before it (example: " VALUE") to cell in column C. not sure how to go about this. i attached a one row example

View Replies!   View Related
If Condition:one Cell Is Greater Than Previous Cell By At Least A Certain %?
how to include a condition in the condition? What I mean is in this set of numbers:
1000(A) 1000(B) 1000(C) 1200(D) 500(E) 1000(F)

I want excel to point out (with a 1, True, x, whatever) if (B) is greater than (A) by 10% or more and if (C) is greater than (B) and so on. I know I cant use =if(C>B,1,0)...Im just unsure how to include the % in the function

View Replies!   View Related
Cell Adds Previous Values Automatically
I have a market data linked to excel through DDE link so prices are updated real time.

Cell A5 contains last traded volume which updates whenever a trade is done.

I would like to SUM/ADD all last trade volume in cell C5. Is there any function or macro can help me to do this?

View Replies!   View Related
Going Back To Previous Cell Within Loop Macro
I need to copy a changing source cell, paste its value into another specific cell (always the same), and then return to the source cell for continued action (ie range selection, copy & value paste, which I can code).

This action is then followed in the next cell to the R of the first cell copied, etc to end of data. I can code the move to the R.

How do I return to the source cells as part of a loop?

Specific notes included in attached - I hope I've explained it clearly.


View Replies!   View Related
Show If Cell Is Greater Or Smaller Than Previous Value
I have a DDE feed going into an Excel spreadsheet which gives me a share price in real-time. i.e the cell value is constantly changing.

I would like to create a formula that tells me if the price is 'Rising' or 'Falling' based on the previous value, before it was updated.

So, for example, if cell C4 said £1.00 and the the next price change changed cell C4 to £1.05 I would like cell C5 to say 'Rising' If the next price change was say to £1.04 I would like cell C5 to say 'Falling'.

View Replies!   View Related
Macro To Use Previous Cell Value Based On A Flag
see the attached file for an example. I am creating a formula/macro for performing the following: If "Y" exists in column J, then replace in the next cell below (columns G and H) with the values from the cell above in columns G and H and then clear the contents from the above cells.

View Replies!   View Related
Vlookup = Lookup Returning Previous Row Cell Data
I have a list of airport codes and I need to look up these 2 digit codes and find the relevant airport and country.

For some reason when I use the lookup function it is finding the correct lookup value but returning the column cell in the previous row.

View Replies!   View Related
Macro - Auto Populate The Data, Based On The Previous Cell Values
the post 5 for the actual issue. This being my first post could not update it correctly. I have put my views int he 5th post which will be more clear.

View Replies!   View Related
Running Totals In The Cell As The Previous Week And So On Until New Data Is Entered And Updates The Total
I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesnít seem to work? (Have thought about threats of violence but would probably lose my job) Iím if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.

View Replies!   View Related
Update A Cell Value From A Previous Sheet After Copying The Sheet And Renaming It
I have copied a sheet, moved it to the end and renamed it with a date that is in cell "A1"

Now after that process is finished I need it to update the date in cell "A1" of the newly created sheet with the next day's date.

I am stuck however referring to the previous sheet to update the date value in "A1"

View Replies!   View Related
Min And Max In A Sequence
I have in column " A" 500 rows with numbers in random sequences each
sequence has random number of rows , each sequence is seperated with a blank
cell (the result of a formula) some sequences have zerrows included.
I need to find the lowest number in each sequence and put that number in
column

" B" I also need to find the highest number and put that number in column "
C"

eg:- A B C
4 2 10
6
2
8
10
blank cell
7 7 50
9
11
13
50
18
21
30
15
blank cell
3 1 17
5
0
1
0
0
17
6

some sequences have two or more of the same numbers , in case of two ore
more of the same numbers only one is needed

View Replies!   View Related
Letters In Sequence
I would like to create a column with letters from alphabet in a sequence. If I write A and in cell below I put B then highlight the two cells and drag down I get a repetition of A and B. How do I get the following alphabet letters ie. C,D, E etc.?

View Replies!   View Related
15 Digit Sequence
My question is, a formula was provided to me that add's a comma and a space after a 15 digit sequence of numbers, that formula is:

=IF(MID(C378,LEN(C378)-1,1)="W",C378&" ,",C378)

And what that does is it would change data in a cell that looks like this
01-02-034-05-W2 TO 02-03-045-06-W2

To this:

01-02-034-05-W2 TO 02-03-045-06-W2 ,

The formula works great, thanks to the help of the posters!

However I just ran into an issue, what if I have a set of data in a cell that looks like this:

01-02-034-05-W2 TO 02-03-045-07-W2
02-02-034-05-W2 TO 02-03-045-07-W2
03-02-034-05-W2 TO 02-03-045-07-W2

And I need a space and a comma at the end of each sequence, so the above would change to this after the formula is applied:

01-02-034-05-W2 TO 02-03-045-07-W2 ,
02-02-034-05-W2 TO 02-03-045-07-W2 ,
03-02-034-05-W2 TO 02-03-045-07-W2 ,

Now the data in the cell is wrapped so it lists one set of numbers, then the next set, then the next set as seen above.

View Replies!   View Related
Fibonacci Sequence
I'm trying to write some code to calculate the value of a Fibonacci Sequence, at a random point in the sequence.

For example if I have 5, 15, 30, 50, 75, 105... and my random value is 3 it would return 30.

Being new to VB the only way I've of thought of to do this is a massive if then statement.

View Replies!   View Related
Multiply Every Integer Less Than The Number In Cell A1 And Greater Than Zero By The Number Cell In A2..
Cell A1 = 3
Cell A2 = 4

Is there a formula to calculate
(3*4)+(2*4)+(1*4)

I need to multiply every integer less than the number in cell A1 and greater than zero by the number cell in A2. I was thinking factorial, but that's not it... Can't remember from my math days.

View Replies!   View Related
Create An Alphanumeric Sequence
I want to create a 4 character alphanumeric sequence. The sequence is to be like this: a1a1-z10z10. If it's a number, increment 1 until you reach 10, then move to the next column, if it's a letter, increment a until z, then move to the next column. Excel's autofill feature doesn't seem to work well for me due to the letters involved, so if someone has a macro handy that would be great.

View Replies!   View Related
Count Numbers In Sequence ....
I have a list of random numbers in column A the number range is from 0 to 20,000. What I am trying to do is to count the sequential numbers after each random number....sorry this is not really explained well so I willl use a numeric example.

A
23
24
25
33
34
60
77
80

Above is what I have in column A you can see that my number range starts a 23 so I would like to count the sequential numbers that occur from 23 in this case it would be count(23,24,25) giving the answer 3 I would like this answer to be inserted into column B next to 23 and the numbers that were counted in this formula then be deleted as rows (i.e. where the number 24 and 25 was counted then the rows containing these numbers are deleted), The macro moves then to count the next number.

In the case of just a single number then a value of 1 is entered into the column next to the number indicating that there was only one number in that sequence.


View Replies!   View Related
Identify Numbers In Sequence
I have a task to identify all the numbers in sequence in a worksheet, how do I do that?

Ex:
Column A
1
2
3
4
5
7
9
10
11
12
13
14
15
16
20
21
22

Desired output 1-5,7,9,10-16,20-22.


View Replies!   View Related
Instances That Occur In A Sequence
I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.

E.g. if a person is absent for 1 day the formula assigns a value of 1
if a person is off for 3 days in succession the formula assigns a value of 1


View Replies!   View Related
Find Gaps In Sequence
I have a file that contains addresses in column C. I need to find any gaps in addresses.

Ex:
211 Corbin Dr
213 Corbin Drive
214 Corbin Drive
123 Apple Drive
124 Apple Dr
124 Apple Dr
127 Apple Drive

I want to identify that there is a gap between 211 Corbin and 213 Corbin and 124 and 127 Apple Drive.

Currently the address including house number are in column c. However, I split the house number into a separate column and the street address in yet another column. There are also duplicates which I have identified by using conditional formatting to highlight the duplicates.

View Replies!   View Related
Average Numbers In Sequence
I have cells O1 to O300 numbers in random sequences of no more than 10

I need to average the 5 highest numbers in each sequence eg:

O P
1 2 16 (result)
2 20
3 4
4 6
5 14
6 12
7 8
8 10
9 16
10 18
11 blank cell
12 15 9 (result)
13 3
14 6
15 12
16 9
17 blank cell
18 blank cell
19 4 23 (result)
20 16
21 20
22 40
23 8
24 11
25 28
26 blank cell

down to 300 rows, the sequence of numbers can be from 1 to 10 but never
more than 10

I got a function : =IF(COUNT(O1:O10>=10,AVERAGE(LARGE(O1:O10,{1,2,3,4,5})),"
") but that works only for 10 numbers and not for less

View Replies!   View Related
Auto Fill Sequence
I am fighting with this for a longer time now and can't find a solution:

is it possible in Excel to generate a number sequence (to create a list for auto fill) representing numbers like this:

000001
000006
000002
000007
000003
000008
000004
000009
000005
000010
000011
000016
000012
000017
000013
000018
000014
000019
000015
000020

and so on till 001000

View Replies!   View Related
Highlight Numbers In Sequence
There are 350 numbers in a column, 7 digits in each cell. I want to see which cells have 3 or more consecutive numbers. Only looking at the last 3 digits. Example. 1960657. 1960658. 1960659. 1960660. Has 4 consecutive numbers.
Conditional formatting ? or other?

View Replies!   View Related
Removal Of Leader Sequence
I need a macro, preferably a "sub" that will search a long text input and when it finds the first instance of the letters "GSH" in that order removing all previous text before it. The amount of text characters, letters, and order before the gsh will vary.

For example:

FGHYTIUGSHIYKIOLHY --> GSHIYKIOLHY

View Replies!   View Related
Filling In Numbers In Sequence
I am trying to "auto fill" a column with numbers in sequential order. (1,2,3,4,5...) I typed in 1,2,3... in the first cells and clicked on the black corner dot to drag the sequence down. It keeps putting in 1,2,3,1,2,3,1,2,3..... I have also went into the advanced options and clicked the "enable fill handle and cell drag-and-drop" and also Alert before overwriting cells" and it still fills in 1,2,3,1,2,3....

View Replies!   View Related
Change Find Sequence
I want to change the sequence of find for the below code. I want to find the last entered row, instead of first entered. Now this code find first entered row.

View Replies!   View Related
AddIn Automation Sequence
how to automate AddIns sequence when opening Excel? I have 5 different AddIn (xla) and I have to launch them in a specific order (after the menu Essbase is installed - with xll) cause I'm adding rows in that menu. Excel is launching my xla's in alphabetical order but before Essbase is installed.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved