"You can't change part of an array." but source cannot be located

[Sam_Mahoney]

My source database is locked from sorting or filtering by the 'you can't change part of an array' error message, but I cannot find the array formula that it is referencing in order to correct it. 

I have tried searching in formulas for .xl and { to track it down in this sheet and check links with other sheets, and have also checked my connected files (through 'edit links') to no avail. We do not use array formulas so once I've found it I'm happy to just delete it, but the frustrating thing is it's nowhere to be found. 

I'm new to array formulas so would love for somebody to tell me this is an easy fix but for now our main database is almost useless. All help appreciated!

[#2]

@Sam_Mahoney 

The error message "You can't change part of an array" typically occurs when Excel detects that you are trying to edit a portion of an array formula. These array formulas are enclosed in curly braces {} and are used to perform calculations on a range of cells.

Here are some steps you can take to locate and correct the array formula causing this issue:

1. Check for Array Formulas: Array formulas are typically used to perform complex calculations. They can be easily identified because they are enclosed in curly braces {}. To locate array formulas:
• Select the cells that are causing the error.
• Look in the Formula Bar. If you see curly braces around the formula, you have found an array formula.
2. Evaluate the Formula: Excel has a feature called "Evaluate Formula" that allows you to step through a formula to see the intermediate results. Here's how to use it:
• Select the cell with the formula.
• Go to the "Formulas" tab on the Excel Ribbon.
• Click on "Evaluate Formula" in the Formula Auditing group.
• This will open the Evaluate Formula dialog box. Click "Evaluate" to step through the formula and see which part of it is causing the issue.
3. Check Conditional Formatting: Sometimes, the error can be triggered by conditional formatting rules. Go to "Home" > "Conditional Formatting" > "Manage Rules" and see if there are any rules that reference arrays.
4. Named Ranges: If your workbook uses named ranges, check if any named ranges are causing the issue. Go to "Formulas" > "Name Manager" and review the named ranges.
5. Check Linked Data: If your workbook is linked to external data sources, it is possible that the issue is related to linked data. Go to "Data" > "Edit Links" and review the links to ensure they are up-to-date and not causing any issues.
6. Review Excel Options: Sometimes, Excel settings can cause unexpected behavior. Go to "File" > "Options" > "Formulas" and make sure that "Enable iterative calculation" and "Enable multi-threaded calculation" are appropriately set.
7. Review VBA Code: If your workbook contains VBA (macros), check the VBA code for any array-related operations.
8. Check Data Validation: If you have data validation rules in your worksheet, make sure they are not inadvertently causing the issue.
9. Check Add-Ins: If you have any Excel add-ins installed, disable them temporarily to see if one of them is causing the problem.
10. Backup and Isolate: If you are unable to locate the specific issue, consider creating a backup of your workbook and then systematically delete or isolate parts of your workbook to narrow down the cause.

Once you have located the specific formula or condition causing the error, you can either correct it or remove it. Keep in mind that array formulas can be complex, so it may take some time and careful examination to identify and fix the issue. The text, steps was created with the help of AI.

Hope this will help you.

[#3]

@Sam_Mahoney 

If you are on Pro version it shall be Inquire available, you may activate it from File->Options->Addins->COM addins

Within Inquire tab is Workbook Analysis, it will show you lot of information includes locations of the array formulae.