Data validation with if condition

[springran]

Hi All, Plese help

I have set up a drop down list in Column A ( only two team A & B) and I have a reference number list within the other tab. When people select "A" team I want to automaticlly add " A" in from of number and populate the number in column B.


Result should like below

 

[Jeffrey Mahoney]

=IF(A1<>"",A1&"12-3456","")
Where are you getting your number? Is it always the same related to the team? If so then create a table with TEAM and NUMBER and do a XLOOKUP. If not , the is it the same row as B2?

 

[springran]

=IF(A1<>"",A1&"12-3456","")
Where are you getting your number? Is it always the same related to the team? If so then create a table with TEAM and NUMBER and do a XLOOKUP. If not , the is it the same row as B2?

Hi Jeffrey

Thank you for your help

I used your formular and got the below results.

Column D ( reference number should be in other tab).

The results want to see as row 7-9. When people pick "P" the formular in column B automatuictly pick up next refereence number in reference number tab.

 

[springran]

Can anyone help me Please ?????