0

I had been trying out Define Name in Excel. I defined a name for a cell in which I would put a text or date value or I may leave it blank.

But when I use this defined name in another cell, the second cell shows a numeric value except when there is actual text in the first cell.

Value in first cell | Value in second cell
<blank>             | 0
Some text           | Some text
Date                | Numeric representation of date

Samples displaying the issue with "defined name" feature

How do I define a name for a column such that the value in first cell (whatever that may be) is displayed exactly as it is in the second cell? Am I missing something here?

Edit:
Upon suggestion of mcalex, I repeated the experiment with format applied (Text and Date as appropriate).

Repeated experiment results

The results are closer to what I need, but still not good enough. For example, even though the format applied works for dates, the blank text is still shown as a 0. Short of resorting to a formula like (pseudocode) if 0 then show blank else show text, what can be done to fix this?

Short Answer:
Apply Text format on source cell (first cell) and the following Custom format on destination cells (second cell):

#,##0;-#,##0;

For dates, apply Date format on both source cell and destination cells. Please see the answer of mcalex for more details.

3

You are getting the value from the first cell - what you want is the format. The results are similar to what you would get if you were copying the cells - as normal cells rather than named ranges - to cells that were formatted as 'General'.

When Excel sees the equals formula used on a cell, it includes the formatting. This doesn't happen when using equals on a named range.

You may need to resort to some VBA that copies formats to achieve the outcome you are looking for.

Edit:

To have a copied/'equalled' cell not display 0 when the source cell is blank, you can use a Custom Format. Select the cell with the erroneous 0 and change the format to Custom, and set the Type to:

#,##0;-#,##0;

This will set a comma separator, no decimals for numbers, will include a minus sign for negative numbers and set 0 values to blank. This is probably not ideal, but should display correctly. If you want to see decimals, set it to:

#,##0.##;-#,##0.##;
| improve this answer | |
  • Please look at the edit to my question. Thanks. – ADTC Apr 1 '14 at 5:32
  • The custom format #,##0;-#,##0; works great. Thanks! – ADTC Apr 1 '14 at 6:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.