Getting Cell Data Based on Boolean Values

[Vonsteiner]

Ok,

I have a range of 10 cells that will be filled with a true/false value based on a userform. Is there a way I can fill in another cell with the values that are true? In the below examples say Value 1 is in D1 and TRUE is in E1. In cell A1 I would like to show all the cells in D with a true value in E. So, in the top example: "Value 1, Value 4, Value 5, Value 7, Value 8" would be what shows in A1. In the bottom example: "Value 1, Value 2, Value 7, Value 9" would be what shows in A1. I would like to know if there is a formula I can add either in VBA or within a cell.

Value 1	TRUE
Value 2	FALSE
Value 3	FALSE
Value 4	TRUE
Value 5	TRUE
Value 6	FALSE
Value 7	TRUE
Value 8	TRUE
Value 9	FALSE
Value 10	FALSE
Value 1	TRUE
Value 2	TRUE
Value 3	FALSE
Value 4	FALSE
Value 5	FALSE
Value 6	FALSE
Value 7	TRUE
Value 8	FALSE
Value 9	TRUE
Value 10	FALSE

<colgroup><col width="69" span="2" style="width:52pt"> </colgroup><tbody>
</tbody>

Michael

 

[Scott Huish]

Try this UDF:

Function TrueValues(r As Range)
Dim c As Range, s As String
For Each c In r.Offset(, 1).Resize(, 1)
    If c Then s = s & c.Offset(, -1) & ", "
Next
If Len(s) Then s = Left(s, Len(s) - 2)
TrueValues = s
End Function

Then in the cell you want the result:
=TRUEVALUES(D1:E10)

Excel 2010
	A	B	C	D	E
1	Value 1, Value 4, Value 5, Value 7, Value 8			Value 1	TRUE
2				Value 2	FALSE
3				Value 3	FALSE
4				Value 4	TRUE
5				Value 5	TRUE
6				Value 6	FALSE
7				Value 7	TRUE
8				Value 8	TRUE
9				Value 9	FALSE
10				Value 10	FALSE
Sheet1

Cell Formulas
Range	Formula
A1	=truevalues(D1:E10)

 

[Vonsteiner]

Thanks Scott. That worked like a charm.

Michael