PDA

View Full Version : [SOLVED] If Statement



spaz9876
08-11-2005, 08:57 AM
I can't figure out an IF statement.
I want one cell to show more than one value result based on other cells.


IF H1>0, Then GS0740101 AND IF I1>0 Then GS0741102 AND IF J1>0 then GS0741203
So if all three cells are greater than 0, it will show all three GS numbers (would need a comma between them all).

Is this possible?

mdmackillop
08-11-2005, 09:34 AM
For all three values, try

=IF(AND(H1>0,I1>0,J1>0),H1 & ", " & I1 & ", " & J1)
Do you need a result for 1 and 2 cells as well?

spaz9876
08-11-2005, 09:57 AM
This is giving a FALSE result - is the "AND" making it look to see if ALL have to be greater than 0 in order to be TRUE? I want it if even only 1 is greater than 0, then it will show the value. If H1, H2 and H4 are greater than 0 but H3 is not, it will show H1, H2, H4.

Bob Phillips
08-11-2005, 10:05 AM
I can't figure out an IF statement.
I want one cell to show more than one value result based on other cells.

IF H1>0, Then GS0740101 AND IF I1>0 Then GS0741102 AND IF J1>0 then GS0741203
So if all three cells are greater than 0, it will show all three GS numbers (would need a comma between them all).

Is this possible?


=SUBSTITUTE(IF(H1>0,"GS0740101",",")&","&IF(I1>0,"GS0741102",",")&","&IF(J1>0,"GS0741023",","),",,","")

mdmackillop
08-11-2005, 10:15 AM
This can also be done with a User Defined Function (UDF), which can be more flexible as well.

Option Explicit
Function DoList(MyCells As Range)
Dim Cel
Dim Tmp As String
'Check each cell in selection
For Each Cel In MyCells
'Set the comparison
If Cel >= 1 Then
'Create string
Tmp = Tmp & Cel & ", "
End If
Next
'Remove end comma and space
DoList = Left(Tmp, Len(Tmp) - 2)
End Function

spaz9876
08-11-2005, 10:31 AM
The Substitute one isnt working.
The VBA Code works however I want if H1 is greater than zero, value is I1 instead of it taking the value of H1.

And thank you for helping me!

mdmackillop
08-11-2005, 10:49 AM
Can you post a sample of your layout?

spaz9876
08-11-2005, 11:50 AM
Here it is. Thanks!

mdmackillop
08-11-2005, 11:57 AM
Revised to return offset value


Option Explicit

Function DoList(MyCells As Range)
Dim Cel
Dim Tmp As String
'Check each cell in selection
For Each Cel In MyCells
'Set the comparison
If Cel >= 1 Then
'Create string
Tmp = Tmp & Cel.Offset(0, 1) & ", "
End If
Next
'Remove end comma and space
DoList = Left(Tmp, Len(Tmp) - 2)
End Function

spaz9876
08-11-2005, 12:10 PM
Ok that works - only thing is it puts commas in funny. Looks like this:
GS-07F-0101M, , , , GS-07F-8789D, , , , , GS-07F-7820C, , ,

mdmackillop
08-11-2005, 12:16 PM
I'm not getting that problem.

spaz9876
08-11-2005, 12:19 PM
Sorry - I had 2 columns selected I changed it to =dolist(H8:H18) instead of (H8:I18) and now it works. THanks! What do I owe you? :thumb

mdmackillop
08-11-2005, 12:25 PM
Just help another when you can!:beerchug: