| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return column header within an array when a value is met
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return column header within an array when a value is met
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return column header within an array when a value is met
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return column header within an array when a value is met
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return column header within an array when a value is met
I am unable to think of a cleaner formula...
Unfortunately concatenate does not accept a list... You can always do this through a macro... '--------------------- Sub joinHeadings() Dim i As Integer Dim oStr As String oStr = "" 'Replace 10 by the no of columns you have For i = 1 To 10 If Cells(2, i).Value = "Yes" Then oStr = oStr & Cells(1, i).Value End If Next i MsgBox oStr End Sub '--------------------- If not then ... Assuming you have your column headers in Sheet1 then in Sheet2 A1 enter =IF(Sheet1!A2="Yes",Sheet1!A1,"") and copy across Then in A2 you can have =(A1&B1&....) and refer to this cell where you have your HUGE formulae... It will not change anything but will be cleaner... -- Always provide your feedback... "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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return column header within an array when a value is met
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return column header within an array when a value is met
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return column header within an array when a value is met
Thanks for the feedback. I'm new to macros and need to do some homework.
Really appreciate the quick turn around and idea. "Sheeloo" wrote: I am unable to think of a cleaner formula... Unfortunately concatenate does not accept a list... You can always do this through a macro... '--------------------- Sub joinHeadings() Dim i As Integer Dim oStr As String oStr = "" 'Replace 10 by the no of columns you have For i = 1 To 10 If Cells(2, i).Value = "Yes" Then oStr = oStr & Cells(1, i).Value End If Next i MsgBox oStr End Sub '--------------------- If not then ... Assuming you have your column headers in Sheet1 then in Sheet2 A1 enter =IF(Sheet1!A2="Yes",Sheet1!A1,"") and copy across Then in A2 you can have =(A1&B1&....) and refer to this cell where you have your HUGE formulae... It will not change anything but will be cleaner... -- Always provide your feedback... "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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return column header within an array when a value is met
Hi,
If this is homework I don't think you should use a macro, the instructor will know immediately that you got help from somewhere else. Sorry I have no other suggestions. If any of these helped please check the Yes button. -- Thanks, Shane Devenshire "ECDNewEngland" wrote: Thanks for the feedback. I'm new to macros and need to do some homework. Really appreciate the quick turn around and idea. "Sheeloo" wrote: I am unable to think of a cleaner formula... Unfortunately concatenate does not accept a list... You can always do this through a macro... '--------------------- Sub joinHeadings() Dim i As Integer Dim oStr As String oStr = "" 'Replace 10 by the no of columns you have For i = 1 To 10 If Cells(2, i).Value = "Yes" Then oStr = oStr & Cells(1, i).Value End If Next i MsgBox oStr End Sub '--------------------- If not then ... Assuming you have your column headers in Sheet1 then in Sheet2 A1 enter =IF(Sheet1!A2="Yes",Sheet1!A1,"") and copy across Then in A2 you can have =(A1&B1&....) and refer to this cell where you have your HUGE formulae... It will not change anything but will be cleaner... -- Always provide your feedback... "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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return column header within an array when a value is met
Thanks for the concern, actually gave me quite the chuckle. I wish I was
back in school!!! Unfortunately decades past that point in the rat race of the corporate world trying to do something more efficiently. Thanks again. "ShaneDevenshire" wrote: Hi, If this is homework I don't think you should use a macro, the instructor will know immediately that you got help from somewhere else. Sorry I have no other suggestions. If any of these helped please check the Yes button. -- Thanks, Shane Devenshire "ECDNewEngland" wrote: Thanks for the feedback. I'm new to macros and need to do some homework. Really appreciate the quick turn around and idea. "Sheeloo" wrote: I am unable to think of a cleaner formula... Unfortunately concatenate does not accept a list... You can always do this through a macro... '--------------------- Sub joinHeadings() Dim i As Integer Dim oStr As String oStr = "" 'Replace 10 by the no of columns you have For i = 1 To 10 If Cells(2, i).Value = "Yes" Then oStr = oStr & Cells(1, i).Value End If Next i MsgBox oStr End Sub '--------------------- If not then ... Assuming you have your column headers in Sheet1 then in Sheet2 A1 enter =IF(Sheet1!A2="Yes",Sheet1!A1,"") and copy across Then in A2 you can have =(A1&B1&....) and refer to this cell where you have your HUGE formulae... It will not change anything but will be cleaner... -- Always provide your feedback... "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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return column number from column header text | Excel Discussion (Misc queries) | |||
| Rank and return column header | Excel Discussion (Misc queries) | |||
| Lookup Value in Range/Array and Return Column Header Value | Excel Discussion (Misc queries) | |||
| Lookup Value in Range/Array and Return Column Header Value | Excel Worksheet Functions | |||
| Return Column header, if row value is > X | Excel Discussion (Misc queries) | |||
Linear Mode
