CODEWITHSUNDEEP
excelexcel-formula
PIPRON79
PIPRON79

Reputation: 141

Automatic cell increase

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?

Upvotes: 0

Views: 72

Answers (2)

JNevill
JNevill

Reputation: 50034

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

Upvotes: 1

dhalik
dhalik

Reputation: 465

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.

Upvotes: 0

Related Questions