Automatic cell increase

Question

I am building a spreadsheet and would like a way to automatically increase the cells in column A dependent on whether a user name is entered into Column B. For example:

If A1 = R-0001 and B1 = Sarah then when a new user enters their name in Column B I would like A2 = R-0002 etc etc

Can anyone help with a macro or alternative that will do this?

Answer 1

This can be done with a cell formula. In A1 enter =If(B1<>"", "R-" & Text(Row(), "0000"), "") and copy that down however many rows you believe you may have names for.

A quick break down of what this formula does:

1. First it test if B1 has a value (when you copy this down it will test B2, B3, B4, etc..
2. If it has a value then it concatenates "R-" with Row number
3. The Text(Row(), "0000") bit insures that your Row number that is returned is 4 digits. Cell A1 will produce 0001 where Cell A532 will produce 0532
4. If B1 is empty than A1 will also be empty

Answer 2

If you want to do this in a formulaic way, you can set the contents of A1 to be

"R-"&TEXT(ROW(),"0000") 

which would make the value R-(rownumber), with a fixed with of at least 4. It is probably the most simplistic way of doing this.