I can't figure out an IF statement.
I want one cell to show more than one value result based on other cells.
So if all three cells are greater than 0, it will show all three GS numbers (would need a comma between them all).IF H1>0, Then GS0740101 AND IF I1>0 Then GS0741102 AND IF J1>0 then GS0741203
Is this possible?
For all three values, try
Do you need a result for 1 and 2 cells as well?=IF(AND(H1>0,I1>0,J1>0),H1 & ", " & I1 & ", " & J1)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
Originally Posted by spaz9876
=SUBSTITUTE(IF(H1>0,"GS0740101",",")&","&IF(I1>0,"GS0741102",",")&","&IF(J1>0,"GS0741023",","),",,","")
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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!
Can you post a sample of your layout?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Here it is. Thanks!
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Ok that works - only thing is it puts commas in funny. Looks like this:
GS-07F-0101M, , , , GS-07F-8789D, , , , , GS-07F-7820C, , ,
I'm not getting that problem.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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?
Just help another when you can!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
