Ensure the Sales worksheet is active. Enter a function in cell B8 to create a custom transaction number. The transaction number should be comprised of the item number listed in cell C8 combined with the quantity in cell D8 and the first initial of the payment type in cell E1. Use Auto Fill to copy the function down, completing the data in column B. Enter a nested function in cell G8 that displays the word Flag if the Payment Type is Credit and the Amount is greater than or equal to $4000. Otherwise, the function will display a blank cell. Use Auto Fill to copy the function down, completing the data in column G.
Create a data validation list in cell D5 that displays Quantity, Payment Type, and Amount.
Type the Trans# 30038C in cell B5, and select Quantity from the validation list in cell D5.
Enter a nested lookup function in cell F5 that evaluates the Trans # in cell B5 as well as the Category in cell D5, and returns the results based on the data in the range C8:F32.