Forum Discussion
Formula to autofill a PO #
I have a workbook with 2 sheets.
Sheet 1, column A is where the formula needs to go - based off the information in the sheet called Customer list.
Example: Customer List - Column A is customer name - Column B is the PO# Starting String. This is specific to the customer.
I want to open Sheet 1 and type a customer name in B2 and a PO number automatically fill in based off the criteria mentioned above. So if my Customer name is Asset Living (on sheet: Customer List - A2) and I type Asset Living in Sheet 1, B2, the the PO# that populates in Sheet 1 A2 should be AL(from Customer List, B2) followed by random numbers (preferrably 3 digits).
9 Replies
=LET(Code,XLOOKUP(B2,'Customer List'!$A$2:$A$17,'Customer List'!$B$2:$B$17,"New Customer"), Nbr,COUNTIF($B$1:$B1,B2)+1, PONbr,Code&TEXT(Nbr,"000"), PONbr )Rather than "random", I'd recommend sequential since verifying that a number hasn't already been used would be overly complicated.
- I'm getting an error #NAME?
