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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
If I was just comparing formula to formula I would say that the very last bracket ")" is in different places...
 
Upvote 0
You're missing a closing bracket after "CC"

too slow
 
Last edited:
Upvote 0
But you do more in 20 minutes than I do in 20 days.
(y)
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top