Tuesday, June 29, 2021

field error

The #FIELD! error occurs under the following circumstances:   

  • The referenced field is missing from a linked data type.

  • The referenced field doesn't apply to the linked data type, therefore it does not exist.

  • The referenced field is protected by attribution, therefore it can't be inserted into the grid.

  • The referenced field is in a format Excel couldn't read.

  • The referenced cell doesn't contain a linked data type.

Note: You can insert linked data types from the Data tab. You can also write formulas that reference them.

When a new column tries to reference a field from a data type record, but the record doesn't have any data for that particular field, the #FIELD! error will occur. For example, if you added a column to show stock expense ratios, but the stock record on that row doesn't have an expense ratio in the first place, then the result is #FIELD!.

Solutions:

  • If Error Checking is enabled, click the Error floatie to the left of the cell, and choose the Ignore Error option.

    Ignore a #FIELD! error

  • Remove the column, and replace it with a new column that references a field that does have data.

If you type a formula like =A1.City, or =FIELDVALUE(A1,"City"), and cell A1 doesn't use a Geography data type, the #FIELD! error will occur. Similarly, if you create a formula like =A1.Price, or =FIELDVALUE(A1,"Price"), and cell A1 doesn't use the Stocks data type, then the error will occur.

This can also happen when a formula references a field from a data type, but the data type doesn't have any data for that particular field. For example, if you entered a formula like =A1.ExpenseRatio, or =FIELDVALUE(A1,"ExpenseRatio"), but the that particular stock doesn't have an expense ratio in the first place, the result is #FIELD!.

Solutions:

  • If Error Checking is enabled, click the Error floatie to the left of the cell, and choose the Ignore Error option.

  • Delete the formula.

There are two main reasons for this error:

  • If the image Excel is trying to fetch is missing some key information, it can't be displayed and will result in a #FIELD! error.

  • You are referencing an image from a data type that doesn't have an image field.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Introduction to linked data types in Excel

FIELDVALUE function

at

No comments:

Post a Comment