Solved

Number cells from 1 to 10 based on values being place in other cells

Posted on 2012-08-22
120 Views
To simplify this question for the sake of getting the help necessary, if I have 2 columns of 10 cells each in Excel and I wanted to place numbers from 1 to 10 in the right column based on when the columns to the left have text placed in them, how would I do that?

So let's say my cell range is A1:B10. If someone put any word, say "Mike", in cell A4, then cell B4 would populate with a 1. If they next put "Bill" in A10, then cell B10 would populate with a 2. The numbers in the right column would continue to increment until I have 10 cells numbered 1 through 10.

0
Question by:ambientsbs

LVL 18

Expert Comment

You would need a reference key for this.
The below file gives an example.
You can hide the columns I and J so that the reference table is not visible.

Match-name.xlsx

This example does it via number, however you can easily change it to the other way by changing the formula to:

=IF(A2="","Enter Name",(INDEX(\$I\$6:\$I\$15,MATCH(A2,\$J\$6:\$J\$15,0))))

You can also add the names as a drop down list.
0

Author Comment

Thanks for the help, but this doesn't accomplish what I'm looking to do. I don't want the number to be associated with a certain name, but rather the order in which I typed the name in. So if the first name I typed in was "John" a number 1 would appear in the column next to John. If I typed in "Steve" a few cells below it, a number 2 would appear in the column next to Steve. The names will be completely random and there's no telling out of my 10 cells which one I will enter a name into first, second, etc.
0

LVL 18

Expert Comment

OK that makes it harder and would probably require VBScript.

What is the purpose of doing this however? Knowing that might make it easier to come up with a solution or an alternative.

Also is the range you are entering names in finite and contiguous, or are you adding names in random cells in the entire workbook?
0

LVL 8

Accepted Solution

Can you try this file if it is what you needed? Type values in column A.
sequential.xlsm
0

LVL 24

Expert Comment

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0