Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
"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 -- |
#7
|
|||
|
|||
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 -- |
#8
|
|||
|
|||
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 -- |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 -- |
#15
|
|||
|
|||
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 |
#16
|
|||
|
|||
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 -- |
#17
|
|||
|
|||
.. 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 -- |
#18
|
|||
|
|||
INT with the "+1" .. that is <g
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#19
|
|||
|
|||
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 |
#20
|
|||
|
|||
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 |
#21
|
|||
|
|||
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 |
#22
|
|||
|
|||
"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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing a formula for a colored value | New Users to Excel | |||
Simplify formula | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel |