concatenate with more than 10 sheets
 

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,

concatenate with more than 10 sheets
 

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,

concatenate with more than 10 sheets
 

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