| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating a range of cells from another sheet, possible?
Okay, so I've got an XLS I've written to handle interview data for a project I'm working on. One of the aspects of this is that I need to take a group of cells defined in one location, concatenate them together on different lines in the same cell, and insert that in multiple spots to serve as a reference. To date I've come up with zilch in terms of automating this using some conbination of CONCATENATE, INDIRECT, and ROW (which was the solution I thought might get me there). A solution that I tried but that failed is the following: CONCATENATE(INDIRECT("'"&"Interview Summary"&"'!"$A"ROW(INDIRECT("77:88"))) Which always ended up resolving to 77 and giving me a single value. The following is the ugly solution I'm currently leveraging: =CONCATENATE( IF(ISTEXT('Interview Summary'!$A$77),'Interview Summary'!$A$77,""), IF(ISTEXT('Interview Summary'!$A$78),CHAR(10)&'Interview Summary'!$A $78,""), IF(ISTEXT('Interview Summary'!$A$79),CHAR(10)&'Interview Summary'!$A $79,""), IF(ISTEXT('Interview Summary'!$A$80),CHAR(10)&'Interview Summary'!$A $80,""), IF(ISTEXT('Interview Summary'!$A$81),CHAR(10)&'Interview Summary'!$A $81,""), IF(ISTEXT('Interview Summary'!$A$82),CHAR(10)&'Interview Summary'!$A $82,""), IF(ISTEXT('Interview Summary'!$A$83),CHAR(10)&'Interview Summary'!$A $83,""), IF(ISTEXT('Interview Summary'!$A$84),CHAR(10)&'Interview Summary'!$A $84,""), IF(ISTEXT('Interview Summary'!$A$85),CHAR(10)&'Interview Summary'!$A $85,""), IF(ISTEXT('Interview Summary'!$A$86),CHAR(10)&'Interview Summary'!$A $86,""), IF(ISTEXT('Interview Summary'!$A$87),CHAR(10)&'Interview Summary'!$A $87,""), IF(ISTEXT('Interview Summary'!$A$88),CHAR(10)&'Interview Summary'!$A $88,""), ) Is it possible to do this in a better, more automated way? Excel's function language is extremely annoying (I could write this in js, perl, or any number of languages in about five seconds). Does anyone have any idea how I could implement a more universal version of this that would be easier to update, etc.? Thanks in advance for any help. -rt |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenating a range of cells from another sheet, possible?
Hi
Take a look at JE MCGimpsey's Muticat code. That might give you a start in trying to achieve what you want. http://www.mcgimpsey.com/excel/udfs/multicat.html -- Regards Roger Govier wrote in message ups.com... Okay, so I've got an XLS I've written to handle interview data for a project I'm working on. One of the aspects of this is that I need to take a group of cells defined in one location, concatenate them together on different lines in the same cell, and insert that in multiple spots to serve as a reference. To date I've come up with zilch in terms of automating this using some conbination of CONCATENATE, INDIRECT, and ROW (which was the solution I thought might get me there). A solution that I tried but that failed is the following: CONCATENATE(INDIRECT("'"&"Interview Summary"&"'!"$A"ROW(INDIRECT("77:88"))) Which always ended up resolving to 77 and giving me a single value. The following is the ugly solution I'm currently leveraging: =CONCATENATE( IF(ISTEXT('Interview Summary'!$A$77),'Interview Summary'!$A$77,""), IF(ISTEXT('Interview Summary'!$A$78),CHAR(10)&'Interview Summary'!$A $78,""), IF(ISTEXT('Interview Summary'!$A$79),CHAR(10)&'Interview Summary'!$A $79,""), IF(ISTEXT('Interview Summary'!$A$80),CHAR(10)&'Interview Summary'!$A $80,""), IF(ISTEXT('Interview Summary'!$A$81),CHAR(10)&'Interview Summary'!$A $81,""), IF(ISTEXT('Interview Summary'!$A$82),CHAR(10)&'Interview Summary'!$A $82,""), IF(ISTEXT('Interview Summary'!$A$83),CHAR(10)&'Interview Summary'!$A $83,""), IF(ISTEXT('Interview Summary'!$A$84),CHAR(10)&'Interview Summary'!$A $84,""), IF(ISTEXT('Interview Summary'!$A$85),CHAR(10)&'Interview Summary'!$A $85,""), IF(ISTEXT('Interview Summary'!$A$86),CHAR(10)&'Interview Summary'!$A $86,""), IF(ISTEXT('Interview Summary'!$A$87),CHAR(10)&'Interview Summary'!$A $87,""), IF(ISTEXT('Interview Summary'!$A$88),CHAR(10)&'Interview Summary'!$A $88,""), ) Is it possible to do this in a better, more automated way? Excel's function language is extremely annoying (I could write this in js, perl, or any number of languages in about five seconds). Does anyone have any idea how I could implement a more universal version of this that would be easier to update, etc.? Thanks in advance for any help. -rt |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenating a range of cells from another sheet, possible?
wrote...
.... The following is the ugly solution I'm currently leveraging: =CONCATENATE( IF(ISTEXT('Interview Summary'!$A$77), 'Interview Summary'!$A$77,""), IF(ISTEXT('Interview Summary'!$A$78),CHAR(10)& 'Interview Summary'!$A$78,""), IF(ISTEXT('Interview Summary'!$A$79),CHAR(10)& 'Interview Summary'!$A$79,""), IF(ISTEXT('Interview Summary'!$A$80),CHAR(10)& 'Interview Summary'!$A$80,""), IF(ISTEXT('Interview Summary'!$A$81),CHAR(10)& 'Interview Summary'!$A$81,""), IF(ISTEXT('Interview Summary'!$A$82),CHAR(10)& 'Interview Summary'!$A$82,""), IF(ISTEXT('Interview Summary'!$A$83),CHAR(10)& 'Interview Summary'!$A$83,""), IF(ISTEXT('Interview Summary'!$A$84),CHAR(10)& 'Interview Summary'!$A$84,""), IF(ISTEXT('Interview Summary'!$A$85),CHAR(10)& 'Interview Summary'!$A$85,""), IF(ISTEXT('Interview Summary'!$A$86),CHAR(10)& 'Interview Summary'!$A$86,""), IF(ISTEXT('Interview Summary'!$A$87),CHAR(10)& 'Interview Summary'!$A$87,""), IF(ISTEXT('Interview Summary'!$A$88),CHAR(10)& 'Interview Summary'!$A$88,""),) .... First off, don't mix CONCATENATE and &. At the very least it's confusing. Better to use & everywhere. If you won't have any embedded linefeed or ASCII 127 (DEL) chars in any of these cells' contents, you could try =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( T('Interview Summary'!$A$77)&CHAR(10)& T('Interview Summary'!$A$78)&CHAR(10)& T('Interview Summary'!$A$79)&CHAR(10)& T('Interview Summary'!$A$80)&CHAR(10)& T('Interview Summary'!$A$81)&CHAR(10)& T('Interview Summary'!$A$82)&CHAR(10)& T('Interview Summary'!$A$83)&CHAR(10)& T('Interview Summary'!$A$84)&CHAR(10)& T('Interview Summary'!$A$85)&CHAR(10)& T('Interview Summary'!$A$86)&CHAR(10)& T('Interview Summary'!$A$87)&CHAR(10)& T('Interview Summary'!$A$88)&CHAR(10)& ""," ",CHAR(127)),CHAR(10)," "))," ",CHAR(10)),CHAR(127)," ") Still ugly, but more easily extended. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenating a range of cells from another sheet, possible?
Hi
What do you really want to do? Your formulas are such a mess I don't have a quess what you intented to do with them! CONCANTENATE (or & operator) connects several strings, and returns a string (NB! Not a cell/range reference!). P.e. =CONCANTENATE("A","B","C") , or ="A" & "B" & "C" returns a string "ABC". =CONCANTENATE("Sheet1!","A1") returns a string "Sheet1!A1", not a reference to cell Sheet1!A1, etc. INDIRECT returns a cell/range reference from a string representing such reference. P.e. =INDIRECT("Sheet1A1") returns a reference to cell Sheet1!A1, i.e. it is same as formula =Sheet1!A1 The formula =INDIRECT("Sheet1!A1:A10") returns an error, because you can't have a direct reference to multicell range in a cell. But you can use it as range parameter in other functions, like =SUM(INDIRECT("Sheet1!A1:A10")) , which is same as =SUM(Sheet1!A1:A10) The formula =INDIRECT("SUM(Sheet1!A1:A10)") returns an error, of-course. I think it is obvious why! NB! The INDIRECT function works with external sources only when the source workbook is opened. So it will be not very bright idea to write a formula like: =INDIRECT("'C:\My Documents\[" & Sheet1!A1 & ".xls]" & Sheet1!B1 & "'!A1") , and to try to refer to any excel file in folder My Dcuments typing file name into cell A1 and sheet name into cell B1 - onless the referred file is opened, you get an error. ROW returns an integer, representing a row number of cell reference, passed as parameter (by default the cell where you entered the formula). P.e. =ROW() =ROW(A2) etc. I can't imagine for what it was meant in your formula. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) wrote in message ups.com... Okay, so I've got an XLS I've written to handle interview data for a project I'm working on. One of the aspects of this is that I need to take a group of cells defined in one location, concatenate them together on different lines in the same cell, and insert that in multiple spots to serve as a reference. To date I've come up with zilch in terms of automating this using some conbination of CONCATENATE, INDIRECT, and ROW (which was the solution I thought might get me there). A solution that I tried but that failed is the following: CONCATENATE(INDIRECT("'"&"Interview Summary"&"'!"$A"ROW(INDIRECT("77:88"))) Which always ended up resolving to 77 and giving me a single value. The following is the ugly solution I'm currently leveraging: =CONCATENATE( IF(ISTEXT('Interview Summary'!$A$77),'Interview Summary'!$A$77,""), IF(ISTEXT('Interview Summary'!$A$78),CHAR(10)&'Interview Summary'!$A $78,""), IF(ISTEXT('Interview Summary'!$A$79),CHAR(10)&'Interview Summary'!$A $79,""), IF(ISTEXT('Interview Summary'!$A$80),CHAR(10)&'Interview Summary'!$A $80,""), IF(ISTEXT('Interview Summary'!$A$81),CHAR(10)&'Interview Summary'!$A $81,""), IF(ISTEXT('Interview Summary'!$A$82),CHAR(10)&'Interview Summary'!$A $82,""), IF(ISTEXT('Interview Summary'!$A$83),CHAR(10)&'Interview Summary'!$A $83,""), IF(ISTEXT('Interview Summary'!$A$84),CHAR(10)&'Interview Summary'!$A $84,""), IF(ISTEXT('Interview Summary'!$A$85),CHAR(10)&'Interview Summary'!$A $85,""), IF(ISTEXT('Interview Summary'!$A$86),CHAR(10)&'Interview Summary'!$A $86,""), IF(ISTEXT('Interview Summary'!$A$87),CHAR(10)&'Interview Summary'!$A $87,""), IF(ISTEXT('Interview Summary'!$A$88),CHAR(10)&'Interview Summary'!$A $88,""), ) Is it possible to do this in a better, more automated way? Excel's function language is extremely annoying (I could write this in js, perl, or any number of languages in about five seconds). Does anyone have any idea how I could implement a more universal version of this that would be easier to update, etc.? Thanks in advance for any help. -rt |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenating a range of cells from another sheet, possible?
Off topic.....
=INDEX(A:A............ Does Index index the entire column of just the used range? Biff "Harlan Grove" wrote in message oups.com... wrote... ... The following is the ugly solution I'm currently leveraging: =CONCATENATE( IF(ISTEXT('Interview Summary'!$A$77), 'Interview Summary'!$A$77,""), IF(ISTEXT('Interview Summary'!$A$78),CHAR(10)& 'Interview Summary'!$A$78,""), IF(ISTEXT('Interview Summary'!$A$79),CHAR(10)& 'Interview Summary'!$A$79,""), IF(ISTEXT('Interview Summary'!$A$80),CHAR(10)& 'Interview Summary'!$A$80,""), IF(ISTEXT('Interview Summary'!$A$81),CHAR(10)& 'Interview Summary'!$A$81,""), IF(ISTEXT('Interview Summary'!$A$82),CHAR(10)& 'Interview Summary'!$A$82,""), IF(ISTEXT('Interview Summary'!$A$83),CHAR(10)& 'Interview Summary'!$A$83,""), IF(ISTEXT('Interview Summary'!$A$84),CHAR(10)& 'Interview Summary'!$A$84,""), IF(ISTEXT('Interview Summary'!$A$85),CHAR(10)& 'Interview Summary'!$A$85,""), IF(ISTEXT('Interview Summary'!$A$86),CHAR(10)& 'Interview Summary'!$A$86,""), IF(ISTEXT('Interview Summary'!$A$87),CHAR(10)& 'Interview Summary'!$A$87,""), IF(ISTEXT('Interview Summary'!$A$88),CHAR(10)& 'Interview Summary'!$A$88,""),) ... First off, don't mix CONCATENATE and &. At the very least it's confusing. Better to use & everywhere. If you won't have any embedded linefeed or ASCII 127 (DEL) chars in any of these cells' contents, you could try =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( T('Interview Summary'!$A$77)&CHAR(10)& T('Interview Summary'!$A$78)&CHAR(10)& T('Interview Summary'!$A$79)&CHAR(10)& T('Interview Summary'!$A$80)&CHAR(10)& T('Interview Summary'!$A$81)&CHAR(10)& T('Interview Summary'!$A$82)&CHAR(10)& T('Interview Summary'!$A$83)&CHAR(10)& T('Interview Summary'!$A$84)&CHAR(10)& T('Interview Summary'!$A$85)&CHAR(10)& T('Interview Summary'!$A$86)&CHAR(10)& T('Interview Summary'!$A$87)&CHAR(10)& T('Interview Summary'!$A$88)&CHAR(10)& ""," ",CHAR(127)),CHAR(10)," "))," ",CHAR(10)),CHAR(127)," ") Still ugly, but more easily extended. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenating a range of cells from another sheet, possible?
On Mar 6, 1:53 am, "Arvi Laanemets" wrote:
Hi What do you really want to do? Your formulas are such a mess I don't have a quess what you intented to do with them! Arvi: Thanks for the post. I think it was actually really clear from my post (where I described the problem and what I'm trying to accomplish), but in the event it wasn't let me take another stab. I have 10 cells in a main sheet in a workbook that may or may not all have values (depends on the situation). I need a routine that can cycle through each of these cells, take the value if one exists, build a list of the values that is hard return delimited, and insert that list into another cell in another sheet in the workbook. To be blunt I am essentially trying to replicate simple "For" loop functionality in an Excel formula. For example, say I have A1-A10 in Sheet1 and in this instance A1-A6 are filled in. The values are as follow: Sheet1!A1 - This Sheet1!A2 - Is Sheet1!A3 - An Sheet1!A4 - Example Sheet1!A5 - Of Sheet1!A6 - Usage Sheet1!A7 - Sheet1!A8 - Sheet1!A9 - Sheet1!A10 - I need a formula I can put in a cell in another worksheet that will go through these cells to create the following: Sheet2!C8 - This Is An Example Of Usage As before, any help anyone can offer in doing this with formulas is appreciated. Thanks Roger for the pointer on MutiCat, I'm still trying to do this in Formula for now, but a VB solution may be my only route. Best, rt |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenating a range of cells from another sheet, possible?
Hi
In which case, Harlan's reply does exactly what you want. Remember to format the cell as Wrap text. -- Regards Roger Govier wrote in message oups.com... On Mar 6, 1:53 am, "Arvi Laanemets" wrote: Hi What do you really want to do? Your formulas are such a mess I don't have a quess what you intented to do with them! Arvi: Thanks for the post. I think it was actually really clear from my post (where I described the problem and what I'm trying to accomplish), but in the event it wasn't let me take another stab. I have 10 cells in a main sheet in a workbook that may or may not all have values (depends on the situation). I need a routine that can cycle through each of these cells, take the value if one exists, build a list of the values that is hard return delimited, and insert that list into another cell in another sheet in the workbook. To be blunt I am essentially trying to replicate simple "For" loop functionality in an Excel formula. For example, say I have A1-A10 in Sheet1 and in this instance A1-A6 are filled in. The values are as follow: Sheet1!A1 - This Sheet1!A2 - Is Sheet1!A3 - An Sheet1!A4 - Example Sheet1!A5 - Of Sheet1!A6 - Usage Sheet1!A7 - Sheet1!A8 - Sheet1!A9 - Sheet1!A10 - I need a formula I can put in a cell in another worksheet that will go through these cells to create the following: Sheet2!C8 - This Is An Example Of Usage As before, any help anyone can offer in doing this with formulas is appreciated. Thanks Roger for the pointer on MutiCat, I'm still trying to do this in Formula for now, but a VB solution may be my only route. Best, rt |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenating a range of cells from another sheet, possible?
On Mar 6, 11:39 am, "Roger Govier"
wrote: Hi In which case, Harlan's reply does exactly what you want. Remember to format the cell as Wrap text. True, and its appreciated. Sorry Harlan for not including you in my previous post. However, I guess I was looking for a way to do this without having to explicitly call each line... something like a variablized for loop. Thanks, Rick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I ignore blanks when concatenating cells in Excel? | Excel Discussion (Misc queries) | |||
| Concatenating info from several cells into the one above | Excel Discussion (Misc queries) | |||
| Concatenating Cells | Excel Discussion (Misc queries) | |||
| Concatenating cell values to create sheet names | Excel Discussion (Misc queries) | |||
| concatenating cells with their formats | Excel Worksheet Functions | |||
Linear Mode
