Hi, Im using the following formula to pull data from several worksheets onto
another worksheet:
=CONCATENATE("Product 1: ",Sheet1!A1,"
","Product 2: ",Sheet2!A1,"
","Product 3: ",Sheet3!A1)

This gives me the following result, assuming that the data in A1 on Sheets
1, 2, and 3 was Apples, Oranges, and Pears, respectively:
Product 1: Apples
Product 2: Oranges
Product 3: Pears

This works fine. The only problem is if there were no data in, say, cell A1
on Sheet3. Then my results would look like this:
Product 1: Apples
Product 2: Oranges
Product 3:

I would like to use a formula or a macro that would allow me to do a sort of
€œconcatenate if€� type of thing, so that if a cell is blank, it would not
concatenate the €œProduct€� part. Im not sure if this can be done with a
regular Excel formula. I thinking it might need a user defined function or a
macro, if its possible at all. Can anyone help? Thanks in advance for any
information.

Hi,

It's a bit messy but here's a formula to do it.

=IF(Sheet1!A1<"","Product 1: "& Sheet1!A1&" ","")&
IF(Sheet2!A1<"","Product 2: "& Sheet2!A1&" ","")&IF(Sheet3!A1<"","Product
3: "& Sheet3!A1,"")

Mike

"FJ" wrote:

Hi, Im using the following formula to pull data from several worksheets onto
another worksheet:
=CONCATENATE("Product 1: ",Sheet1!A1,"
","Product 2: ",Sheet2!A1,"
","Product 3: ",Sheet3!A1)

This gives me the following result, assuming that the data in A1 on Sheets
1, 2, and 3 was Apples, Oranges, and Pears, respectively:
Product 1: Apples
Product 2: Oranges
Product 3: Pears

This works fine. The only problem is if there were no data in, say, cell A1
on Sheet3. Then my results would look like this:
Product 1: Apples
Product 2: Oranges
Product 3:

I would like to use a formula or a macro that would allow me to do a sort of
€œconcatenate if€� type of thing, so that if a cell is blank, it would not
concatenate the €œProduct€� part. Im not sure if this can be done with a
regular Excel formula. I thinking it might need a user defined function or a
macro, if its possible at all. Can anyone help? Thanks in advance for any
information.

Hi, Mike, thank you so much! :) Your formula worked great! :) I didn't
think this could be done with a regular formula. I always learn so much from
these discussion groups. :)

Thanks again! :)




"Mike H" wrote:

Hi,

It's a bit messy but here's a formula to do it.

=IF(Sheet1!A1<"","Product 1: "& Sheet1!A1&" ","")&
IF(Sheet2!A1<"","Product 2: "& Sheet2!A1&" ","")&IF(Sheet3!A1<"","Product
3: "& Sheet3!A1,"")

Mike

"FJ" wrote:

Hi, Im using the following formula to pull data from several worksheets onto
another worksheet:
=CONCATENATE("Product 1: ",Sheet1!A1,"
","Product 2: ",Sheet2!A1,"
","Product 3: ",Sheet3!A1)

This gives me the following result, assuming that the data in A1 on Sheets
1, 2, and 3 was Apples, Oranges, and Pears, respectively:
Product 1: Apples
Product 2: Oranges
Product 3: Pears

This works fine. The only problem is if there were no data in, say, cell A1
on Sheet3. Then my results would look like this:
Product 1: Apples
Product 2: Oranges
Product 3:

I would like to use a formula or a macro that would allow me to do a sort of
€œconcatenate if€� type of thing, so that if a cell is blank, it would not
concatenate the €œProduct€� part. Im not sure if this can be done with a
regular Excel formula. I thinking it might need a user defined function or a
macro, if its possible at all. Can anyone help? Thanks in advance for any
information.

Glad I could help

"FJ" wrote:

Hi, Mike, thank you so much! :) Your formula worked great! :) I didn't
think this could be done with a regular formula. I always learn so much from
these discussion groups. :)

Thanks again! :)




"Mike H" wrote:

Hi,

It's a bit messy but here's a formula to do it.

=IF(Sheet1!A1<"","Product 1: "& Sheet1!A1&" ","")&
IF(Sheet2!A1<"","Product 2: "& Sheet2!A1&" ","")&IF(Sheet3!A1<"","Product
3: "& Sheet3!A1,"")

Mike

"FJ" wrote:

Hi, Im using the following formula to pull data from several worksheets onto
another worksheet:
=CONCATENATE("Product 1: ",Sheet1!A1,"
","Product 2: ",Sheet2!A1,"
","Product 3: ",Sheet3!A1)

This gives me the following result, assuming that the data in A1 on Sheets
1, 2, and 3 was Apples, Oranges, and Pears, respectively:
Product 1: Apples
Product 2: Oranges
Product 3: Pears

This works fine. The only problem is if there were no data in, say, cell A1
on Sheet3. Then my results would look like this:
Product 1: Apples
Product 2: Oranges
Product 3:

I would like to use a formula or a macro that would allow me to do a sort of
€œconcatenate if€� type of thing, so that if a cell is blank, it would not
concatenate the €œProduct€� part. Im not sure if this can be done with a
regular Excel formula. I thinking it might need a user defined function or a
macro, if its possible at all. Can anyone help? Thanks in advance for any
information.