Linking formula

J

Jonibenj

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
 
R

RagDyer

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!
==============================================
 
R

Rowan

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
 
J

Jonibenj

Dear RD,

Thanks for the tip - it works like a charm on some testing data I mad
up. However, I also have text strings on the other two lines of shee
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 wor
properly, I should only use the 'fill handle' on cells A1-A3 instead o
A1-A6.

However, my links are actually a bit more complex than this. I hav
quite a long formula including text strings and references. I als
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, excep
for one problem - the references B8 & C8 do not change! It is lik
they are operating as absolute references, but as you can see, the
aren't!

Any advances??

Jonatha
 
J

Jonibenj

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

Jonatha
 
M

Max

.. 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
Click to expand...

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
 
M

Max

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"
 
M

Max

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!
Click to expand...

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
 
R

RagDyer

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?
 
J

Jonibenj

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
 
M

Max

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.
Click to expand...

The formula can be placed in any starting cell in Sheet2,
not necessarily in A1

--
 
R

RagDyer

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!
==============================================
 
R

RagDyer

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 said:
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!
==============================================



message news:[email protected]...
Click to expand...
 
M

Max

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

(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:
Click to expand...
="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"
 
R

RagDyer

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"
 
R

RagDyer

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?!?!
 
M

Max

.. 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
 
J

Jonibenj

Max said:
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 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
Click to expand...
 
J

Jonibenj

RagDyer said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help!!!!!! 2
Filtering of data in a master excel sheet from multiple sheets 3
Linking sheet between two files 4
How to update data from multiple sheets to one specific sheets 4
Copying cell formulas 1
Calculate data from different sheets 4
Adding consistant cells. 4
Formula from a single cell to a merged cell 4

Top