Referencing every nth cell in another sheet

[Alpay]

My problem is as follows.
Sheet1
Contains at regular row numbers with same column number
values which I want to reference. e.g.
A B
1 12 *
2 * *
3 * *
4 23 *
5 * *
6 * *
7 45 *
* denotes values which I'm not interested in.

Sheet2
Contains a summary of each nth row. e.g. for the above
example it would contain the values of A1, A4, A7, in
other words A(1+3i) with i=0..n
A
1 12
2 23
3 45

Now what I would like to do is in Sheet2, in cell A1
define that it is equal to 'Sheet1'A1 and in cell A2
define that it is equal to the reference in A1 of Sheet2
plus 3 rows. Similarly cell A3 in Sheet2 would say that it
is equal to the reference in A2 of Sheet2 plus 3 rows.

Anyone if this is possible with Worksheet Functions or do
I need to write a VB script?

Kind Regards

Alpay

 

[David McRitchie]

Hi Alpay,
If you remember the old computer paper with green bar striping
your problem kind of reminds me of that.

You can select the Column A or you can select the entire sheet
depending on if you just wan to color the first column or the
entire row.

Format, Conditional Formatting (with A1 selected)
Formula 1 is: =Mod(ROW($A1),3)=1

Since you also want a filtered list (my opinion) you can
place the above formula into an empty column at row 1 and
copy down with the fill-handle

example AK1 is your seed cell that filled down and is selected
AK1: =MOD(Row($A1),3)=1
with column AK selected
Data, Filter, AutoFilter
click on the dropdown on cell AK1 and choose True
you now have a filtered list.

To remove filtering, choose all from the dropdown.
or to completely remove
Data, Filter, AutoFilter (unclick)

You will see the value you are not interested in to the right of
the first column but sounds close to what you asked for.

Otherwise you can use a macro to create another sheet with
only those values it is a simple loop.

Instructions to install a macro are in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Do your testing in another workbook strictly for testing such
as a copy of your workbook.

The following macro will create a new sheet with every third
value from Column A of the original sheet.

Public Sub Every_Third()
Dim nCol As Long, nRow As Long, cRow As Long, lastrow As Long
Dim wsSource As Worksheet, wsNew As Worksheet
Dim lastcell As Range
nCol = 0
nRow = 1
Dim I As Long
Set lastcell = Cells.SpecialCells(xlLastCell)
lastrow = lastcell.Row
Set wsSource = ActiveSheet
Set wsNew = Worksheets.Add
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For cRow = 1 To lastrow Step 3
If cRow > lastrow Then GoTo done
Cells(nRow, 1) = wsSource.Cells(cRow, 1)
nRow = nRow + 1
Next cRow
done:
Application.ScreenUpdating = True 'place at end when debugged
Application.DisplayAlerts = True
End Sub

 

[Max]

Oops, typo - line below should read " ... bottom right corner..." (not left)

"Select B1:B2, drag down the bottom left corner to fill the series 1, 4, 7,
10, 13 ... down col B"

 

[Allan Rogg]

My problem is as follows.
Sheet1
Contains at regular row numbers with same column number
values which I want to reference. e.g.
A B
1 12 *
2 * *
3 * *
4 23 *
5 * *
6 * *
7 45 *
* denotes values which I'm not interested in.

Sheet2
Contains a summary of each nth row. e.g. for the above
example it would contain the values of A1, A4, A7, in
other words A(1+3i) with i=0..n
A
1 12
2 23
3 45

Now what I would like to do is in Sheet2, in cell A1
define that it is equal to 'Sheet1'A1 and in cell A2
define that it is equal to the reference in A1 of Sheet2
plus 3 rows. Similarly cell A3 in Sheet2 would say that it
is equal to the reference in A2 of Sheet2 plus 3 rows.

Anyone if this is possible with Worksheet Functions or do
I need to write a VB script?

Kind Regards

Alpay

Enter into column A in Sheet2

=INDIRECT("Sheet1!A"&3*ROW()-2)

This will put Sheet1!A1 in Sheet2!A1, Sheet1!A4 in Sheet2!A2, etc.

Allan Rogg

 

[Alpay]

OK Max, Allan and David

Thanks to you all for the help!