I have a spreadsheet that is date by colume and customers by rows and I need
to concatenate the date and comment for each customer into one long string
for all dates and all comments per customer, an example is "09/01/08 review
10/01/08 left message 11/01/08 no answer". What I need to know is I would
like to copy this formula down which I can do but say the second customer
does not have a comment with 10/01/08 I would like their string to read
"09/01/08 left message 11/01/08 spoke to customer". Basically copying the
formula down but having it dynamically not concatenate if the comment field
is blank? Am I asking the impossible? Here is an example of my concantenate
statement.
=O$3&" "&O4&" "&P$3&" "&P4&" "&Q$3&" "&Q4

--
"If you can''''t have the best of everything make the best of everything you
have"...BIAKathy

"BIAKathy" wrote in message
...
I have a spreadsheet that is date by colume and customers by rows and I
need
to concatenate the date and comment for each customer into one long string
for all dates and all comments per customer, an example is "09/01/08
review
10/01/08 left message 11/01/08 no answer". What I need to know is I would
like to copy this formula down which I can do but say the second customer
does not have a comment with 10/01/08 I would like their string to read
"09/01/08 left message 11/01/08 spoke to customer". Basically copying the
formula down but having it dynamically not concatenate if the comment
field
is blank? Am I asking the impossible? Here is an example of my
concantenate
statement.
=O$3&" "&O4&" "&P$3&" "&P4&" "&Q$3&" "&Q4

All you need to do is use the IF function to test if the comment column has
an empty cell. So, if comments are in col P, your concatenate statement
needs to be replaced by =IF(ISBLANK(P3),concatenate expression with no
comment,O$3&" "&O4&" "&P$3&" "&P4&" "&Q$3&" "&Q4). (Assuming I have
correctly interpreted your problem).

V

thank you that was exactly what I was looking for. I ended up with
=IF(ISBLANK(K4),,K$3&" "&K4)&" "&IF(ISBLANK(L4),,L$3&" "&L4)&"
"&IF(ISBLANK(M4),,M$3&" "&M4)&" "&IF(ISBLANK(N4),,N$3&" "&N4)&"
"&IF(ISBLANK(O4),,O$3&" "&O4)&" "&IF(ISBLANK(P4),,P$3&" "&P4)&"
"&IF(ISBLANK(Q4),,Q$3&" "&Q4)&" "&IF(ISBLANK(R4),,R$3&" "&R4)&"
"&IF(ISBLANK(S4),,S$3&" "&S4)&" "&IF(ISBLANK(T4),,T$3&" "&T4) since I was
combining a date field with a possible comment or blank.

--
"If you can''''t have the best of everything make the best of everything you
have"...BIAKathy


"Victor Delta" wrote:

"BIAKathy" wrote in message
...
I have a spreadsheet that is date by colume and customers by rows and I
need
to concatenate the date and comment for each customer into one long string
for all dates and all comments per customer, an example is "09/01/08
review
10/01/08 left message 11/01/08 no answer". What I need to know is I would
like to copy this formula down which I can do but say the second customer
does not have a comment with 10/01/08 I would like their string to read
"09/01/08 left message 11/01/08 spoke to customer". Basically copying the
formula down but having it dynamically not concatenate if the comment
field
is blank? Am I asking the impossible? Here is an example of my
concantenate
statement.
=O$3&" "&O4&" "&P$3&" "&P4&" "&Q$3&" "&Q4

All you need to do is use the IF function to test if the comment column has
an empty cell. So, if comments are in col P, your concatenate statement
needs to be replaced by =IF(ISBLANK(P3),concatenate expression with no
comment,O$3&" "&O4&" "&P$3&" "&P4&" "&Q$3&" "&Q4). (Assuming I have
correctly interpreted your problem).

V

"BIAKathy" wrote in message
...
thank you that was exactly what I was looking for. I ended up with
=IF(ISBLANK(K4),,K$3&" "&K4)&" "&IF(ISBLANK(L4),,L$3&" "&L4)&"
"&IF(ISBLANK(M4),,M$3&" "&M4)&" "&IF(ISBLANK(N4),,N$3&" "&N4)&"
"&IF(ISBLANK(O4),,O$3&" "&O4)&" "&IF(ISBLANK(P4),,P$3&" "&P4)&"
"&IF(ISBLANK(Q4),,Q$3&" "&Q4)&" "&IF(ISBLANK(R4),,R$3&" "&R4)&"
"&IF(ISBLANK(S4),,S$3&" "&S4)&" "&IF(ISBLANK(T4),,T$3&" "&T4) since I was
combining a date field with a possible comment or blank.

Phew! But glad it worked!

V