Letter represents number

[sparky3883]

Hi All

I was wondering if someone could please help me.

I have a sheet in Excel that has 36 columns and 34 rows.
Coulmn A (from 1-33) will contain names. Columns B and C will be left
empty. The other remaining columns D - AJ (also from 1 - 33) will
contain a letter inputted by the user.

A34 will contain the word total.

What I am hoping to do is, each letter inputted anywhere in the
remaining columns will be represented by a number, and i would like the
total of represented numbers to be shown in the total row (Row34)

Here is a short lists of some of the letters i would represented

T = 1
s = 1
L = 4
K = 7

So, for example, if i insert the letter K into H8 - the number 7 will
be shown in H34.
If i inserted the letter T into M11, the letter S into M21 and the
letter K into M23 the total would be shown as 9 ( the numbers are to be
added together)

The letters will always be shown in the main part of the sheet, it's
just the numbers that i would like to be added together and shown in
the appropriate column. No column will ever have a fixed letter in it.

I have attached my Blank sheet so it may make it a bit easier to
understand.

I realise that I am asking alot, but I would be very grateful if
someone could please set me off in the right direction.

Many thanks in advance

Mark

Attachment filename: blank.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=474168

 

[Guest]

Hi Mark,

not very elegant, but it works for me.

Enter in column A from say row 35 downwards the list of
your letters, and in column B the values for the letters.

Let's assume we look at column D now.
Enter in D35:
=(COUNTIF(D$1$33,$A35)*$B35)
This formula you can copy down and to the right.

In the cells D34 you may make a Sum of all the rows below.

The rows from below 34 you may hide.

Would that help?

Best regards

Wolf

 

[Wolf]

Hi Mark,

if you do not want to have this "side calculation" on the
the sames sheet, you may also put it on a seperate sheet
and hide this sheet, so that your users will not see it.

Wolf