Formula needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want create a formula that if the Min column (C3, F3, I3, and so on...) is
greater than 0, then A1 will combine the XX1, XX2, XX3 and so on... can
anyone help????

For clarification... then A1 will combine to show "XX1, XX2, XX3" and so
on... can
anyone help????
Click to expand...

FAC, MIN and MAX are their own column

Columns: A B C D E
F G
Row 1 XX1 XX2
Row 2 FAC MIN MAX FAC MIN MAX
Row 3 1 1 1 1 1
1
 
not positive what you want, but maybe
=if(c3>0,c$1,"")&if(e$3>0,", "&e1,"")&...

or if you mean
=if(min(C3:C100)>0,C$1,"")&if(Min(E3:E100)>0,", "&E$1,"")&...
 
Would a custom Finction do?

If so then try:

Function Cat(rRow As Long) As String
Application.Volatile
Dim R As Long
Dim EndData As Integer
Dim C As String

C = ""
R = rRow
EndData = Cells(R, 255).End(xlToLeft).Column

For x = 1 To EndData
If UCase(Cells(R, x).Value) = "MIN" Then
If Cells(R + 1, x).Value > 0 Then
C = C & Cells(R - 1, x).Value & ", "
End If
End If
Next x
C = Left(C, Len(C) - 2)
Cat = C

End Function

I used rRow in case the real worksheet row is not the same as your example.
If the "MIN" labels really are in Row 2 then enter the function as:

=Cat(2)

If the labels are in a different row that use that row number.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Back
Top