Need formula help
 

I have 5 cells each can have a value or not, but what i want is the 6th cell
to tell me what cells have values. For example if a1, b1, and d1 have values
but c1 and e1 are blank, can I get cell f1 to say (1,2,4) with out a million
if statements. I need this to show a number for any combination of the 5
cells. Any help?

Paul

Need formula help
 

="("&IF(ISNUMBER(J5),"1, ","")&IF(ISNUMBER(K5),"2, ","")&IF(ISNUMBER(L5),"3,
","")&IF(ISNUMBER(M5)," 4, ","")&IF(ISNUMBER(N5),"5","")&")"

Worked for me - the number were in column j-m, the only item is that you
might have an additional "," that you might need.

Did it work for you??
--
Wag more, bark less


"Paul" wrote:

I have 5 cells each can have a value or not, but what i want is the 6th cell
to tell me what cells have values. For example if a1, b1, and d1 have values
but c1 and e1 are blank, can I get cell f1 to say (1,2,4) with out a million
if statements. I need this to show a number for any combination of the 5
cells. Any help?

Paul

Need formula help
 

thanks...works for me....

"Brad" wrote:

="("&IF(ISNUMBER(J5),"1, ","")&IF(ISNUMBER(K5),"2, ","")&IF(ISNUMBER(L5),"3,
","")&IF(ISNUMBER(M5)," 4, ","")&IF(ISNUMBER(N5),"5","")&")"

Worked for me - the number were in column j-m, the only item is that you
might have an additional "," that you might need.

Did it work for you??
--
Wag more, bark less


"Paul" wrote:

I have 5 cells each can have a value or not, but what i want is the 6th cell
to tell me what cells have values. For example if a1, b1, and d1 have values
but c1 and e1 are blank, can I get cell f1 to say (1,2,4) with out a million
if statements. I need this to show a number for any combination of the 5
cells. Any help?

Paul

Need formula help
 

You're welcome - glad to help - thanks for the feedback....
--
Wag more, bark less


"Paul" wrote:

thanks...works for me....

"Brad" wrote:

="("&IF(ISNUMBER(J5),"1, ","")&IF(ISNUMBER(K5),"2, ","")&IF(ISNUMBER(L5),"3,
","")&IF(ISNUMBER(M5)," 4, ","")&IF(ISNUMBER(N5),"5","")&")"

Worked for me - the number were in column j-m, the only item is that you
might have an additional "," that you might need.

Did it work for you??
--
Wag more, bark less


"Paul" wrote:

I have 5 cells each can have a value or not, but what i want is the 6th cell
to tell me what cells have values. For example if a1, b1, and d1 have values
but c1 and e1 are blank, can I get cell f1 to say (1,2,4) with out a million
if statements. I need this to show a number for any combination of the 5
cells. Any help?

Paul

Need formula help
 

Right click sheet tabview codeinsert thischange 21 to suit. Use fixit if
you get a freezeup.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("a1:e21")) Is Nothing Then Exit Sub
Cells(Target.Row, "F") = ""
For i = 1 To 5
If Len(Trim(Cells(Target.Row, i))) 0 Then mystr = mystr & "," &
Cells(Target.Row, i)
Next i
Cells(Target.Row, "F") = Right(mystr, Len(mystr) - 1)
End Sub

Sub fixit()
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul" wrote in message
...
I have 5 cells each can have a value or not, but what i want is the 6th
cell
to tell me what cells have values. For example if a1, b1, and d1 have
values
but c1 and e1 are blank, can I get cell f1 to say (1,2,4) with out a
million
if statements. I need this to show a number for any combination of the 5
cells. Any help?

Paul

Need formula help
 

I mis-read and gave you the data in the cells. if you want to know which
column then just add
..column

If Len(Trim(Cells(Target.Row, i))) 0 Then _
mystr = mystr & "," & Cells(Target.Row, i).COLUMN


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Right click sheet tabview codeinsert thischange 21 to suit. Use fixit
if you get a freezeup.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("a1:e21")) Is Nothing Then Exit Sub
Cells(Target.Row, "F") = ""
For i = 1 To 5
If Len(Trim(Cells(Target.Row, i))) 0 Then mystr = mystr & "," &
Cells(Target.Row, i)
Next i
Cells(Target.Row, "F") = Right(mystr, Len(mystr) - 1)
End Sub

Sub fixit()
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul" wrote in message
...
I have 5 cells each can have a value or not, but what i want is the 6th
cell
to tell me what cells have values. For example if a1, b1, and d1 have
values
but c1 and e1 are blank, can I get cell f1 to say (1,2,4) with out a
million
if statements. I need this to show a number for any combination of the 5
cells. Any help?

Paul