I have a workbook that has 41 sheets and a summary sheet. I have information
in cells that I want to collect the data. the formula that I have is:

=CONCATENATE(Sheet1!C6,", ",Sheet2!C6,", ",Sheet3!C6,", ",Sheet4!C6,",
",Sheet5!C6,", ",Sheet7!C6,", ",Sheet8!C6,", ",Sheet9!C6,", ",Sheet11!C6,",
",Sheet12!C6,", ",Sheet13!C6,", ",Sheet14!C6)

I can not add any more sheets. What am I doing wrong or is there an easier
way to collect this information. Every sheet is the same format just
different text.
Thank you,

most functions in Excel only allow thirty items in the array list
I would recommend
=concatenate(Sheet1!C6.". ",...Sheet10!C6,",
")&concatinate(Sheet11!...)&concatenate(Sheet21!...
of just
=sheet1!C6&", "&Sheet2!C6... and not use concatenate at all

"gnuoyt" wrote:

> I have a workbook that has 41 sheets and a summary sheet. I have information
> in cells that I want to collect the data. the formula that I have is:
>
> =CONCATENATE(Sheet1!C6,", ",Sheet2!C6,", ",Sheet3!C6,", ",Sheet4!C6,",
> ",Sheet5!C6,", ",Sheet7!C6,", ",Sheet8!C6,", ",Sheet9!C6,", ",Sheet11!C6,",
> ",Sheet12!C6,", ",Sheet13!C6,", ",Sheet14!C6)
>
> I can not add any more sheets. What am I doing wrong or is there an easier
> way to collect this information. Every sheet is the same format just
> different text.
> Thank you,

You could replace your =concatenate() function with the & operator:

=sheet1!c6&", "&sheet!c6&","&....



gnuoyt wrote:
>
> I have a workbook that has 41 sheets and a summary sheet. I have information
> in cells that I want to collect the data. the formula that I have is:
>
> =CONCATENATE(Sheet1!C6,", ",Sheet2!C6,", ",Sheet3!C6,", ",Sheet4!C6,",
> ",Sheet5!C6,", ",Sheet7!C6,", ",Sheet8!C6,", ",Sheet9!C6,", ",Sheet11!C6,",
> ",Sheet12!C6,", ",Sheet13!C6,", ",Sheet14!C6)
>
> I can not add any more sheets. What am I doing wrong or is there an easier
> way to collect this information. Every sheet is the same format just
> different text.
> Thank you,

--

Dave Peterson