Need formula help

P

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
 
B

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??
 
P

Paul

thanks...works for me....

Brad said:
="("&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??
 
D

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top