=MCONCAT not bringing back what I'd expect

  • Evening all,


    assuming I have Sheets2 to Sheets6 all with "hello" in cell A1 and I want to concat these all into a cell :


    =INDIRECT("'Sheet"&ROW(2:6)&"'!A1") seems to hold the answer if I check via F2 then F9 :


    {"hello";"hello";"hello";"hello";"hello";}


    if I then apply the =MCONCAT function to this, it only brings back the first string entered normally :


    =MCONCAT(INDIRECT("'Sheet"&ROW(2:6)&"'!A1"),";")


    "hello"


    or array-enetered :


    {=MCONCAT(INDIRECT("'Sheet"&ROW(2:6)&"'!A1"),";")}


    "hello"



    rather than all 5 occurances....


    what am I missing ?


    thanks
    Chris

  • I may be thinking too simple for what you are trying to do, but wouldn't this solve your dilema?


    =CONCATENATE(Sheet2!A1,Sheet3!A1,Sheet4!A1,Sheet5!A1,Sheet6!A1)


    This results in the formula cell displaying "hellohellohellohellohello".


    Does that work?

  • thanks,


    totally, yes it would :-)


    but I have 170 sheets and would rather use the =row(1:170) if possible as it would save lots of time and typing


    I'm also new to this use of array formulae so when it don't work, I wanna know why !


    but yes, your method would work, thanks for the advice, it's just that I'm not after an alternative - I want to know why my method of attacking it doesn't work


    sorry - I should have made it clearer


    :)

  • Hi Chris,


    I feel your pain. Just when I expect my cat
    to return with a mouse she shows up with a
    bird, expecting a bird shows up with a mouse.


    It must be an obvious bug with XP.


    I never experienced this prob. with 97?


    James

  • Chris although I am not quite sure why your formula would not work I did some tests on it using Harland Groves function and it seems to show that it is a range. I took a different approach I used =MCONCAT(THREED(Sheet1:Sheet6!A1)," ,") and this will give you the desired results but I know that this still does not answer our question! I will continue to look into it because I am just as curious now to find out why!!:thumbup:


  • Hey James,


    yeah, I hear you.... my cat regularly carpets the floor with feathers too !!!!


    thanks....... worse still though is that I'm running this on '97 at home - I'll try it in XP at work on Friday to see if it performs any better


    Chris
    :)

  • Quote

    Originally posted by PJ KARAFFA
    Chris although I am not quite sure why your formula would not work I did some tests on it using Harland Groves function and it seems to show that it is a range. I took a different approach I used =MCONCAT(THREED(Sheet1:Sheet6!A1)," ,") and this will give you the desired results but I know that this still does not answer our question! I will continue to look into it because I am just as curious now to find out why!!:thumbup:


    Hey PJ,


    thanks for your time on this......


    it's very odd.... my values are sitting in the array when I hit F2 and F9 before applying the MCONCAT function so I know I must be overlooking something


    maybe I'll post this up in Mr Excel and hope Aladin spots it, he's usually a rock on these kinds of questions


    :-)

  • Hi C D,


    It´s more straight and honest to point to the original thread where You got Your answer from:


    http://216.92.17.166/board/vie…php?topic=41215&forum=2&5


    ...and for some reason I personally dislike cross-posting like this because people who spend their time on one list are not aware of that other people also do it at the same time.


    Q:
    What do You want to achieve by this cross-posting, especially when You know that Aladin is a specialist on Laurent Longre's utility :o


    Q:
    Since You are one of 5 moderator on Mr Excel board I find the behaviour strange and can only regret the way You act :cul:


    BTW, for those who read this thread the function-library that above function are part of is named and can be downloaded here : MoreFunc

  • Chris,


    I saw that this morning. I could not believe that that was it. It makes total sence to me though! Aladin is a genious! He does not miss a trick! Thanks for filling me in anyway!!:thumbup:

  • Lighten up Dennis,


    I asked the question here.


    3 days later I had no solution, so asked the same question at Mr Excel, having spent the intervening days still trying to solve it.


    Aladin solved it. I thanked him, as usual. I reported the results back here.


    Many people would not call this *cross-posting* as you call it, it's called utilising the wealth of the internet to solve a problem. Just because I post it on one site doesn't mean to say I can't seek an answer somewhere else.


    As for being *honest*....


    well..... what can I say - that's a strong adjective to start off an accusation with.

  • Chris,


    Both You and me knows that Aladin frequent Mr Excel and that he is a specialist in using and intepretate MOREFUNC.


    The most appropiate place to start would then be...:o2


    And for cross-posting - Yes, that my opinion, especially when You never stated anywhere that the question have been asked elsewhere.


    You only reported back the outcome of, on it's face, from a private discussion and not that You have received the answer from a thread at Mr Excel which is poor :cul:

  • "Both You and me knows that Aladin frequent Mr Excel and that he is a specialist in using and intepretate MOREFUNC."


    that's why I asked him, 3 days after not getting any other replies


    "The most appropiate place to start would then be..."


    Do NOT dictate to me where I should post my questions - you do not own this forum, you do not own Mr Excel and you do not own the internet


    "You only reported back the outcome of, on it's face, from a private discussion and not that You have received the answer from a thread at Mr Excel which is poor"


    What's the difference, an answer is an answer whether by thread on a board or private message- I specified to PJ (to whom my answer was directed to) that it was Aladin, he seemed quite happy with this : anybody who understood the question, the answer and who Aladin is doesn't need the specific thread which contains no further extra info


    This is the 2nd time you have criticised me and the 2nd time you have accused me of something that *you* don't like. I don't see anybody else complaining about my contributions here, in fact I see a lot of "thanks Chris".


    I see your contributions - they are excellent. I learn a lot from them.


    now please, lighten up.

  • Hi CD,


    Quote


    Do NOT dictate to me where I should post my questions - you do not own this forum, you do not own Mr Excel and you do not own the internet


    Sound like You're pissed of or upset
    :lol: :lol:


    #1 - Never reply when You're upset.



    Quote


    What's the difference, an answer is an answer whether by thread on a board or private message


    Is it :puzzled:


    #2 - always refer to the source if it's available


    [/quote]
    This is the 2nd time you have criticised me and the 2nd time you have accused me of something that *you* don't like. I don't see anybody else complaining about my contributions here, in fact I see a lot of "thanks Chris".
    [quote]


    Sorry CD - it´s the first time in public
    :tumble: but not the last time :cool:


    # 3 Only refer to fact.


    # I don´t care if You like me or not and I don't care if You like or not my contribution :lol: :lol:


    CD - I must admit that I'm surprised over the way You act here by


    * complaining about slow connection to Mr Excel where You act as a moderator and a frequent distributor and also aware of that they will switch to a better board like Ivan Motala said.


    This is irrelevant for this board and create only badwill for Mr Excel which is unprofessional.


    * Tagg JIUK post and foolin around with bad jokes in the Help-forum.


    * Cross-posting and not referring to the sources You use.


    To sum up: Unprofessional - :cul:

  • you're supposed to be the professional Dennis - you have your own forum and website. This is your business.


    Me ? I'm a volunteer here. I do my work in an Art Gallery from 9am-6pm. Then at 7pm, in my home time, I log in here and try to help people out. I volunteer, I do not get paid, I am not a professional technical person like yourself.


    I am not upset : I am disappointed with your childlike attitude and flippancy.


    Dave - I am surprised at Dennis's remarks throughout this whole thread, coupled with the accusation he levelled at me via private messages. Could you please ask him to calm down and treat us all with the level of respect he'd be expecting us to treat him with.


    thanks
    Chris

  • Hi CD,


    Is this how You solve Your problem by running to the owner of the board


    :cul: :cul: :cul:


    So next time I should address my critism to Dave instead of You


    :puzzled: :puzzled: :puzzled:


    Personally I solve my own problem by myself
    :cool: :cool: :cool:


    The most funny part:
    You don't defend Yourself and Your acting here
    :bouncy: :bouncy: :bouncy:


    Well, Dave is welcome to me anytime to discuss my critism of You because I stand up and I always defend my opinion
    :) :) :)


    ...and I will still kick Your ass if necessary
    :D :D :D

  • Dennis,


    defend myself ?


    I really don't think I've done anything wrong.


    Yes, I'm asking Dave - it's his site, not yours.


    You'll "kick my ass" ? Fine.... what were you saying about "professionalism" earlier ?


    Come on Dennis - please stop being silly.

  • CD,


    Quote


    Yes, I'm asking Dave - it's his site, not yours.


    So whenever somebody strongly disagree
    You run to the owner and complain/cry on the shoulder of the owner
    :cul: :cul: :cul:


    Is this what You also do on other boards as well
    :puzzled: :puzzled: :puzzled:


    Seems very strange to me and I wonder why You need to tell me what You will do or not.
    :question::question::question:


    I still critism You
    :D :D :D

  • "So whenever somebody strongly disagree
    You run to the owner and complain/cry on the shoulder of the owner
    :cul: :cul: :cul:"


    When they resort to threats, yes I complain


    "Is this what You also do on other boards as well
    :puzzled: :puzzled: :puzzled:"


    no, is this how you conduct your own forum ?



    "I still critism You
    :D :D :D"


    so it would seem.