I want to create a secondary excel sheet that combines information from a
primary sheet. The primary sheet contains an extract of data from Pastel
which is an asortment of data in rows and columns. However, the data is
repeated at regular intervals. Eg. Cell B4, B17, B30 contain same data for
different clients. I want a formula that I can drag and drop which increases
the cell reference from =sheet1!B4 to =sheet1!B17, to =sheet1!B30 etc. Sheet
2 will have all data in columns, the first row of which I have copied across
to sheet 2 as =B4; =B5; =A7;=D4; etc.
Hope this makes sense.
HELP
--
GJC

I am not sure if it makes sense or not, do you want to increment with 13 per
row you copy down so you will get what's in Sheet1 B4, B17, B30, B43, B56 and
so on? If so this formula will do that

=OFFSET(Sheet1!$B$4,ROW(1:1)*13-13,)

Regards

Peo Sjoblom

"Big G" wrote:

I want to create a secondary excel sheet that combines information from a
primary sheet. The primary sheet contains an extract of data from Pastel
which is an asortment of data in rows and columns. However, the data is
repeated at regular intervals. Eg. Cell B4, B17, B30 contain same data for
different clients. I want a formula that I can drag and drop which increases
the cell reference from =sheet1!B4 to =sheet1!B17, to =sheet1!B30 etc. Sheet
2 will have all data in columns, the first row of which I have copied across
to sheet 2 as =B4; =B5; =A7;=D4; etc.
Hope this makes sense.
HELP
--
GJC

I want a formula that I can drag and drop which increases
the cell reference from =sheet1!B4 to =sheet1!B17,
to =sheet1!B30 etc.

One way is to use INDIRECT

In Sheet2
--------------
Put in say, A2:
=INDIRECT("'"&"Sheet1"&"'!B"&ROW(A1)*13-13+4)
Copy down

A2, A3, A4, etc will return the equivalents of:
=Sheet1!B4, =Sheet1!B17, =Sheet1!B30
(in increments of 13 rows as you copy down)

And to return likewise in say, B2, B3, B4 down,
but starting with link to B5 in Sheet1 (instead of B4), viz.:
=Sheet1!B5, =Sheet1!B18, =Sheet1!B31, etc
just change the last number "4" to "5" in the formula, i.e.:

Put in B2:
=INDIRECT("'"&"Sheet1"&"'!B"&ROW(A1)*13-13+5)
Copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Big G wrote in message
...
I want to create a secondary excel sheet that combines information from a
primary sheet. The primary sheet contains an extract of data from Pastel
which is an asortment of data in rows and columns. However, the data is
repeated at regular intervals. Eg. Cell B4, B17, B30 contain same data for
different clients. I want a formula that I can drag and drop which
increases
the cell reference from =sheet1!B4 to =sheet1!B17, to =sheet1!B30 etc.
Sheet
2 will have all data in columns, the first row of which I have copied
across
to sheet 2 as =B4; =B5; =A7;=D4; etc.
Hope this makes sense.
HELP
--
GJC