Thanks for the feedback. I am new to macro's and need to do some homework.
Really appreciate the quick turn around and idea.

"Dave Peterson" wrote:

I'd use a macro:

Option Explicit
Function myConCat(TopRow As Range, ThisRow As Range)

Dim myStr As String
Dim iCtr As Long
Dim mySep As String

mySep = ", "

If TopRow.Columns.Count < ThisRow.Columns.Count _
Or TopRow.Areas.Count < 1 _
Or TopRow.Rows.Count < 1 _
Or ThisRow.Areas.Count < 1 _
Or ThisRow.Rows.Count < 1 Then
myConCat = CVErr(xlErrRef)
End If

myStr = ""
For iCtr = 1 To ThisRow.Cells.Count
If LCase(ThisRow.Cells(1, iCtr).Value) = LCase("yes") Then
myStr = myStr & mySep & TopRow.Cells(1, iCtr).Value
End If
Next iCtr
If myStr < "" Then
myStr = Mid(myStr, Len(mySep) + 1)
End If

myConCat = myStr

End Function


If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=myconcat($A$1:$D$1,A2:D2)

ECDNewEngland wrote:

Here's an example of one of my template field formula's:

=CONCATENATE((IF(In!$B3="Yes",In!$B$2,"")),(IF(In! $C3="Yes",",
"&In!$C$2,"")),(IF(In!$D3="Yes", ", "&In!$D$2,"")),(IF(In!$E3="Yes",",
"&In!$E$2,"")),(IF(In!$F3="Yes",", "&In!$F$2,"")),(IF(In!$G3="Yes",",
"&In!$G$2,"")),(IF(In!$N3="Yes",", "&In!$N$2,"")),(IF(In!$O3="Yes",",
"&In!$O$2,"")),(IF(In!$P3="Yes",", "&In!$P$2,"")),(IF(In!$Q3="Yes",",
"&In!$Q$2,"")),(IF(In!$R3="Yes",", "&In!$R$2,"")),(IF(In!$AB3="Yes",",
"&In!$AB$2,"")),(IF(In!$AC3="Yes",", "&In!$AC$2,"")),(IF(In!$AD3="Yes",",
"&In!$AD$2,"")),(IF(In!$AI3="Yes",", "&In!$AI$2,"")),(IF(In!$AJ3="Yes",",
"&In!$AJ$2,"")),(IF(In!$AK3="Yes",", "&In!$AK$2,"")),(IF(In!$AL3="Yes",",
"&In!$AL$2,"")),(IF(In!$AM3="Yes",", "&In!$AM$2,"")))

Really appreciate the assistance.

"ECDNewEngland" wrote:

I'm trying to autofill a template worksheet based on selecting a unique name
from a cell with a validation list in the template.

I have created a second worksheet with the list of 25 unique names (the
validation list) in Column A, starting in A2. Cells A1 thru CQ1 contain
unique headers beginning with "Name", "header 2", "header 3", etc out to CQ1.

At each intersecting cell of Name / header I have used validation to select
"Yes" or "No" from a list.

I'm trying to populate seven "fields" in my template worksheet with the
column headers using CONCATENATE when "Yes" exists in various columns for
each "Name".

Due to having 95 columns I have used a rediculously long and inefficient
CONCATENATE formula with IF's in my seven template worksheet fields to check
whether "Yes" is true and if so, concatenate the header.

Suggestions please.



--

Dave Peterson