inventory code?

Good day all,
I would like to create a code in excel for inventory purposes to where I could have letters in a cell represent numbers ie: a=0, b=1, c=2, d=3, e=4, f=5, g=6, h=7, i=8, j=9. If I type "bbb" in a cell it would represent $1.11. I would then multiply that cell by a quantity cell to get the total inventory amount for a particular item. Thanks in advance!

ITEM = widget CODE=bc RESULT=.12 QUANTITY=100 TOTAL=$12.00

here's one way

Originally Posted by decojib

Thanks Dave!!....How can I make the values go to $99,999.99? Also I have about 20 different sheets to my inventory "book". Can I reference back to this formula on 1 sheet or do I need to copy this formula on each sheet? Thanks again!!

hmmm...
thats another can of worms,
I vill work on it

I have probably lost focus on the original question, but here is a code you can try out, maybe somebody will be able to work from it, as I was only able to get it to work with helper columns

place the letters in A1
then run the code

letters must be in CAPS or you will get the wrong value

Please Login or Register  to view this content.

Originally Posted by decojib

Sorry Dave.....That code is a little over my head as far as how to implement it.

Sorry about that, here's a better solution, changing the macro into a function

http://www.freewebs.com/davesexcel/L...bers%20UDF.xls

Originally Posted by decojib

Hey Dave,
I took a look at your solution and I must say it is impressive (especially to a limited skilled mind such as mine). Is there any way to adapt what you have done so that I could actually "assign" the values of the letters? For example: "R"=0, "Z"=1, "A"=2..."T"=9 ? Thanks again!

p.s. If I am asking too much, just say so, and I will let it go!

Well I have found a way, here ...
http://groups.google.ca/group/micros...a92fc987e734c6
It is a UDF (User Defined Function)
I just tested it and seems to do what you want
Change the values in the code to your requirements

Originally Posted by decojib

Dave,
That is it!!! Thanks so much for finding that for me! One last thing and I will leave you alone. Like the Lady in that thread, I do not know how to implement a "UDF". Could you send me a file or show me how? Thanks again!

Sure here is an attached example on sheet 2 shows how to enter the formula
In your worksheet enter the keys
Alt F11
this takes you to VBA window
at the top menu select
Insert
Module
Copy and paste this code

Please Login or Register  to view this content.

In A1 enter any combination of the letters(in CAPS) from the code
in B1 enter =MyNum(A1)