Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate first:last! worksheet
I need to concatenate 150 rows in 50 different worksheets for a totals page,
and may need to add additional worksheets in later versions. So, I need P7 in all 50 worksheets to be concatenated on P6 in the totals page, P8 in all worksheets to be concatenated on P7 in the totals page, etc. I am wondering if it's possible to use something like first:last! to concatenate the worksheet range, making it possible to include additional sheets added in before the current last worksheet. I am using "&" for the concatenate function now as I have more than 30 sheets to concatenate, but this means new sheets I add in have to be manually entered into the formula. Thanks in advance for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate first:last! worksheet
This UDF might help you. To install: Right-click on sheet tab, view code.
Insert - Module. Paste this in. Close VBE. '=========== Function TotalConc(r As Range, s As String, Optional d As String = ", ") As String 'r is the cell you want to concatenate 's is the sheet you don't want to include in concatenation 'd is the string seperator you want to use x = 0 For Each ws In ThisWorkbook.Worksheets If ws.Name < s Then TotalConc = TotalConc & ws.Range(r.Address).Value & d End If Next TotalConc = Left(TotalConc, Len(TotalConc) - Len(d)) End Function '================= Your formula in your workbook then becomes something like: =TotalConc(P6,"Total Sheet") Note that this formula does not require bookend sheets (such as "first" and "last") and that you tell the formula which sheet you don't want to include in the concatenation. Also, you can choose a string seperator if you want, the default it a comma. To use a semicolon: =TotalConc(P6,"Total Sheet","; ") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JStiehl" wrote: I need to concatenate 150 rows in 50 different worksheets for a totals page, and may need to add additional worksheets in later versions. So, I need P7 in all 50 worksheets to be concatenated on P6 in the totals page, P8 in all worksheets to be concatenated on P7 in the totals page, etc. I am wondering if it's possible to use something like first:last! to concatenate the worksheet range, making it possible to include additional sheets added in before the current last worksheet. I am using "&" for the concatenate function now as I have more than 30 sheets to concatenate, but this means new sheets I add in have to be manually entered into the formula. Thanks in advance for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate first:last! worksheet
Thanks so much for your help Luke. I installed the code from the tab on my
totals page and it worked, but I have a couple of questions. Here is the exact code I entered in the module: Function TotalConc(P7 As Range, TOTALS As String, Optional d As String = ", ") x = 0 For Each ws In ThisWorkbook.Worksheets If ws.Name < TOTALS Then TotalConc = TotalConc & ws.Range(P7.Address).Value & d End If Next TotalConc = Left(TotalConc, Len(TotalConc) - Len(d)) End Function I tried to put a range in (P7:P150), but it would not accept this. Does that mean I have to enter in a new code for each of these cells (P7 through P150)? Also, on my totals page I entered this formula to total the P7's: =TotalConc(P7,"Total Sheet") This did total P7, but also gave me a series of commas. I'm new to UDF's, so I thank you in advance for your patience. "Luke M" wrote: This UDF might help you. To install: Right-click on sheet tab, view code. Insert - Module. Paste this in. Close VBE. '=========== Function TotalConc(r As Range, s As String, Optional d As String = ", ") As String 'r is the cell you want to concatenate 's is the sheet you don't want to include in concatenation 'd is the string seperator you want to use x = 0 For Each ws In ThisWorkbook.Worksheets If ws.Name < s Then TotalConc = TotalConc & ws.Range(r.Address).Value & d End If Next TotalConc = Left(TotalConc, Len(TotalConc) - Len(d)) End Function '================= Your formula in your workbook then becomes something like: =TotalConc(P6,"Total Sheet") Note that this formula does not require bookend sheets (such as "first" and "last") and that you tell the formula which sheet you don't want to include in the concatenation. Also, you can choose a string seperator if you want, the default it a comma. To use a semicolon: =TotalConc(P6,"Total Sheet","; ") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JStiehl" wrote: I need to concatenate 150 rows in 50 different worksheets for a totals page, and may need to add additional worksheets in later versions. So, I need P7 in all 50 worksheets to be concatenated on P6 in the totals page, P8 in all worksheets to be concatenated on P7 in the totals page, etc. I am wondering if it's possible to use something like first:last! to concatenate the worksheet range, making it possible to include additional sheets added in before the current last worksheet. I am using "&" for the concatenate function now as I have more than 30 sheets to concatenate, but this means new sheets I add in have to be manually entered into the formula. Thanks in advance for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate first:last! worksheet
I got it to work now, my only problem is finding a way to ignore blank cells.
It puts a string seperator in for blank cells--is there any way to prevent this? Thanks again so much. Here is your UDF I used: Function TotalConc(r As Range, s As String, Optional d As String = ", ") x = 0 For Each ws In ThisWorkbook.Worksheets If ws.Name < s Then TotalConc = TotalConc & ws.Range(r.Address).Value & d End If Next TotalConc = Left(TotalConc, Len(TotalConc) - Len(d)) End Function Here is the formula I used in my workbook to test it: =TotalConc(P7,"Totals Sheet") "Luke M" wrote: This UDF might help you. To install: Right-click on sheet tab, view code. Insert - Module. Paste this in. Close VBE. '=========== Function TotalConc(r As Range, s As String, Optional d As String = ", ") As String 'r is the cell you want to concatenate 's is the sheet you don't want to include in concatenation 'd is the string seperator you want to use x = 0 For Each ws In ThisWorkbook.Worksheets If ws.Name < s Then TotalConc = TotalConc & ws.Range(r.Address).Value & d End If Next TotalConc = Left(TotalConc, Len(TotalConc) - Len(d)) End Function '================= Your formula in your workbook then becomes something like: =TotalConc(P6,"Total Sheet") Note that this formula does not require bookend sheets (such as "first" and "last") and that you tell the formula which sheet you don't want to include in the concatenation. Also, you can choose a string seperator if you want, the default it a comma. To use a semicolon: =TotalConc(P6,"Total Sheet","; ") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JStiehl" wrote: I need to concatenate 150 rows in 50 different worksheets for a totals page, and may need to add additional worksheets in later versions. So, I need P7 in all 50 worksheets to be concatenated on P6 in the totals page, P8 in all worksheets to be concatenated on P7 in the totals page, etc. I am wondering if it's possible to use something like first:last! to concatenate the worksheet range, making it possible to include additional sheets added in before the current last worksheet. I am using "&" for the concatenate function now as I have more than 30 sheets to concatenate, but this means new sheets I add in have to be manually entered into the formula. Thanks in advance for your help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate first:last! worksheet
I have resolved the other issues I was having, but am having trouble with
this function working on new spreadhsheets I create based on this one. Is there something extra I need to do? Thanks in advance for your help. "Luke M" wrote: This UDF might help you. To install: Right-click on sheet tab, view code. Insert - Module. Paste this in. Close VBE. '=========== Function TotalConc(r As Range, s As String, Optional d As String = ", ") As String 'r is the cell you want to concatenate 's is the sheet you don't want to include in concatenation 'd is the string seperator you want to use x = 0 For Each ws In ThisWorkbook.Worksheets If ws.Name < s Then TotalConc = TotalConc & ws.Range(r.Address).Value & d End If Next TotalConc = Left(TotalConc, Len(TotalConc) - Len(d)) End Function '================= Your formula in your workbook then becomes something like: =TotalConc(P6,"Total Sheet") Note that this formula does not require bookend sheets (such as "first" and "last") and that you tell the formula which sheet you don't want to include in the concatenation. Also, you can choose a string seperator if you want, the default it a comma. To use a semicolon: =TotalConc(P6,"Total Sheet","; ") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JStiehl" wrote: I need to concatenate 150 rows in 50 different worksheets for a totals page, and may need to add additional worksheets in later versions. So, I need P7 in all 50 worksheets to be concatenated on P6 in the totals page, P8 in all worksheets to be concatenated on P7 in the totals page, etc. I am wondering if it's possible to use something like first:last! to concatenate the worksheet range, making it possible to include additional sheets added in before the current last worksheet. I am using "&" for the concatenate function now as I have more than 30 sheets to concatenate, but this means new sheets I add in have to be manually entered into the formula. Thanks in advance for your help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate first:last! worksheet
You can ignore blank cells with something like:
If ws.Range(r.Address).Value < "" Then TotalConc = TotalConc & ws.Range(r.Address).Value & d x = x + 1 End If You have to initialize x to 0 at the outset. The incrementing is there in case all the cells are blank. If that happens, your concatenated string has a length of 0 and attempting to strip the last separator causes a #VALUE error. So end the function with something like If x 0 Then TotalConc = Left(TotalConc, Len(TotalConc) - Len(d)) End If "JStiehl" wrote: I got it to work now, my only problem is finding a way to ignore blank cells. It puts a string seperator in for blank cells--is there any way to prevent this? Thanks again so much. Here is your UDF I used: Function TotalConc(r As Range, s As String, Optional d As String = ", ") x = 0 For Each ws In ThisWorkbook.Worksheets If ws.Name < s Then TotalConc = TotalConc & ws.Range(r.Address).Value & d End If Next TotalConc = Left(TotalConc, Len(TotalConc) - Len(d)) End Function Here is the formula I used in my workbook to test it: =TotalConc(P7,"Totals Sheet") "Luke M" wrote: This UDF might help you. To install: Right-click on sheet tab, view code. Insert - Module. Paste this in. Close VBE. '=========== Function TotalConc(r As Range, s As String, Optional d As String = ", ") As String 'r is the cell you want to concatenate 's is the sheet you don't want to include in concatenation 'd is the string seperator you want to use x = 0 For Each ws In ThisWorkbook.Worksheets If ws.Name < s Then TotalConc = TotalConc & ws.Range(r.Address).Value & d End If Next TotalConc = Left(TotalConc, Len(TotalConc) - Len(d)) End Function '================= Your formula in your workbook then becomes something like: =TotalConc(P6,"Total Sheet") Note that this formula does not require bookend sheets (such as "first" and "last") and that you tell the formula which sheet you don't want to include in the concatenation. Also, you can choose a string seperator if you want, the default it a comma. To use a semicolon: =TotalConc(P6,"Total Sheet","; ") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JStiehl" wrote: I need to concatenate 150 rows in 50 different worksheets for a totals page, and may need to add additional worksheets in later versions. So, I need P7 in all 50 worksheets to be concatenated on P6 in the totals page, P8 in all worksheets to be concatenated on P7 in the totals page, etc. I am wondering if it's possible to use something like first:last! to concatenate the worksheet range, making it possible to include additional sheets added in before the current last worksheet. I am using "&" for the concatenate function now as I have more than 30 sheets to concatenate, but this means new sheets I add in have to be manually entered into the formula. Thanks in advance for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate N cells (where N is a worksheet value) | Excel Worksheet Functions | |||
Concatenate | Excel Worksheet Functions | |||
concatenate and then take off last # | Excel Discussion (Misc queries) | |||
=CONCATENATE() help! | Excel Worksheet Functions | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel |