How to Create a Custom Text Translation(?)

[Gary_at_TMI]

I'm really not sure what/how to ask this question, but I have not been able to find anything that is similar to my problem. If this question has already been posted and answered, a link to that post would be perfect.

Basically, I am wondering how I can enter in a short code into a cell, but have it displayed as "Human Friendly" text.

i.e.
I would type into a cell COGS, and when I press enter, or move to a different cell, "Cost of Goods Sold" would be displayed in that cell. The value of the code would not be changed, is would be display as the full text version when not editing the cell,

I have 13 codes currently that I would like displayed more human friendly:

BANK = Bank
CCARD = Credit Card
COGS = Cost of Goods Sold
EQUITY = Equity
EXEXP = Other Expense
EXINC = Other Income
EXP = Expense
FIXASSET = Fixed Asset
INC = Income
LTLIAB = Long Term Liability
OASSET = Other Asset
OCASSET = Other Current Asset
OCLIAB = Other Current Liability

I would also like to be able to easily expand/modify the list of valid codes and displayed text if possible.

Any help or suggestions would be greatly appreciated.

TIA

 

[GlennUK]

Yoink! Code saved away for future use!

ha ha, thank you!!!!

 

[Joseph.Marro]

GlennUK,

That is a nice little trick. I had not thought to use that approch before. I will be saving this bit of code.

Thank you,

Joseph Marro

 

[Gary_at_TMI]

Thanks Ruddles for the reply. Yes I am familiar with VLOOKUP(), (this is the way I have it now) and use it judiciously. But I am hoping for a method of keeping the data/display to a single cell. Like when you enter "12/31/11" into a cell, the display will be "December 31, 2011"

There are some amazing approaches being posted here to producing the results I want. It is going to take me a bit to get my head around "worksheet code" as I have never seen this before.

52 yrs on Saturday, and I still get to learn new stuff!!

 

[Gary_at_TMI]

Thanks Sektor for the code. I have not worked with VB in about 15+years, so please bare with me..

I tried to enter your code into Tools>Macros, but the dialog is disabled with only the Cancel button active. Is this the correct location to be entering the code? If so, how do I activate the Macos dialog window?

Thank you as well GlenUK. Do I enter your code in the Macros dialog as well? Am not familiar with the worksheet code area.

Both your solutions are brilliant, I just don't know how to implement them.

Note: I am working in Excel 2008 Mac, if that makes a difference.

TIA again.

 

[GlennUK]

Am I right in thinking that VBA is not supported for Excel 2008 Mac? If so, you are out of luck with my solution. Although I think I could write an XLM old style macro to do the job, but it wouldn't be automatic ( there is no event triggering if there's no VBA ).

 

[Gary_at_TMI]

Thanks Glenn. Yeah, I just found the help video that told me that I have to use Excel 2004. The narrator was very apologetic about there being no VB support until the next version of Excel though.... that made me feel much better....

I do have 2004 installed, so I will give your's and Sektor's code a try there, and see how far I get.

Thanks very much for the help.

 

[Ruddles]

52 yrs on Saturday, and I still get to learn new stuff!!

Young whippersnapper!

 

[Biz]

Hi GlennUK,

Nice approach mate. It is ezy to bamboozle auditors lol

Biz