Hi, I’m 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. I’m 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 it’s 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, I’m 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. I’m 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 it’s 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, I’m 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. I’m 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 it’s 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, I’m 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. I’m 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 it’s possible at all. Can anyone help? Thanks in advance for any
> > > information.