concatenate with more than 10 sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
You could replace your =concatenate() function with the & operator:

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