Using Ampersand "Showing a Zero"

[klutz]

Hi,

A bit of help pleezz.

In a column with data from row 15 to 50. Each row/column cell has a formula that is fed from another sheet. Sometimes these rows have data sometimes they don't.

What I am doing is using the ampersand (example,

A15&", "&A16&", "&A17&", "&A18&", "&A19&", "&A20&", "&A21&", "&A22&", "&A23&", "&A24&", "&A25.............................&A49&", "&A50

in cell A23 thru A50 there is no data and in the cell where I have the ampersand formula it shows as a ZERO.

I must have them all in there becuz there could be data to be filled in later on.

Any ideas?..

Da K---

[lucas]

I'm afraid I don't understand the question. Do you not want to see the zero?

[Bob Phillips]

Try

=IF(A15="","",A15&", ")&IF(A16="","",A16&", ")&....

[klutz]

Try

=IF(A15="","",A15&", ")&IF(A16="","",A16&", ")&....

That will work for less than 7 Nested If's...., I thought about that but figured there some other easier way...

I'm gonna have to nest the IF's using the & operator.......

Is there a way for VBA code to do this...

[Bob Phillips]

No it works for far more than that because they are not nested.

[klutz]

No it works for far more than that becauyse they are not nested.

Well, I tried it but it still shows the Zero Value.

 
=IF(A15="","",A15&", ")&IF(A16="","",A16&", ")&IF(A17="","",A17&", ")&IF(A18="","",A18&", ")&IF
 
(A19="","",A19&", ")&IF(A20="","",A20&", ")&IF(A21="","",A21&", ")&IF(A22="","",A22&", ")&IF
 
(A23="","",A23&", ")&IF(A24="","",A24&", ")&IF(A25="","",A25&", ")

COLOMBIA, Argentina, Chile, Ecuador, El Salvador, Guatemala, Mexico, Panama, Puerto Rico, 0, 0,



Any other thoughts?

[mdmackillop]

How about a UDF. Enter =ListItems(A23:A50)

Function ListItems(data As Range)
Dim Cel As Range, txt As String
For Each Cel In data
   If Cel <> "" Then txt = txt & Cel & ", "
Next
ListItems = Left(txt, (Len(txt) - 2))
End Function

[klutz]

How about a UDF. Enter =ListItems(A23:A50)

Function ListItems(data As Range)
Dim Cel As Range, txt As String
For Each Cel In data
If Cel <> "" Then txt = txt & Cel & ", "
Next
ListItems = Left(txt, (Len(txt) - 2))
End Function

Really Nice but it shows the Zero value as well.

Any other thought....

Thanks...

[Bob Phillips]

It can't do, there must be something else going on that you aren't telling us about.

[klutz]

It can't do, there must be something else going on that you aren't telling us about.

On Sheet named "Program" Column A15 thru A50 cells each has a formula like this:

A15 [ ='SchedulesFees'!A15]
A16 [ ='SchedulesFees'!A16]
....................
A50 [ ='SchedulesFees'!A50]

that comes from sheet SchedulesFees.

Not all of the cells in sheet SchedulesFees will have data all the time.

In this case that we were trying to solve only 10 cells contained data and the rest did not. Those that did not are the ones that are shoing up as a Zero Value when using the apmersand and or UDF code that was provided.

So, to get around this I did the following:

On the sheet "Program" A15 I entered the following and copied on to the rest of the rows:

=IF('SchedulesFees'!A24=0,"",'SchedulesFees'!A24)

This allowed me to use both XLD ampersand format and mdmackillop VBA Code as well.

It works... Now the zeros do not show...

Thanx to all of for your help...