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