I need to something really simple - but I can't figure out how to do it.
I want to use the autofill feature to increment a cell value by 2 like this
=Sheet2!C1
=Sheet2!C3
=Sheet2!C5
=Sheet2!C7
etc.
I need to use the autofill or something automatic or programmed becuase I need to do it for about 1000 cells several times. I have tried everything I can think of. I can probably use VB to program something up but I haven't done any programing for years and I really suck at it anyway.
Thanks
Full Version: WOT Excel Guru needed
QUOTE(Porcharu @ Apr 18 2006, 09:19 PM) 
I need to something really simple - but I can't figure out how to do it.
I want to use the autofill feature to increment a cell value by 2 like this
=Sheet2!C1
=Sheet2!C3
=Sheet2!C5
=Sheet2!C7
etc.
I need to use the autofill or something automatic or programmed becuase I need to do it for about 1000 cells several times.
Hard to describe, easy to show, but I'll try.
Type your formula in 3 or 4 rows like you've shown.
Highlight the cells you just filled.
put your mouse on the little black box in the lower right corner of the selection you just made.
Hold down the left mouse button and drag down to as many cells as you want to fill.
Your selection box will grow and the formulas will be automatically incremented.
(heh heh, he said incremented
)You can do this in as many column as you like. Basically you just create the pattern and excel should recognize and repeat it.
Hope this helps.
...or if you have a fairly recent version of Excel....
put your first number in the first cell.
go to Edit/fill/series
this will bring up a dialog that you can pick to fill in a row or column and what type of increment and when to stop.
There is always a million ways to do something.
put your first number in the first cell.
go to Edit/fill/series
this will bring up a dialog that you can pick to fill in a row or column and what type of increment and when to stop.
There is always a million ways to do something.
I should have been more clear - I need to increment the cell number not the values in the cell. I have tried both of those ways - doesn't work that way it alway increments the CELL number by 1. I have tried to "teach" it by typing in about 20 cells and it refuses to "learn". I tried all of the fill options. I tried letting it increment by 1 and going back and selecting every other cell then copy-paste and the stupid program "re-increments" the series back to 1! 
This will work in any version. Put your formula with consecutive references in two cells. Type a "1" and a "2" in the next column, like this:
=Sheet2!C1 1
=Sheet2!C2 2
Copy the above (don't use the drag to extend) and paste to double the required number of rows. Sort by the 1 / 2 column and delete the unwanted cells.
=Sheet2!C1 1
=Sheet2!C2 2
Copy the above (don't use the drag to extend) and paste to double the required number of rows. Sort by the 1 / 2 column and delete the unwanted cells.
QUOTE(Porcharu @ Apr 18 2006, 09:56 PM)
I should have been more clear - I need to increment the cell number not the values in the cell. I have tried both of those ways - doesn't work that way it alway increments the CELL number by 1. I have tried to "teach" it by typing in about 20 cells and it refuses to "learn". I tried all of the fill options. I tried letting it increment by 1 and going back and selecting every other cell then copy-paste and the stupid program "re-increments" the series back to 1!
Wow, that's weird.
I managed to do what you wanted (sorta) but you have to skip a row in your pattern =Sheet1!A1
=Sheet1!A3
=Sheet1!A5
(I'm sure if you were incrementing by 3 you'd have to skip 2 rows) But I don't think that will get you what you want unless you can live with blank rows. You could always delete them, but that's a lot of extra work.
Burning my guru hat now. (I stole it anyway)
QUOTE(Porcharu @ Apr 18 2006, 10:19 PM)
I need to something really simple - but I can't figure out how to do it.
I want to use the autofill feature to increment a cell value by 2 like this
=Sheet2!C1
=Sheet2!C3
=Sheet2!C5
=Sheet2!C7
etc.
I need to use the autofill or something automatic or programmed becuase I need to do it for about 1000 cells several times. I have tried everything I can think of. I can probably use VB to program something up but I haven't done any programing for years and I really suck at it anyway.
Thanks
What you need to do can easily be done with the INDEX function, but it is going to require that you set the increment separately.
Here is how I would do it. Let's assume the range of information that you want to reference is contained in Sheet2!$A$1:$A$50. It would be easiest to NAME that range, but we'll leave that for another lesson.
Now, there are two things you need to do. First, I put this formula in cell A1 of my current sheet and then filled it down.
=INDEX(Sheet2!$A$1:$A$50,B1)
In column B of that sheet, I filled in the following values:
1
3
5
7
9
11
13
15
17
19
21
etc.
The formula increments only to odd numbers based upon the information in Column B. This then looks up the value of that row in the range on Sheet2.
I can put this together in a couple of minutes for you. Let me know.
I'm not going to call myself a guru but I have made my living by programming Excel. Here is yet another way to do this, using VBA.
Option Explicit
Sub UpdateCell()
'by Toby Erkson
'19April2006
'
'Takes the formula in the active cell, increments it by 2, moves to the cell below
'the active cell, activates the cell, and drops in the new formula.
'
Dim CurrentFormula As String, NextValue As Double, StartHere As Integer
Dim NextRow As Long, NewFormula As String
CurrentFormula = ActiveCell.Formula 'Pull formula
StartHere = InStr(CurrentFormula, "C") 'Find the Column in the formula
NextValue = Str(Val(Right(CurrentFormula, Len(CurrentFormula) - StartHere)) + 2) 'Increment column
NewFormula = Left(CurrentFormula, StartHere) & NextValue 'Build new formula
NextRow = ActiveCell.Row + 1 'Increment row
If NextRow < 65537 Then 'Make sure we stay on worksheet
Cells(NextRow, ActiveCell.Column).Select 'Move to next row
Selection.Value = NewFormula 'Drop new formula into cell
End If
End Sub
Dump this code into the Module section. Since you've coded before I'll assume you can do this easy step. Alt + F11 will open the VBA Editor in Excel.
I would recommend binding the macro to a key sequence i.e. assign it to a Ctrl + key for faster use
I like to keep my code simple and easy to understand. There are other ways this can be coded, and most likely even fancier, but they would likely be more difficult to understand for any beginner.
CODE
Option Explicit
Sub UpdateCell()
'by Toby Erkson
'19April2006
'
'Takes the formula in the active cell, increments it by 2, moves to the cell below
'the active cell, activates the cell, and drops in the new formula.
'
Dim CurrentFormula As String, NextValue As Double, StartHere As Integer
Dim NextRow As Long, NewFormula As String
CurrentFormula = ActiveCell.Formula 'Pull formula
StartHere = InStr(CurrentFormula, "C") 'Find the Column in the formula
NextValue = Str(Val(Right(CurrentFormula, Len(CurrentFormula) - StartHere)) + 2) 'Increment column
NewFormula = Left(CurrentFormula, StartHere) & NextValue 'Build new formula
NextRow = ActiveCell.Row + 1 'Increment row
If NextRow < 65537 Then 'Make sure we stay on worksheet
Cells(NextRow, ActiveCell.Column).Select 'Move to next row
Selection.Value = NewFormula 'Drop new formula into cell
End If
End Sub
Dump this code into the Module section. Since you've coded before I'll assume you can do this easy step. Alt + F11 will open the VBA Editor in Excel.
I would recommend binding the macro to a key sequence i.e. assign it to a Ctrl + key for faster use
I like to keep my code simple and easy to understand. There are other ways this can be coded, and most likely even fancier, but they would likely be more difficult to understand for any beginner.
Thanks for all the help everyone. I managed to fiqure out a brute force method combined with a feature in the 'PUP6' addon pack that is working just fine.
It's not pretty - (or elegant like Toby's or Paul's methods) but it works.
Steve
It's not pretty - (or elegant like Toby's or Paul's methods) but it works.
Steve
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.