Linking formula
 

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

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

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

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

"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

It seems I used your original cells of B8 and C8 to start.

This will start from B1 and C1:

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

--
HTH,

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


"RagDyer" wrote in message
...
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

Believe the OP's latest post did say:
.. the value from 'ED Numbers'!B1,
and the value from 'ED Numbers'!C1

(I fell for the line "8" which s/he posted yesterday)

in which case, perhaps a slight tweak to your formula in B1

Try this in B1:
="HD_SN "&'ED Numbers'!$A$23&INDEX('ED Numbers'!$B:$B,
(ROW(1:1)-1)/3+1)&INDEX('ED Numbers'!$C:$C,(ROW(1:1)-1)/3+1)&"T"
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

And ... allow this old guy to *properly* eliminate the superfluous absolutes
where the entire column is referenced:

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

--
Regards,

RD

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

"RagDyer" wrote in message
...
It seems I used your original cells of B8 and C8 to start.

This will start from B1 and C1:

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

--
HTH,

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


"RagDyer" wrote in message
...
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

Seems we both caught it (B8 - B1) at about the same time.

And, to prove our great minds work in unison, we both went with the "+1",
didn't we?<g

I'm confused with your INT though?!?!
--
Regards,

RD

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

"Max" wrote in message
...
Believe the OP's latest post did say:
.. the value from 'ED Numbers'!B1,
and the value from 'ED Numbers'!C1

(I fell for the line "8" which s/he posted yesterday)

in which case, perhaps a slight tweak to your formula in B1

Try this in B1:
="HD_SN "&'ED Numbers'!$A$23&INDEX('ED Numbers'!$B:$B,
(ROW(1:1)-1)/3+1)&INDEX('ED Numbers'!$C:$C,(ROW(1:1)-1)/3+1)&"T"
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

.. I'm confused with your INT though?!?!

I used: ... INT((ROWS($A$1:A1)-1)/3) ..
as it produces a triplet series of zero's, 1's, 2's .. when copied down
which nicely syncs and increments the row for both:
'ED Numbers'!B1 & 'ED Numbers'!C1
in every 4th line when the formula in the starting cell is copied down

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

INT with the "+1" .. that is <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Max Wrote:
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
---

Thanks Max, this does the job beatifully! I really appreciate your
help.

Kind regards,
Jonathan


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

RagDyer Wrote:
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!
==============================================

Thanks for the tip RD, but I like Max's idea better - it is a simpler
operation.

Kind regards,
Jonathan


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

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jonibenj" wrote in
message ...

RagDyer Wrote:
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!
==============================================

Thanks for the tip RD, but I like Max's idea better - it is a simpler
operation.

Kind regards,
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
.. Thanks Max, this does the job beatifully!
I really appreciate your help.

Glad it helped, and thanks for the feedback !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--