Remember Me?

#1
November 21st 05, 05:05 PM posted to microsoft.public.excel.worksheet.functions
 LOU Posts: n/a
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

#2
November 21st 05, 05:17 PM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
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

#3
November 21st 05, 05:47 PM posted to microsoft.public.excel.worksheet.functions
 LOU Posts: n/a
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

#4
November 21st 05, 06:04 PM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
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
news
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

#5
November 21st 05, 08:42 PM posted to microsoft.public.excel.worksheet.functions
 LOU Posts: n/a
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
news
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

#6
November 21st 05, 08:43 PM posted to microsoft.public.excel.worksheet.functions
 LOU Posts: n/a
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
news
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

#7
November 21st 05, 09:04 PM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
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
news
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Gouden Willem Charts and Charting in Excel 3 March 7th 06 01:11 PM alexy Excel Worksheet Functions 3 September 15th 05 10:35 AM PeterB Excel Worksheet Functions 0 June 1st 05 12:23 PM Josephine Excel Discussion (Misc queries) 2 March 3rd 05 04:37 PM Mcobra41 Excel Worksheet Functions 3 February 23rd 05 08:22 PM

All times are GMT +1. The time now is 05:01 AM.