CODEWITHSUNDEEP
excel
AmethystStarling
AmethystStarling

Reputation: 25

Creating a cell that updates to new value

I am wondering if there is any way to create a cell that automatically updates to the next in a series in Excel.

If Column 2 contains any of these:

2A
2B
2C

Then the cell would say use 2D next. And then when that column shows 2D, then use 2E, and so on. I have tried using an if function, but I can't seem to get it to work. I plan on having the list of available entries on a separate worksheet that the data will draw from.

Thank you in advanced!

Upvotes: 2

Views: 66

Answers (1)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

You could try this:

Have the text "2A" in cell A1.

In cell A2, paste this formula:

= LEFT(A$1,LEN(A$1)-1)&CHAR(CODE(RIGHT(A$1,1))+ROWS(A$1:A2)-1)

And drag down as necessary.

EDIT

Based on your comment, you can modify cell A2 to this:

= IF(RIGHT(A1,1)="Z",(LEFT(A1,LEN(A1)-1)+1)&"A",
  LEFT(A1,LEN(A1)-1)&CHAR(CODE(RIGHT(A1,1))+1)

Upvotes: 1

Related Questions