If any of these cells have text in them put a comma, if there is no text then a comma

[sharpmel]

I have an extremely long formula and I need to list the family members like so.

Person lives with PARENT 1 CELL (PARENT1 NAME CELL, PARENT1 AGE CELL)

Then it gets trickier because there are 4 more sets (relation, name, age) to account for other family members. If ANY of these cells have information in them there should be a comma afterwards [i.e., (PARENT1 NAME CELL, PARENT1 AGE CELL), ]. If NONE of these cells have any information in them there should be a period.

&IF('Qualtrics Output'!BD3, 'Qualtrics Output'!BG3, 'Qualtrics Output'!BJ3, 'Qualtrics Output'!BM3="",”.”,”,”) is not how to do it...
All the referenced cells are names btw.

[martindwilson]

post a sample workbook showing various results you expect

[sharpmel]

PAT Automated Scoring and Report_Draft 11 29 12.xlsx
See worksheet 3 titled Report Draft. Highlighted Row 20.

I need to have the comma/period and age conditional basically.

[martindwilson]

not sure where you want periods,the formula looks to be working as it is

[sharpmel]

The commas and the age in between the family members listed. If there are 4 family members it will show up Person (name, age), Person2 (name, age), Person3 (name, age), Person4 (name, age), (age ) , (age ).

I need the static text to be conditional like an IF statement but I don't know how to set it up.

[sharpmel]

So something based on an IF OR statement. If any of the cells having =IF(OR(B1>100,B2>100,B3>100),”Over Budget”,”Acceptable”)

How do you say if a cell has ANY text in it? IsText?

[martindwilson]

2 ways
if(a1<>"",
or if(istext(a1),

[sharpmel]

Thanks

IF(OR(ISTEXT('Qualtrics Output'!BA3),ISTEXT('Qualtrics Output'!BD3), ISTEXT('Qualtrics Output'!BG3), ISTEXT('Qualtrics Output'!BJ3), ISTEXT('Qualtrics Output'!BM3))," , ", ".")

solved