Changing letter entries into numbers

[RADICAL_THOUGHT]

hello,

the letters N,D,L,E,P are shifts, I want to be able to type these letters in the blank cells and get numbers in place of the letters.

so when I type in 'N' I want it to show in the cell as '12' with the same background colour.

TIA

 

[Peter_SSs]

Welcome to the MrExcel board!

so when I type in 'N' I want it to show in the cell as '12' ..

What about the other letters/numbers?

... with the same background colour.

How is the background colour applied now?

 

[RADICAL_THOUGHT]

CURRENTLY I HAVE CONDITIONALLY FORMATTED IT SO THAT WHEN I TYPE 'N' IT COMES UP AS 'N' BUT WITH THE BACKGROUND COLOUR.

'N' = 12
'E' = 9
'D' = 12
'L' = 9
'P' = 9

 

[RADICAL_THOUGHT]

SO IVE ATTACHED WHAT I WANT TO TYPE AND BELOW WHAT I WOULD LIKE TO SEE.

THANKYOU

 

[Peter_SSs]

OK, see if this small example works sufficiently as you want. If so you can apply it to a larger range and for other letters. I have just done cell G4 and only for the letter "D"

24 08 30.xlsm
	F	G	H
3
4		12
5
Letter-Number

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:N4	Expression	=G4="D"	text	NO

In the Conditional Formatting set-up as well as the fill colour I have used this on the 'Number' tab.

.. and looking at my sheet when G4 is selected you can still see "D" in the formula bar

 

[RADICAL_THOUGHT]

THAT WORKED THANKYOU

 

[RADICAL_THOUGHT]

IS THERE A WAY TO MAKE IT COUNT AS A NUMBER TOWARDS THE TOTAL AT ALL

 

[felixstraube]

You could use

=SUM(XLOOKUP(C3:AG3, {"N";"E";"D";"L";"P"}, {12;9;12;9;9},0,0))

Or adjust the range as necessary.

 

[RADICAL_THOUGHT]

THANKYOU VERY MUCH FOR THE HELP GUYS

 

[Peter_SSs]

You're welcome. Glad we could help.

BTW, for the future please note #14 of the Forum Rules which asks you not to post in all upper case.