On Fri, 23 Sep 2005 03:44:09 -0700, "Denzil B" <Denzil
wrote:

Each row from 2 on contains a product item in colum A. The top row, colums C
on, contain product options, 30. Below that, each row has place to select
the product option (y/n). Column B must get all the options selected as a
concatenated text string. concatenate(if(c2="y",c$1,""),",
",if(d2="y",d$1,""),", "...... doesnt work because 30 text entries are
needed, it is clumsy, takes forever to write up and is a mission to
modify/edit. Is there a better way?

Seems like a UDF would be the way to go.

The following is a suggestion.

It's relatively *hard-coded* in that the function takes no arguments, but
rather sets up the ranges within the function itself.

I have assumed that your option names are in row 1; and your product items are
in column A; and that the function will always be in column B.

In addition, I have assumed that the only acceptable entry in the selection
area is a "y".

All of this can be modified.

In any event, to enter the function, <alt<F11 opens the VB Editor. Ensure
your project is highlighted in the project explorer window, then Insert/Module
and paste the code below into the window that opens.

To use the function, enter
=ProdOptions()

into some cell in Column B. It will return the concatenation of the item in
Column A of that row with the strings from row 1 of any cell containing a "y"
in the remainder of the row.

=====================================
Function ProdOptions()
Application.Volatile

Dim FuncAddr As Range
Dim FuncRow As Long
Dim i As Long

Const OptionNamesRow As Long = 1
Const ProdNamesCol As Long = 1

Set FuncAddr = Application.Caller
FuncRow = FuncAddr.Row

If FuncAddr.Column < 2 Then
ProdOptions = CVErr(xlErrRef)
Exit Function
End If

ProdOptions = Cells(FuncRow, 1).Text

For i = 3 To 256
If Cells(FuncRow, i) = "y" Then
ProdOptions = ProdOptions & Cells(OptionNamesRow, i)
End If
Next i

End Function
===========================
--ron