I have two sheets, and I want to link information from one to the other.
The difficulty is that on sheet 1, the data is entered in every
consecutive cell down a column, but on sheet 2, I want that data linked
to every third cell down the column. So, it goes like this:

A1 on sheet 1 - A1 on sheet 2
A2 on sheet 1 - A4 on sheet 2
A3 on sheet 1 - A7 on sheet 2

and so on.

Can somebody help?
Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466994

Start with entering text strings that *look* like your formulas by prefixing
the equal sign with a <Space.

on Sheet2, in A1 enter
<Space=Sheet1!A1

In A4 enter
<Space=Sheet1!A2

Now, select A1 to A6

Click on the "fill handle" of that 6 cell selection,
And drag down to copy as needed.

Then, while the copy range is *still* selected,
<Edit <Replace
In "Find What", enter
<Space=
In "Replace With", enter
=
Then click <Replace All

And you're done!
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Jonibenj" wrote in
message ...

I have two sheets, and I want to link information from one to the other.
The difficulty is that on sheet 1, the data is entered in every
consecutive cell down a column, but on sheet 2, I want that data linked
to every third cell down the column. So, it goes like this:

A1 on sheet 1 - A1 on sheet 2
A2 on sheet 1 - A4 on sheet 2
A3 on sheet 1 - A7 on sheet 2

and so on.

Can somebody help?
Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile:
http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466994

Dear RD,

Thanks for the tip - it works like a charm on some testing data I made
up. However, I also have text strings on the other two lines of sheet
2 that need copying down also, thus:

='Sheet1'!A1
HD_EX Y
HD
='Sheet1'!A2
HD_EX Y
HD

and so on.

By experimenting, I actually found that to make your suggestion work
properly, I should only use the 'fill handle' on cells A1-A3 instead of
A1-A6.

However, my links are actually a bit more complex than this. I have
quite a long formula including text strings and references. I also
have text strings on the two intervening lines which need copying also.
Here is a sample of the three lines that need copying:

="HD_SN "&'ED Numbers'!$A$23&'ED Numbers'!B8&'ED Numbers'!C8&"T"
HD_EX Y
HD

When I perform your copy procedure on this, it seems to work, except
for one problem - the references B8 & C8 do not change! It is like
they are operating as absolute references, but as you can see, they
aren't!

Any advances??

Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466994

"Jonibenj" wrote
.. However, I also have text strings on the other two lines of
sheet 2 that need copying down also, thus:

='Sheet1'!A1
HD_EX Y
HD
='Sheet1'!A2
HD_EX Y
HD

Perhaps try this in the interim ..

In Sheet2
---------
Put in A1:

=IF(MOD(ROWS($A$1:A1)-1,3)=0,INDEX(Sheet1!A:A,INT((ROWS($A$1:A1)-1)/3)+1),IF
(MOD(ROWS($A$1:A1)-1,3)=1,"HD_EX Y","HD"))

Copy A1 down as far as required

The above seems to return the desired copy pattern from Sheet1,
interspersed with the 2 specified lines of text
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Clarification:

I presumed you had this formula below
in Sheet1's A1, which is copied down:

="HD_SN "&'ED Numbers'!$A$23&'ED Numbers'!B8&'ED Numbers'!C8&"T"

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Here is a sample of the three lines that need copying:

="HD_SN "&'ED Numbers'!$A$23&'ED Numbers'!B8&'ED Numbers'!C8&"T"
HD_EX Y
HD

When I perform your copy procedure on this, it seems to work, except
for one problem - the references B8 & C8 do not change! It is like
they are operating as absolute references, but as you can see, they
aren't!

And if you wanted the above to be done directly, assuming the formula above
(in line 1) is the "starting" line for copying down, then one way ..

In Sheet2
---------
Put in A1:

=IF(MOD(ROWS($A$1:A1)-1,3)=0,"HD_SN "&'ED Numbers'!$A$23&
INDIRECT("'ED Numbers'!B"&INT((ROWS($A$1:A1)-1)/3)+8)&
INDIRECT("'ED Numbers'!C"&INT((ROWS($A$1:A1)-1)/3)+8)&"T",
IF(MOD(ROWS($A$1:A1)-1,3)=1,"HD_EX Y","HD"))

Copy A1 down as far as required

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

I'm a bit confused.

First you show 3 rows per set, one formula and two text, then you mention 4
rows per set, one formula, a second formula, then 2 rows of text.

Do you have both types of sets?

Also, are you adding a <Space in front of your second formula, or are you
copying that as an actual formula?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jonibenj" wrote in
message ...

Dear RD,

Thanks for the tip - it works like a charm on some testing data I made
up. However, I also have text strings on the other two lines of sheet
2 that need copying down also, thus:

='Sheet1'!A1
HD_EX Y
HD
='Sheet1'!A2
HD_EX Y
HD

and so on.

By experimenting, I actually found that to make your suggestion work
properly, I should only use the 'fill handle' on cells A1-A3 instead of
A1-A6.

However, my links are actually a bit more complex than this. I have
quite a long formula including text strings and references. I also
have text strings on the two intervening lines which need copying also.
Here is a sample of the three lines that need copying:

