Formula cannot be locked or top down spread
If the original formula is too long or if an MPCData formula references a cell that contains a concatenating string, the formula may fail to lock or top down spread for the following reasons:
- MPCData formula with
concatenating string
- If an MPCData formula
references a cell that contains a concatenating string, the concatenating
string must use this basic syntax:
=<string1>&<string2> - If the concatenating
string does not include the & character but uses another function to
concatenate strings, such as
Concatenate(<string1>,<string2>), then the cell containing the MPCData formula will display the correct value but will no longer be writable, nor can it be locked or used for top down spreading.
- If an MPCData formula
references a cell that contains a concatenating string, the concatenating
string must use this basic syntax:
- Original formula too long
- When data is inserted, a validation formula is created. The validation formula can be viewed from the Settings tab on the Data Validation dialog box, . If the validation formula exceeds a 255-character limit, an alternate storing mechanism is used, a hidden Excel sheet. When moving the formula to the hidden sheet, the worksheet name is appended to each cell reference in the formula. If the extended formula exceeds the 1,024-character limit for the Excel sheet after the sheet name is added, then the cell with the original formula cannot be locked or top down spread.
- If a formula must be stored using the alternative method, and the extended formula exceeds 1,024 characters, an error message displays at the time data is inserted. The following is a sample error message you might see:
- There was a problem with the formulas added to the following cells. The calculations may appear correct but the Lock and Spread feature will not be available for those cells. This could be because of an invalid formula or a formula that is too long. Check the Event Log for details of the individual formulas. Cells with errors: $D$4, $E$4, $F$4, $I$4, $J$4
- You can try to resolve this issue by:
- Limiting the worksheet name to as few characters as possible
- Limiting the number of cell references in the formula