Slight problem with stuff not disappearing when it should...

[Drayde]

If I have to post examples, I will, but I'm going to try and keep this minimal :

=E3&IF(I3=0,"",", "&I3&"% of cross sectional area")&IF(AND(G3>0,H3=0),", at "&G3&" o'clock","")&IF(AND(G3>0,H3>0),", from "&G3&" to "&H3&" o'clock","")&IF(J3>0,", "&J3&"""","")

Is returning, as an example,

Water Level, 30% of cross sectional area, from to o'clock

The 'from to o'clock' is from the third &IF, G3>0,H3>0. The problem is, both those cells are empty, and I want it to just return nothing. It should just be

Water Level, 30% of cross sectional area

I can't seem to get the from to o'clock to go away.

[Drayde]

I see why, but it leaves me stuck. The reason is because I need it to return the values from those columns, and it's reading the formulas. How do I prevent it from reading the formulas, and just the values?

[Norie]

Are there formulas in G3 and H3?

[Drayde]

Yep. As I said, I realize that's why I'm getting a problem... I'm just not sure what to do about it. I need those formulas there, but I need the value to be ignored if the cell is empty, and be treated as nothing so that I don't get text that shouldn't be there.

[Norie]

Instead of testing for >0 try testing for <>"".

[Drayde]

That didn't seem to fix it.... and now that I realized that's my problem, it's made the initial problem much worse. Now the same line looks like:

Water Level, 30% of cross sectional area, at o'clock, from to o'clock,"

A completely empty, where G, H, I, and J are ALL empty now looks like this:

Manhole, % of cross sectional area, at o'clock, from to o'clock, "

Hrm. This is dumb. You'd think there'd be a simple thing to tell Excel to just look at the value of the cell and ignore formulas.

[Norie]

Excel doesn't look at the formulas, it only looks at the value in the cell whether it's the result of a formula or manually entered.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[Drayde]

The Detail tab is the one with familiar information. The C column is what I'm trying to fix.

Wincan 7 Detail Help.xlsx

[Norie]

Does this do what you want?

=E2&IF(I2=0,"",", "&I2&"% of cross sectional area")&IF(AND(G2<>0,H2=""),", at "&G2&" o'clock","")&IF(AND(G2<>"",H2<>""),", from "&G2&" to "&H2&" o'clock","")&IF(J2<>"",", "&J2&"""","")

[Drayde]

Getting closer, I think... I'm still getting 'at O'clock' on the Manholes, when there shouldn't be anything.

And still getting Cross-Sectional area, as well. I column needs its formula dragged down.

[Norie]

Perhaps.

=E2&IF(I2="","",", "&I2&"% of cross sectional area")&IF(AND(G2<>"",H2=""),", at "&G2&" o'clock","")&IF(AND(G2<>"",H2<>""),", from "&G2&" to "&H2&" o'clock","")&IF(J2<>"",", "&J2&"""","")

[Drayde]

That did it! I'll have to look at what you did sometime - I'm still trying to learn. But that fixed it. Thank you very much, Norie!

[Drayde]

I swear, one of the most difficult things about building this converter is getting stuff NOT to appear when it shouldn't.

[Norie]

Basically I replaced all the 0s with "" as that was what the formulas in the columns E, G, H etc. were returning.