#VALUE! Error When Concatenating Cells

만료된 KB 콘텐츠 고지 사항

이 문서는 Microsoft에서 더 이상 지원하지 않는 제품에 대해 작성되었습니다. 따라서 이 문서는 “있는 그대로" 제공되며 더 이상 업데이트되지 않습니다.

Symptoms


In Microsoft Excel, when you concatenate cells, you receive a #VALUE! error if the Transition Formula Evaluation (TFE) option in version 5.0 or the Alternate Expression Evaluation (AEE) option in version 4.0 is enabled and one of the concatenated cells is blank or contains a number.


This behavior also occurs with the INDIRECT function when you use concatenated cells. For example, the formula


=INDIRECT("A"&B1)


returns A6 if B1 has a value of six and TFE or AEE is not selected. The formula returns the #VALUE! error if TFE or AEE is selected.

Workaround

To avoid receiving the #VALUE! error, do either of the following.

Disable the TFE or AEE option.

NOTE: Make a backup copy of your worksheet. (If you disable alternate expression evaluation, the values calculated on your worksheet may change. If you have a backup copy of your worksheet, you can compare the earlier version of your worksheet with the updated version and make sure that your calculations are correct.)

Microsoft Excel 98 Macintosh Edition


To disable the TFE or AEE option, follow these steps:

  1. On the Tools Menu, click Preferences and click the Transition tab.
  2. Under Sheet Options, clear the Transition Formula Evaluation check
box and click OK.

Microsoft Excel Version 5.0


To disable the TFE or AEE option, follow these steps:

  1. On the Tools menu, click Options and click the Transition tab.
  2. Under Sheet Options, clear the Transition Formula Evaluation check box and click OK.

Microsoft Excel Version 4.0


To disable the TFE or AEE option, follow these steps:

  1. On the Options menu, click Calculation.
  2. Under Sheet Options, clear the Alternate Expression Evaluation check box and click OK.
-OR-

Use the TEXT Function

To concatenate cells while TFE or AEE is enabled, use the TEXT function in any concatenation formulas with cells that contain numeric values or are blank, as in the following example:


=A1&TEXT(A2,IF(A2="","","0"))


The above formula combines the contents of cell A1 with the contents of cell A2, and uses the TEXT function to ensure that the contents of cell A2 are interpreted as text. The IF statement ensures that the cell is formatted correctly based on whether cell A2 is blank or contains a number.

More Information

If Transition Formula Evaluation or Alternate Expression Evaluation is selected, the worksheet is calculated based on the Lotus 1-2-3 rules for evaluating expressions instead of the Microsoft Excel rules. This option is automatically selected when you open a Lotus 1-2-3 worksheet in Microsoft Excel.

References

"User's Guide 1," version 4.0, page 57


"User's Guide 2," version 4.0, page 237


"Function Reference," version 4.0, page 431
속성

문서 ID: 94202 - 마지막 검토: 2005. 8. 15. - 수정: 1

피드백