Thanks for the feedback. Really appreciate the quick turn around and idea.

"ShaneDevenshire" wrote:

Hi,

Probably the best way to handle this would be with a custom VBA functions,
if not you could simply your formula with a lot of range names.
In the following example, I have remove some unnecessary parens "()" and
added range names for all you absolute references. In addition I have
replace the "yes" with a defined name of y, and ", " with a defined name of
K. As you can see this is some improvement, but not much.

=CONCATENATE(IF(In!$B3=y,B,K),IF(In!$C3=y,J&ICC,K) ,IF(In!$D3=y,
J&D,K),IF(In!$E3=y,J&E,K),IF(In!$F3=y,J&F,K),IF(In !$G3=y,J&G,K),IF(In!$N3=y,J&N,K),IF(In!$O3=y,J&0,K ),IF(In!$P3=y,J&P,K),IF(In!$Q3=y,J&Q,K),IF(In!$R3= y,J&RR,K),IF(In!$AB3=y,J&AB,K),IF(In!$AC3=y,J&AC,K ),IF(In!$AD3=y,J&AD,K),IF(In!$AI3=y,J&AI,K),IF(In! $AJ3=y,J&AJ,K),IF(In!$AK3=y,J&AK,K),IF(In!$AL3=y,J &AL,K),IF(In!$AM3=y,J&AM,K))

--
Thanks,
Shane Devenshire


"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.