1. Registered User
Join Date
Feb 2007
Location
Devon, UK
Posts
37

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)))))))

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. Registered User
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. King of Understatement
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

4. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Damn slow fingers

5. Registered User
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. Registered User
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. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

Originally Posted by lovinfeelin
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]

8. Registered User
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. Registered User
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
•