Need formula help

[Paul]

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

 

[Brad]

="("&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??

 

[Paul]

thanks...works for me....

="("&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??

 

[Brad]

You're welcome - glad to help - thanks for the feedback....

 

[Don Guillett]

Right click sheet tab>view code>insert this>change 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]

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