Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference autofill.
I am pulling data for certain cells on another sheet. It is every 7 cell that I need from one column of about 1000 rows. When I work on a referencing the cells I am unable to properly autofill. For example I enter =i5 in a cell and =i12 below that that cell and then autofill then next cell and get =i7, instead of =i19 How can I set this up to count by sevens so when when I fill I am able to reference i5, i12, i19, ect without typing everything in by hand? Thank you. -- mtnone ------------------------------------------------------------------------ mtnone's Profile: http://www.excelforum.com/member.php...o&userid=34950 View this thread: http://www.excelforum.com/showthread...hreadid=546823 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference autofill.
One way of doing it would be to set up a helper column and use the OFFSET function. Try this on a blank sheet as an example: For the helper column in this case column A enter 5, =A1+7, =A2+7, = A3+7... so the column will display 5, 12,19, 26 etc. In my example, I used column B as a numerical column 1,2,3,4,...to prove a point; the aim being to pick off every 7th id which we will come to next using the OFFSET function. In cell C1 enter "=OFFSET(A1,A1+1,1)" and then fill this down - and you have just picked up every 7th reference. All you need to do now is modify the formula to work across sheets and you're there. Enjoy -- MartinShort Software Tester ------------------------------------------------------------------------ MartinShort's Profile: http://www.excelforum.com/member.php...o&userid=22034 View this thread: http://www.excelforum.com/showthread...hreadid=546823 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference autofill.
Hi,
you can try a formula like this On the fist line =INDIRECT(address(row();9;;;"Plan1")) For the next lines add 6 rows =INDIRECT(address(row()+6;9;;;"Plan1")) Assuming that "plan1" is the name of the spreadsheet and the is collum "I" (the 9th) hope it help and thanks for the feedback regards from Brazil Marcelo "mtnone" escreveu: I am pulling data for certain cells on another sheet. It is every 7 cell that I need from one column of about 1000 rows. When I work on a referencing the cells I am unable to properly autofill. For example I enter =i5 in a cell and =i12 below that that cell and then autofill then next cell and get =i7, instead of =i19 How can I set this up to count by sevens so when when I fill I am able to reference i5, i12, i19, ect without typing everything in by hand? Thank you. -- mtnone ------------------------------------------------------------------------ mtnone's Profile: http://www.excelforum.com/member.php...o&userid=34950 View this thread: http://www.excelforum.com/showthread...hreadid=546823 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference autofill.
try this mark first cell u want formula in
Sub Lasey() Dim i For i = 5 To 1000 Step 7 If Range("A" & i) < "" Then ' Change A to urs kolumn ActiveCell.Formula = "=A" & i ' Change A to urs kolumn ActiveCell.Offset(1, 0).Activate End If Next End Sub |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference autofill.
Sub Lasey2()
Dim i For i = 5 To 1000 Step 7 If Range("Sheet1!A" & i) < "" Then ActiveCell.Formula = "=Sheet1!A" & i ActiveCell.Offset(1, 0).Activate End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Circular reference toolbar not working | Excel Discussion (Misc queries) | |||
How can I autofill a series to reference non adjacent cells? | Excel Discussion (Misc queries) | |||
More- AutoFill with Non-Seqeuntial Cell References ? | Excel Worksheet Functions | |||
Relative reference autofill increment other than +1 | Excel Discussion (Misc queries) |