Auto pick up next number

[springran]

Issue the next number

I have set up data validation in column A ( only two on the list A&B).

I have the below table in 2nd tab ( as data sources)

Output in 1st tab in column B
I want a formula to search in 2nd tab ( Number 1 & Number 2 ), if column A( Team) select A I want to return reference number start with A. The formular is always look for the next number to issue it out.

Thank you

 

[Eric W]

Maybe:

Book2
	A	B
1	Team	Reference number
2	A	A95-0001
3	B	B50-0002
4	B	B50-0003
5	B	B50-0004
6	A	A95-0005
7
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=IF(A2="","",INDEX(Sheet2!$B$2:$C$8,ROWS(B$2:B2),MATCH(A2,{"A","B"},0)))

Cells with Data Validation
Cell	Allow	Criteria
A2:A7	List	A,B

My first stab was actually to return the first unused value in the each column, but it didn't match your expected results. If that's of interest, try:

=IF(A2="","",INDEX(Sheet2!$B$2:$C$8,COUNTIF(A$2:A2,A2),MATCH(A2,{"A","B"},0)))

 

[springran]

Maybe:

Book2
	A	B
1	Team	Reference number
2	A	A95-0001
3	B	B50-0002
4	B	B50-0003
5	B	B50-0004
6	A	A95-0005
7
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=IF(A2="","",INDEX(Sheet2!$B$2:$C$8,ROWS(B$2:B2),MATCH(A2,{"A","B"},0)))

Cells with Data Validation
Cell	Allow	Criteria
A2:A7	List	A,B

My first stab was actually to return the first unused value in the each column, but it didn't match your expected results. If that's of interest, try:

=IF(A2="","",INDEX(Sheet2!$B$2:$C$8,COUNTIF(A$2:A2,A2),MATCH(A2,{"A","B"},0)))

Thank you Eric