Combining Text on statement
Hello,
I have a spreadsheet set as such, Columns A = Date B = Employee (B2 THRU B116 , Column to house formula) Columns C1 thru z1 have employee names in C1 thru Z1 I have a 1 indicatiing they are assigned this day. I would like the formula to review C1:Z1 and if there is a 1 in a column bring back the employees name to that row in column B and combine them together. I hope this is clear, I need to stay away from a pivot table though, any help would be great. Thank you, Lou |
Combining Text on statement
Your example is a bit confusing.
Is this what you want =COUNTIF(C2:Z2,1) for the employee in B2 -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Hello, I have a spreadsheet set as such, Columns A = Date B = Employee (B2 THRU B116 , Column to house formula) Columns C1 thru z1 have employee names in C1 thru Z1 I have a 1 indicatiing they are assigned this day. I would like the formula to review C1:Z1 and if there is a 1 in a column bring back the employees name to that row in column B and combine them together. I hope this is clear, I need to stay away from a pivot table though, any help would be great. Thank you, Lou |
Combining Text on statement
Bob,
Thank you, sorry for the confusion. I want it to bring back the persons name from row 1, Example.. A= Date B1= Employees C1= Bill D1=Jen E1=Lou 1/1/06 Bill, Jen 1 1 2/1/06 Bill, Lou 1 1 3/1/0 Jen, Lou 1 1 Thank you. Lou "Bob Phillips" wrote: Your example is a bit confusing. Is this what you want =COUNTIF(C2:Z2,1) for the employee in B2 -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Hello, I have a spreadsheet set as such, Columns A = Date B = Employee (B2 THRU B116 , Column to house formula) Columns C1 thru z1 have employee names in C1 thru Z1 I have a 1 indicatiing they are assigned this day. I would like the formula to review C1:Z1 and if there is a 1 in a column bring back the employees name to that row in column B and combine them together. I hope this is clear, I need to stay away from a pivot table though, any help would be great. Thank you, Lou |
Combining Text on statement
I still don't think I get it.
What part of that are you trying to calculate, and what is being input? Another guess. C2: =IF(ISNUMBER(FIND(B$1,$B2)),1,"") and copy down and acrooss -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Bob, Thank you, sorry for the confusion. I want it to bring back the persons name from row 1, Example.. A= Date B1= Employees C1= Bill D1=Jen E1=Lou 1/1/06 Bill, Jen 1 1 2/1/06 Bill, Lou 1 1 3/1/0 Jen, Lou 1 1 Thank you. Lou "Bob Phillips" wrote: Your example is a bit confusing. Is this what you want =COUNTIF(C2:Z2,1) for the employee in B2 -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Hello, I have a spreadsheet set as such, Columns A = Date B = Employee (B2 THRU B116 , Column to house formula) Columns C1 thru z1 have employee names in C1 thru Z1 I have a 1 indicatiing they are assigned this day. I would like the formula to review C1:Z1 and if there is a 1 in a column bring back the employees name to that row in column B and combine them together. I hope this is clear, I need to stay away from a pivot table though, any help would be great. Thank you, Lou |
Combining Text on statement
Heya Bill thank you for your time and patience.
I want to bring the person Name from the HEADER row in the column which has the employees. Column A Column B Column C Column D Column E ROW 1 - HEADER ROW Date Employees Bill Jen Lou ROW 2 1/1/06 Bill, Jen 1 1 ROW 3 2/1/06 Bill, Lou 1 1 ROW 4 3/1/06 Jen, Lou 1 1 Column B will hold the formula which will bring back the results. If there is a 1 in any cell from C2:E2 it will bring the names back from the header row. Example above would be something like this in B2 If(c2=1,$c$1,if(d2=1,$d$1)) I hope this makes sense. Thank you, Lou "Bob Phillips" wrote: I still don't think I get it. What part of that are you trying to calculate, and what is being input? Another guess. C2: =IF(ISNUMBER(FIND(B$1,$B2)),1,"") and copy down and acrooss -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Bob, Thank you, sorry for the confusion. I want it to bring back the persons name from row 1, Example.. A= Date B1= Employees C1= Bill D1=Jen E1=Lou 1/1/06 Bill, Jen 1 1 2/1/06 Bill, Lou 1 1 3/1/0 Jen, Lou 1 1 Thank you. Lou "Bob Phillips" wrote: Your example is a bit confusing. Is this what you want =COUNTIF(C2:Z2,1) for the employee in B2 -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Hello, I have a spreadsheet set as such, Columns A = Date B = Employee (B2 THRU B116 , Column to house formula) Columns C1 thru z1 have employee names in C1 thru Z1 I have a 1 indicatiing they are assigned this day. I would like the formula to review C1:Z1 and if there is a 1 in a column bring back the employees name to that row in column B and combine them together. I hope this is clear, I need to stay away from a pivot table though, any help would be great. Thank you, Lou |
Combining Text on statement
sorry, I am just inputting the "1"s under the names down there corresponding
column....... Lou "Bob Phillips" wrote: I still don't think I get it. What part of that are you trying to calculate, and what is being input? Another guess. C2: =IF(ISNUMBER(FIND(B$1,$B2)),1,"") and copy down and acrooss -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Bob, Thank you, sorry for the confusion. I want it to bring back the persons name from row 1, Example.. A= Date B1= Employees C1= Bill D1=Jen E1=Lou 1/1/06 Bill, Jen 1 1 2/1/06 Bill, Lou 1 1 3/1/0 Jen, Lou 1 1 Thank you. Lou "Bob Phillips" wrote: Your example is a bit confusing. Is this what you want =COUNTIF(C2:Z2,1) for the employee in B2 -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Hello, I have a spreadsheet set as such, Columns A = Date B = Employee (B2 THRU B116 , Column to house formula) Columns C1 thru z1 have employee names in C1 thru Z1 I have a 1 indicatiing they are assigned this day. I would like the formula to review C1:Z1 and if there is a 1 in a column bring back the employees name to that row in column B and combine them together. I hope this is clear, I need to stay away from a pivot table though, any help would be great. Thank you, Lou |
Combining Text on statement
I would use a UDF
Function Get(rng As Range) Dim cell As Range For Each cell In rng If cell.Value = 1 Then GetNames = GetNames & Cells(1, cell.Column) & "," End If Next cell If GetNames < "" Then GetNames = Left(GetNames, Len(GetNames) - 1) End If End Function And use in B2 like this =GetNames(C2:E2) -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Heya Bill thank you for your time and patience. I want to bring the person Name from the HEADER row in the column which has the employees. Column A Column B Column C Column D Column E ROW 1 - HEADER ROW Date Employees Bill Jen Lou ROW 2 1/1/06 Bill, Jen 1 1 ROW 3 2/1/06 Bill, Lou 1 1 ROW 4 3/1/06 Jen, Lou 1 1 Column B will hold the formula which will bring back the results. If there is a 1 in any cell from C2:E2 it will bring the names back from the header row. Example above would be something like this in B2 If(c2=1,$c$1,if(d2=1,$d$1)) I hope this makes sense. Thank you, Lou "Bob Phillips" wrote: I still don't think I get it. What part of that are you trying to calculate, and what is being input? Another guess. C2: =IF(ISNUMBER(FIND(B$1,$B2)),1,"") and copy down and acrooss -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Bob, Thank you, sorry for the confusion. I want it to bring back the persons name from row 1, Example.. A= Date B1= Employees C1= Bill D1=Jen E1=Lou 1/1/06 Bill, Jen 1 1 2/1/06 Bill, Lou 1 1 3/1/0 Jen, Lou 1 1 Thank you. Lou "Bob Phillips" wrote: Your example is a bit confusing. Is this what you want =COUNTIF(C2:Z2,1) for the employee in B2 -- HTH RP (remove nothere from the email address if mailing direct) "LOU" wrote in message ... Hello, I have a spreadsheet set as such, Columns A = Date B = Employee (B2 THRU B116 , Column to house formula) Columns C1 thru z1 have employee names in C1 thru Z1 I have a 1 indicatiing they are assigned this day. I would like the formula to review C1:Z1 and if there is a 1 in a column bring back the employees name to that row in column B and combine them together. I hope this is clear, I need to stay away from a pivot table though, any help would be great. Thank you, Lou |
All times are GMT +1. The time now is 04:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com