stupid questions

[KK1966]

Hi

I have stupid questions as I?m not familiars excel,

May I know if formulas can set up 5 rows to go?

Since I have the data have too many row at the COL ?A,
A1 = A/C numers
A2 = products code
A3 = address
A4 = partnership name
A5 = volumes

At the COL D can I just drag the cells to down that?s can just returns the A/C numbers.

Sample:

Range D1 = A1
D2 = A6
D3 = A11 ?


does it be formulas support ?



Thanks very much

[rbrhodes]

Hi kk,

I don't know of any way to do that with a formula. It could be done with VBA...

In your workbook:

- Press <ALT+F11>

- Use Insert/Module in the window that opens

- Copy and paste the code into that 'Module'

- File/Close and Return to Excel

- Save the file

- Press <Alt+F8> to get a list of Macros

- Choose "FillD" and click Run






[VBA]
Option Explicit
Sub FillD()
Dim i As Long
Dim dRow As Long
Dim gRow As Long
Dim countr As Long
Dim LastRow As Long
' Get last row of data ColUmn A
LastRow = Range("a65536").End(xlUp).Row

' Define loop
LastRow = (LastRow / 5) + 1

' Start in row 1
dRow = 1
gRow = 1

' Do all
For i = 1 To LastRow
Cells(dRow, "D") = "=A" & gRow
' Increment destination row
dRow = dRow + 1
' Increment get row
gRow = gRow + 5
Next i
End Sub

[/VBA]

[KK1966]

Wow, that?s very great,
Thank you very much your help

Actually My row start from A10 to last row,

How can it to modify your code

Pleased

[KK1966]

Dear rbrhodes

I get understanding, Grow=5 that's ok

Thanks very much
Thanks
thanks

[Bob Phillips]

D1: =INDEX(A:A,(ROW(A1)-1)*5+1)

and copy down

[Bob Phillips]

[VBA]

For i = 1 To LastRow
Cells(dRow, "D") = "=A" & gRow
' Increment destination row
dRow = dRow + 1
' Increment get row
gRow = gRow + 5
Next i
End Sub

[/VBA]

Simpler

[vba]

For i = 1 To LastRow
Cells(i, "D") = "=A" & (i - 1) * 5 + 1
Next i
[/vba]