="HD_SN "&'ED Numbers'!$A$23&'ED Numbers'!B8&'ED Numbers'!C8&"T"
HD_EX Y
HD

When I perform your copy procedure on this, it seems to work, except
for one problem - the references B8 & C8 do not change! It is like
they are operating as absolute references, but as you can see, they
aren't!

Any advances??

Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile:
http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466994

OK. Let me see if I can spell it out clearly.

Sheet1 is called 'ED Numbers', and has the following information:

- a single value in A23
- a list of values in column B
- a list of values in column C

Sheet2 has a list of results in column B, composed of triplet sets of
data:

Row1 - B1 combines a mixture of text strings, the value from 'ED
Numbers'!A23, the value from 'ED Numbers'!B1, and the value from 'ED
Numbers'!C1
Row2 - B2 contains the text string 'HD_EX Y'
Row3 - B3 contains the text string 'HD'

This set is repeated down column B, so that the data for Row4 is
extracted from A23, B2 & C2 of Sheet1, the data for Row7 is taken from
A23, B3 & C3 of Sheet1, and so on.

Does this clarify things a bit? If I knew how to attach the workbook
for you to see, I would. Maybe I could email it to you?

Many thanks,
Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466994

As an alternative to try out, if you put this slight mod of the formula
suggested earlier (with "8" changed to "1")
in Sheet2's A1:

=IF(MOD(ROWS($A$1:A1)-1,3)=0,"HD_SN "&'ED Numbers'!$A$23&
INDIRECT("'ED Numbers'!B"&INT((ROWS($A$1:A1)-1)/3)+1)&
INDIRECT("'ED Numbers'!C"&INT((ROWS($A$1:A1)-1)/3)+1)&"T",
IF(MOD(ROWS($A$1:A1)-1,3)=1,"HD_EX Y","HD"))

and then copy A1 down as far as required,
you'll find it delivers exactly what you're after, viz.:

... the data for Row4 is
extracted from A23, B2 & C2 of Sheet1,
the data for Row7 is taken from
A23, B3 & C3 of Sheet1, and so on.

The formula can be placed in any starting cell in Sheet2,
not necessarily in A1
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jonibenj" wrote in
message ...

OK. Let me see if I can spell it out clearly.

Sheet1 is called 'ED Numbers', and has the following information:

- a single value in A23
- a list of values in column B
- a list of values in column C

Sheet2 has a list of results in column B, composed of triplet sets of
data:

Row1 - B1 combines a mixture of text strings, the value from 'ED
Numbers'!A23, the value from 'ED Numbers'!B1, and the value from 'ED
Numbers'!C1
Row2 - B2 contains the text string 'HD_EX Y'
Row3 - B3 contains the text string 'HD'

This set is repeated down column B, so that the data for Row4 is
extracted from A23, B2 & C2 of Sheet1, the data for Row7 is taken from
A23, B3 & C3 of Sheet1, and so on.

Does this clarify things a bit? If I knew how to attach the workbook
for you to see, I would. Maybe I could email it to you?

Many thanks,
Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile:
http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466994

Try this in B1:
="HD_SN "&'ED Numbers'!$A$23&INDEX('ED
Numbers'!$B$8:$B$100,(ROW(1:1)-1)/3+1)&INDEX('ED
Numbers'!$C$8:$C$100,(ROW(1:1)-1)/3+1)&"T"

This is B2:
HD_EX Y

This in B3:
HD

Select all 3 cells, and drag down to copy.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Jonibenj" wrote in
message ...

OK. Let me see if I can spell it out clearly.

Sheet1 is called 'ED Numbers', and has the following information:

- a single value in A23
- a list of values in column B
- a list of values in column C

Sheet2 has a list of results in column B, composed of triplet sets of
data:

Row1 - B1 combines a mixture of text strings, the value from 'ED
Numbers'!A23, the value from 'ED Numbers'!B1, and the value from 'ED
Numbers'!C1
Row2 - B2 contains the text string 'HD_EX Y'
Row3 - B3 contains the text string 'HD'

This set is repeated down column B, so that the data for Row4 is
extracted from A23, B2 & C2 of Sheet1, the data for Row7 is taken from
A23, B3 & C3 of Sheet1, and so on.

Does this clarify things a bit? If I knew how to attach the workbook
for you to see, I would. Maybe I could email it to you?

Many thanks,
Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile:
http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466994

One way:

Sub GetRef()
Dim eRow As Long
Dim i As Long
eRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Sheet2")
.Cells(1, 1).Formula = "=Sheet1!A1"
For i = 2 To eRow
.Cells((i * 2) + (i - 2), 1).Formula = "=Sheet1!A" & i
Next i
End With
End Sub

Hope this helps
Rowan

Jonibenj wrote:
I have two sheets, and I want to link information from one to the other.
The difficulty is that on sheet 1, the data is entered in every
consecutive cell down a column, but on sheet 2, I want that data linked
to every third cell down the column. So, it goes like this:

A1 on sheet 1 - A1 on sheet 2
A2 on sheet 1 - A4 on sheet 2
A3 on sheet 1 - A7 on sheet 2

and so on.

Can somebody help?
Jonathan

Thanks Rowan, but I don't know anything about macros! RD's answer seems
to be more down my line, if I can sort out the little difficulty I'm
having!

Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=466994