Displaying a value in cell based on different value entered

[TomWiegand]

Hi all,

My name is Tom, and I'm a recent b-school IT grad. I'd love to say I'm nearly an expert with Excel, but there's something I swear I learned back in high school that I can't remember to save my life.

I would like to format cells to display a particular value when I enter something different. It's hard to explain it as a general problem, so more specifically:

I have a table with a list of characters in a game I play, and one property included is gender. Instead of displaying "Male" or "Female", I would like it to see the appropriate gender symbols (♂ and ♀) when I enter the respective gender. I want the symbol to display while I only have to type in the gender word.

Any tips?

[FDibbins]

why not just use ALT 11 (male) and ALT 12 (female)?

[daffodil11]

This little bit of code does it. If you type male or female or any capitalization variation thereof, it will autochange the cell to the Mars or Venus symbol.

It goes directly on the tab's source. (Right click tab, view source, paste there. Adjust range as needed.)
You'll just need to make sure you use a Font with unicode capabilities on those cells.

Lucinda Sans Unicode or Segoe UI Symbol both work.

Please Login or Register  to view this content.

[TomWiegand]

why not just use ALT 11 (male) and ALT 12 (female)?

My laptop doesn't seem to allow it -- should it work without a number pad?

It's also a personal preference. I'd rather be able to type in a M or F, have AutoFill complete the value and then display the symbol, than use the symbol itself, as long as that's feasible.

This little bit of code does it. If you type male or female or any capitalization variation thereof, it will autochange the cell to the Mars or Venus symbol.

It goes directly on the tab's source. (Right click tab, view source, paste there. Adjust range as needed.)
You'll just need to make sure you use a Font with unicode capabilities on those cells.

Lucinda Sans Unicode or Segoe UI Symbol both work.

Please Login or Register  to view this content.

Is there a way to do this without changing the values of the cell to the symbols? That's what I originally asked for. If not, it isn't changing the values that have already been entered nor does it do anything when I enter a new value in the target cells, so I may be doing something wrong here.

[FDibbins]

This thread has been marked Solved, but it seems that your question is not yet resolved, so I will remove that setting for you

[FDibbins]

My laptop doesn't seem to allow it -- should it work without a number pad?

Im not sure, I dont use a laptop

Im not sure if there is any way - other than VBA, not even sure that will be able to do it - to have the cell contain 1 thing, but to show another

[daffodil11]

To literally have it do one thing while still containing the other, you'd have to develop your own font similar to Webdings or Wingdings.
This way the display output could be custom tailored, while retaining its described value.

[TomWiegand]

Dang, I thought I remembered being able to do something like this before (and it would've been back in the day of Office 2007 or possibly even 03). I'll keep tinkering around, but thanks.

I never set the thread as solved, but I'll make sure to be more vigilant with any of my future threads.

[FlameRetired]

Have you tried AutoCorrect? I just managed to store M and F as ♂ and ♀. Was that how you did it before?

In Excel 2013 it's in File / Options / Proofing.

[daffodil11]

That's so dang creative.