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

willow1985

Board Regular
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

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

Fluff

MrExcel MVP, Moderator
You're missing a closing bracket after "CC"

too slow
 
Last edited:

willow1985

Board Regular
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

MrExcel MVP, Moderator
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?
 
Last edited:

willow1985

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

Fluff

MrExcel MVP, Moderator
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

Board Regular
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
 

Some videos you may like

This Week's Hot Topics

Top