Determining Next sequence of number if there are 7 sequences each starting with a letter

[willow1985]

Hello,

I am looking for a formula that can determine the next sequence of a number from another sheet if there are 7 sequences each starting with a letter. For example:

A001
B001
C001
D001
E001
F001
G001

I know that formula: =MAX('Sheet1'!A$1:A$1000000)+1 works to find the next sequence of a number but I have 7 number sequences.
I was thinking that when the user enters the correct letter (aka: G) in a certain cell that, maybe was a way to do an index/match and IF function to find the next sequence in the cells that match "G".

Maybe someone will be able to help.

Thank you

Carla

 

[willow1985]

Thank you so much Peter, this formula is much better. Turned out the original gave me a VALUE# error when I entered "B" in the reference cell. I am assuming it is because B is the only number sequence with 1 letter and 4 numbers where the other 6 letters only have 3 numbers.

Thank you very much for this formula and it is faster!

Cheers,

Carla

 

[Peter_SSs]

Thank you so much Peter, this formula is much better. Turned out the original gave me a VALUE# error when I entered "B" in the reference cell. I am assuming it is because B is the only number sequence with 1 letter and 4 numbers where the other 6 letters only have 3 numbers.

Thank you very much for this formula and it is faster!

Cheers,

Carla

You're very welcome. Thanks for the follow-up.