Help with Formula
 

I have two sheets with data. The first sheet, which contains the source
information, has data in the range A1:A4 as follows:

Company A
Company B
Company C
Company D

The second sheet, which I want to link to data in sheet 1, will also have a
column A which links to the above data. However, I want to merge cells so
that Range A1:A3 are merged, and then link this cell to "Company A".
Thereafter, I would like to be able to copy and paste down and have the Range
A4:A6 contain "Company B", range A7:A9 contain "Company C", etc.

The problem is that when I copy from the merged cell (A1:A3 which is linked
to A1 on Sheet 1) and paste to the merged cell (A4:A6), instead of taking the
next sequential on sheet 1 (A2), it instead links to cell A4 ("Company D").
Any ideas on how I can adjust for this? Thx.

One way ..

In Sheet1
---------
Put in B1: =INDEX(A:A,INT((ROWS($A$1:A1)-1)/3)+1)

Copy B1 down until zeros appear, signalling exhaustion of data from col A.
Col B will triplicate the values in col A, viz.:

Company A
Company A
Company A
Company B
Company B
Company B
etc

In Sheet2, just point to col B in the link formula & copy down,
i.e. in the 1st merged cell A1:A3 (effectively only cell A1)
put the formula: =Sheet1!B1
then fill down to yield:

Company A
Company B
Company C
etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jason" wrote in message
...
I have two sheets with data. The first sheet, which contains the source
information, has data in the range A1:A4 as follows:

Company A
Company B
Company C
Company D

The second sheet, which I want to link to data in sheet 1, will also have
a
column A which links to the above data. However, I want to merge cells so
that Range A1:A3 are merged, and then link this cell to "Company A".
Thereafter, I would like to be able to copy and paste down and have the
Range
A4:A6 contain "Company B", range A7:A9 contain "Company C", etc.

The problem is that when I copy from the merged cell (A1:A3 which is
linked
to A1 on Sheet 1) and paste to the merged cell (A4:A6), instead of taking
the
next sequential on sheet 1 (A2), it instead links to cell A4 ("Company
D").
Any ideas on how I can adjust for this? Thx.