Create a 'text' string based on whether values appear in other cel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

Hope someone can help... I have a basic table:

Service Child Group Disc_card1 Disc_card2 SeniorCitizen
1 50% 10% 60% 30%
2 50% 15% 25% 90%

For each 'service' I may offer a discount to children, senior citizens or
those with discount cards.

I then want to create an adjacent cell, which populates a text field "There
is a 50% for children, 10% for Groups, No discounts for Disc_card1"

Is there a formula that will do an 'if' query to see if there is a value in
a cell, if there is turn that into a sentence and put it into a text table (I
guess I will need to use 'CONCATENATE' some where?)

Please help!!

Thanks
 
Ben said:
Service Child Group Disc_card1 Disc_card2 SeniorCitizen
1 50% 10% 60% 30%
2 50% 15% 25% 90%
Click to expand...
....

If this table were in A1:F3,
I then want to create an adjacent cell, which populates a text field
"There is a 50% for children, 10% for Groups, No discounts for
Disc_card1"
Click to expand...
....

then try this formula in cell G2.

="There is "&TEXT(B2,"[=0]\n\o;\a 0%")&" discount for children, "
&TEXT(C2,"[=0]\n\o;\a 0%")&" discount for groups, "
&TEXT(D2,"[=0]\n\o;\a 0%")&" discount for Disc_card1, "
&TEXT(E2,"[=0]\n\o;\a 0%")&" discount for Disc_card2, and "
&TEXT(F2,"[=0]\n\o;\a 0%")&" discount for senior citizens."

Fill G2 down into G3.
 
=IF(A2<>"",TEXT(A2,"0%"),"No")&" discount for
children,"&IF(B2<>"",TEXT(B2,"0%"),"No")&" discount for
groups,"&IF(C2<>"",TEXT(C2,"0%"),"No")&" discount for Disc card
1,"&IF(D2<>"",TEXT(D2,"0%"),"No")&" discount for Disc card 2"

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Harlan

Thank you very much for this!!!

May I quickly ask, how can I insert a 'carriage return' to make the next
discount go on the next line??

Thanks

Ben




Harlan Grove said:
Ben said:
Service Child Group Disc_card1 Disc_card2 SeniorCitizen
1 50% 10% 60% 30%
2 50% 15% 25% 90%
Click to expand...
....

If this table were in A1:F3,
I then want to create an adjacent cell, which populates a text field
"There is a 50% for children, 10% for Groups, No discounts for
Disc_card1"
Click to expand...
....

then try this formula in cell G2.

="There is "&TEXT(B2,"[=0]\n\o;\a 0%")&" discount for children, "
&TEXT(C2,"[=0]\n\o;\a 0%")&" discount for groups, "
&TEXT(D2,"[=0]\n\o;\a 0%")&" discount for Disc_card1, "
&TEXT(E2,"[=0]\n\o;\a 0%")&" discount for Disc_card2, and "
&TEXT(F2,"[=0]\n\o;\a 0%")&" discount for senior citizens."

Fill G2 down into G3.
Click to expand...
 
Ben said:
May I quickly ask, how can I insert a 'carriage return' to make the
next discount go on the next line??
Click to expand...
....

="... one discount"&CHAR(10)&"another discount ..."

but you need to set the cell to wrap, and you may have to increase
column widths.
 
Back
Top