Formula for a letter typed

**manbugle** · 03-23-2007, 01:46 PM

I am trying to make a puzzle and use excel to verify I have done it correctly.
What I want to do is when I type a letter in a specific cell have it put a number value in another cell automatically. For instance, if I type the letter "a" in cell A1 I would like the number 60 to populate automatically in cell B1. I want to give each letter in the alphabet a numeric value so I can type a message in the A column and then auto sum the results of the numbers in the B column. Any help would be greatly appreciated.

**VBA Noob** · 03-23-2007, 02:33 PM

Hi,

The code function should help

a small "a" = 97.

So you could enter the below to return 60

=CODE(A1)-37

VBA Noob

**manbugle** · 03-24-2007, 06:51 AM

I see that if I put the =CODE(A1)-37 in B1 and then type an "a" in A1, B1 populates with the 60 like I want, but if I type another value in A1, say the letter "d", which for my puzzle = 15 it calculates a 61. Does this make sense what I am trying to do? I want to experiment with my message in column A and see how it affects the results in column B.

**VBA Noob** · 03-24-2007, 07:01 AM

May I suggest you attach a file with the letter and number for your puzzle.

or maybe just set up your table and do a vlookup

VBA Noob

**teelim** · 03-24-2007, 12:18 PM

Is this what you are trying to do?

hope it helps

**manbugle** · 03-25-2007, 12:27 PM

Thanks for the replies, people. I am attaching my file for you to see. What I am doing is making a puzzle using a polybius square. The numbers given spell a message that hint at solving the puzzle. The puzzle is actually solved by using the numbers given. I need to make a message such that it gives a specific result so I will have to play with the wording. Puzzle is converting the numbers in column "A" to a fraction with the lowest common denominator of 60, then adding them all up and solving for a specific number which is a coordinate to a gps treasure hunt. The top row in my sheet is the values for each letter. The message reads down column "B". It would also be nice to have column "A" filled in for whatever I typed in "B" as well.
Thanks in advance for looking at this.

**VBA Noob** · 03-25-2007, 03:30 PM

This might help

VBA Noob

**manbugle** · 03-26-2007, 02:40 AM

Holy cow that looks great! I am going to have to look at how you did that. Thanks a bunch!!

**manbugle** · 03-26-2007, 10:35 AM

One question though, when I type the letter i it states to use lowercase only but that is what I typed. If I look at the drop down for the the cell, the I is the only one that is uppercase and it wouldn't bother me just to click it in the drop down but the number in column C isn't pulling over either. There is just a #VALUE! in that cell. Is this because I and J exist in the same cell in the polybius?