| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
| How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
| In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
| I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
| populating sheets based on data from parent sheets | Excel Discussion (Misc queries) | |||
Linear Mode
