WOT Excel Guru needed, Help me with a simple task that just can't figure out |
|
Porsche, and the Porsche crest are registered trademarks of Dr. Ing. h.c. F. Porsche AG.
This site is not affiliated with Porsche in any way. Its only purpose is to provide an online forum for car enthusiasts. All other trademarks are property of their respective owners. |
|
WOT Excel Guru needed, Help me with a simple task that just can't figure out |
Porcharu |
Apr 18 2006, 08:19 PM
Post
#1
|
Senior Member Group: Members Posts: 1,314 Joined: 27-January 05 From: Campbell, CA Member No.: 3,518 Region Association: Northern California |
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 |
Foster |
Apr 18 2006, 08:41 PM
Post
#2
|
Thread Killer Group: Members Posts: 110 Joined: 26-May 05 From: Great Lakes Member No.: 4,151 Region Association: Upper MidWest |
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 (IMG:style_emoticons/default/aktion035.gif) ) You can do this in as many column as you like. Basically you just create the pattern and excel should recognize and repeat it. (IMG:style_emoticons/default/type.gif) Hope this helps. |
sgomes |
Apr 18 2006, 08:49 PM
Post
#3
|
Electric Member Group: Members Posts: 815 Joined: 6-May 04 From: Campbell, CA Member No.: 2,029 |
...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. |
Porcharu |
Apr 18 2006, 08:56 PM
Post
#4
|
Senior Member Group: Members Posts: 1,314 Joined: 27-January 05 From: Campbell, CA Member No.: 3,518 Region Association: Northern California |
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! (IMG:style_emoticons/default/mad.gif)
|
Brian_Boss |
Apr 18 2006, 09:17 PM
Post
#5
|
Member Group: Members Posts: 324 Joined: 3-June 03 From: Dallas, TX Member No.: 781 |
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. |
Foster |
Apr 18 2006, 09:24 PM
Post
#6
|
Thread Killer Group: Members Posts: 110 Joined: 26-May 05 From: Great Lakes Member No.: 4,151 Region Association: Upper MidWest |
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! (IMG:style_emoticons/default/mad.gif) Wow, that's weird. (IMG:style_emoticons/default/screwy.gif) 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) |
Part Pricer |
Apr 19 2006, 09:18 AM
Post
#7
|
Believe everything I post Group: Benefactors Posts: 1,825 Joined: 28-December 02 From: Danbury, CT Member No.: 35 |
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. |
Air_Cooled_Nut |
Apr 19 2006, 03:26 PM
Post
#8
|
914 Ronin - 914 owner who lost his 914club.com Group: Members Posts: 1,748 Joined: 19-April 03 From: Beaverton, Oregon Member No.: 584 Region Association: None |
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.
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 (IMG:style_emoticons/default/smile.gif) 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. |
Porcharu |
Apr 19 2006, 06:57 PM
Post
#9
|
Senior Member Group: Members Posts: 1,314 Joined: 27-January 05 From: Campbell, CA Member No.: 3,518 Region Association: Northern California |
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 |
Lo-Fi Version | Time is now: 17th May 2024 - 02:59 PM |
All rights reserved 914World.com © since 2002 |
914World.com is the fastest growing online 914 community! We have it all, classifieds, events, forums, vendors, parts, autocross, racing, technical articles, events calendar, newsletter, restoration, gallery, archives, history and more for your Porsche 914 ... |