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

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

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

pootle flump ur codings are working excelent.

Damn slow fingers

pootle flump ur codings are working excelent.

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.

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)

Hi,

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

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.

Cool, glad you got it working.