Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37

    Unanswered: CONCATENATE - limits?

    Hi All,

    I had written what is presumably a very basic formula in that im sure there is a much easier way to do this but I have come across a problem. The formula below was working fine until i tried to add a further if to the statement

    =IF(B41=1,B42&CONCATENATE(D41),IF(B41=2,B42&CONCAT ENATE(E41),IF(B41=3,B42&CONCATENATE(F41),IF(B41=4, B42&CONCATENATE(G41),IF(B41=5,B42&CONCATENATE(H41) ,IF(B41=6,B42&CONCATENATE(I41),IF(B41=7,B42&CONCAT ENATE(J41),0)))))))

    I had tried to add the following below to the statement

    IF(B41=8,B42&CONCATENATE(K41), if(B41=9,B42&concatenate(l41),if(B41=10,B42&concat enate(m41),if(B41=11,B42&concatenate(n41),if(B41=1 2,B42&concatenate(O41),0)

    and got a forumla error message pop up on the word CONCATENATE on the first part of the second statement. I couldnt see how this differed to the previous and so can only assume there is a limit to the number eith of CONCATENATES you can have in a statement or the number of IFS you can nest within an original. Any help would be appreciated.
    The Statemenet is intended to bring together a string from once cell with a number stored in another cell

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    There is a limit of 7 nested functions in XL 2003 and earlier.

    As far as I can see you can use a formula along the lines of this instead:

    =B42&INDEX(D41:J41,1,B41)


    Hope that helps...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It helps if you pop stuff like that in [code ] [/code ] tags (no spaces).

    Starting point:
    You are missing an = in the second formula
    You are missing lots of closing parentheses in the second formula

    Also, error messages are useful when attempting to identify errors
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Damn slow fingers
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    Thanks Colin,

    Is there a way i can get around the limit of 7, what i need is to get 12 selections (it adds a different value based on the month of the year.

  6. #6
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    the reason there is no equals on the second function is that I explained that it was to be put into the first function, but at the point of the second function it error, i split it into two to be a bit more helpful sorry, i know about the end parenthesys also, but i had just copied and pasted something that I had taken out from inside the original statement sorry.

    With regards to the error itself its just the bog standar microsoft error to say that there has been an error in the formula with no basic description (good old helpful MS)

  7. #7
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Quote Originally Posted by lovinfeelin View Post
    Thanks Colin,

    Is there a way i can get around the limit of 7, what i need is to get 12 selections (it adds a different value based on the month of the year.
    You cannot exceed this limit. You will have to use a different formula, such as the one I posted. [you may have to make some small adjustments to suit your needs]

    Give it a try - if you run into problems just reply on the thread with some more information...

  8. #8
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    Sorry Colin, my mistake, i saw your code and didnt really think much of it.

    I put that into my spreadsheet and that does exactly what I wanted to after I changed the value so that the index went from D41:O41. Thank you very much for your help, its now up to me to go and read up on Index as it seems very useful.

  9. #9
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Cool, glad you got it working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •