Excel stating not a valid formula when the same formula is working in another spreadsheet...

[willow1985]

Hello,

I was hoping someone would be able to help me with the below formula:

="CC "&AGGREGATE(14,6,MID(A3:A1000000,4,9)/(LEFT(A3:A1000000,3)="CC",1)+1

I have the same formula in another sheet:

="IAR "&AGGREGATE(14,6,MID(A$4:A$999952,4,9)/(LEFT(A$4:A$999952,3)="IAR"),1)+1

and it works just fine. I wanted to adapt the new one to find the next number sequence in one that starts with CC however excel is giving me the error when you are missing a bracket or the like. The number sequences look like: CC 1234

I hope someone can see what I am missing.

Thank you

Carla

 

[igold]

If I was just comparing formula to formula I would say that the very last bracket ")" is in different places...

 

[Fluff]

You're missing a closing bracket after "CC"

too slow

 

[igold]

But you do more in 20 minutes than I do in 20 days.

 

[willow1985]

Thank you. Don't know why my eyes were missing that. I am getting a #NAME ? error now *sigh
Not sure why this error.

The column it is looking in has number sequences that all look like: CC1234

I got rid of the space after the CC in the formula but am still getting the error:

="CC"&AGGREGATE(14,6,MID(A3:A1000000,4,9)/(LEFT(A3:A1000000,3)="CC"),1)+1

 

[Fluff]

If you have no space in the values in col A, then you need to change this
(LEFT(A3:A1000000,2)
Otherwise you will get #NUM!, but not sure why you are getting a Name error.

Did you copy/paste the formula into the board, or did you type it out?

 

[willow1985]

Copy and pasted.

Here is the new formula: ="CC"&AGGREGATE(14,6,MID(A3:A1000000,4,9)/(LEFT(A3:A1000000,2)="CC"),1)+1

But I am still getting the #Name ? Error...

 

[willow1985]

I uploaded the document called: CC. Here is the link:

https://www.dropbox.com/s/qlmqg5lno5s83km/CC.xls?dl=0

 

[Fluff]

That file is an xls workbook which only has 65,536 rows, but you are trying to look at 1,000,000 rows, hence the error.
With array formulae it's best to keep the number of rows to a minimum for the sake of performance

 

[willow1985]

Ok, I changed the workbook and formula and are no longer getting the error but I am not getting the correct result either.

Formula: ="CC"&AGGREGATE(14,6,MID(A3:A10000,4,9)/(LEFT(A3:A10000,2)="CC"),1)+1

Result is: CC1000 when it should be CC2820.

Do you see anything else that could be wrong with the formula?

Thank you