Automatic Generate Numbers based on already assigned letters

Hi All,

I am using Excel 2013 and new to it. I need your expert advise. If answer of this question or similar is already replied, please provide the link to me. I could not find the answer in this forum.

I have numbers 1 to 9 and these numbers are assigned to letters A to Z. For example number 1 is assigned to, say, A, J, X or Number 7 is assigned to, say, B, E, M and so on.

Now, whenever I enter any alphabet or letter in any cell, number should show adjacent to that cell. For example, if I enter A in B1 then 1 should show in C1 or J in D2 then 1 should show in E2.

Thanks in anticipation.

Suunil

Guys,

Thanks for prompt response. I tried all formulas but nothing worked. May be I am doing something wrong as Excel is not my forte. Given below is the exact table for your reference.

1 2 3 4 5 6 7 8
A B C D E U O F
I K G M H V Z P
J R L T N W
Q S X
Y

I want if I type any alphabet anywhere in sheet, number should come to adjacent or below.

Originally Posted by Tony Valko

"Anywhere in the sheet" is very ambiguous.

If you really do mean "anywhere" then you'll need an event macro to do this.

Yes Tony, I want anywhere in the sheet. I don't mind if it is solved by macro or VB code (not sure whether both are same). I hope there is no problem with macro if I use it.

Originally Posted by bebo021999

Follow your approach, why not MATCH(B1,"abc....xyz",0)?
Anyway, OP required a 1-9 repeats.

bebo021999,

What is "OP"?

Originally Posted by bebo021999

Maybe this:

Attachment 432714

bebo021999, it worked but only on one cell or I have to copy in all cells where I want. How can I change "B1" to "A1:Z6600"?

Originally Posted by nflsales

=IF(B1="","",INDEX({1,2,3,4,5,8,3,5,1,1,2,3,4,5,7,8,1,2,2,4,6,6,6,3,1,7},SEARCH(B1,"abcdefghijklmnopqrstuvwxyz")))

Please Login or Register  to view this content.

Try this

nflsales, it worked but only on one cell or I have to copy in all cells where I want. How can I change "B1" to whole worksheet or atleast 1000 cells?

Originally Posted by bebo021999

So you want A1:Z6600 contain A-Z, and in, i.e, AA1:AZ6600 display 1-8?

Sorry if I am not clear with my question.

I have only one concern. If I type, say A or I, anywhere in the worksheet, their corresponding numbers should show next/below to them. Example, B29 = A, it should show 1 to B30 or C29.

B29 = A C29 = 1

or

B30 = 1

One more example, AC30 = R, it should show 2 to AC31 or AD30.

AC30 = R AD30 = 2

or

AC31 = 2

I hope this clarifies.

Originally Posted by Tony Valko

Unfortunately, I'm not much of a programmer so someone else will need to help you with a macro.

Good luck!

No problem Tony. Thanks for your help.

Originally Posted by Tony Valko

You may be better off posting this in the Excel Programming / VBA / Macros forum:

http://www.excelforum.com/excel-programming-vba-macros/

Thanks Tony. I will do it now.

Again thanks to everybody who tried to help me.