Hi Roger
I may have been unclear with my first post.
What I need to do is if I have 4 columns of data for example (I've actually
got up to 8 columns of choices (colour) 8 of (size) 5 of (material) etc per
line of product which I need to be output eventually to a CSV File as so:
red blue white pink green orange black grey
As Headings
Some products have all, some none, some have 1 - 8
So the output needs to be:
red¦red|black¦black
etc etc.
The data from each cell in the first sheet is repeated twice in the second
sheet with 1broken pipe then 1 pipe separating each entry.
Your previous code worked perfectly but the data is only displayed once as
this:
red¦black|blue¦
The pipes are alternating correctly but the data is only displayed once and
not twice.
Sorry to be a pain, but the web design programme needs the permutations
imported that way.

Thank you for your patience!

"Roger Govier" wrote:

Hi

Try
=concatrange(Sheet9!A1:H1)
where Sheet 9 contains the data.
The formula can be entered on any Sheet.

To get rid of the last pipe, then amend the line
ConCatRange = sbuf)
to
ConCatRange = Left(sbuf, Len(sbuf) - 1)
--

Regards
Roger Govier

"Excel Helps" wrote in message
...
Is there any way to modify the UDF to display this:
data1¦data1|data2¦data2|data3¦data3

So the data is duplicated but with a broken pipe, then a pipe until the
end
of the string which has no pipe

Also I need to reference a separate worksheet for the data and display in
another sheet.

Thanks


"Excel Helps" wrote:

The UDF works fine,but what do I need to use it to pull data from a
different
worksheet. For Example rather than =ConCatRange(A2:E2)
I would like to reference =sheet9!ConCatRange(A2:E2)


"Gord Dibben" wrote:

Thanks Roger.

These old eyes did not pick up the difference in the piping.

Gord

On Sun, 20 Jan 2008 10:38:10 -0000, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:

Hi Gord

I don't think that will work.
the OP is using two different forms of pipe between the text
red¦red|white¦white|blue¦blue|