To generate a new PO template, you would have to type in the PO number, PO date, Supplier details and Item details each time you wanted to make a purchase. Although this might be okay if you only raise a few Purchase Orders each month, it would be tedious if you had to raise a Purchase Order several times each week. You would have to look into your Supplier Table each time you want to type in the details of a supplier and have to remember to write a new PO number for each new PO.
You would also need to refer to your Items Table to get the Item’s ID and unit cost. With so many values to update, data entry errors and inconsistencies, especially with the PO numbers, might become an issue. Fortunately, Excel has loads of features and functions that can help you make data entry faster and more efficient, and eliminate errors caused by typos and values mismatch.
The specific features and functions we are going to use in this guide are;
- Named Ranges
- Data Validation
- XLOOKUP and the MAX functions.
Using named ranges, we will create a Suppliers list and an Items List based on the Supplier Names and Item Description columns in our Supplier and Items database tables. We will use these lists as data validation sources for our Supplier Name and Item Description fields so that instead of typing out the names, you can select from a dropdown list. We will then use the XLOOKUP function to automatically fetch the Supplier Address, Supplier Email, Supplier Phone, and Supplier ID from the Supplier Database table based on the selected Supplier Name. We will also create a named range for the PO number column in the PO database and use the MAX function in the PO NO# field to get the last PO entry number and then add 1 to create a new entry number.
With this automation, you will only need to change the values of 4 fields. The Supplier Name, PO Date, Item Description, and Qty. PO Date and Qty are the only fields that might require manual typing.
Let us begin by creating the Named Ranges that will be used as data validation lists.
How to Define a Named Range
In Excel, a named range is a feature that allows you to assign a meaningful name to a specific cell or range of cells. Instead of referring to cells by their row and column coordinates (such as A1:A20), you can use a descriptive name that makes it easier to understand the purpose of the data. If the named range refers to a list of values, then it can be used as a data validation source for other cell ranges. In this tutorial, we will create three named ranges as mentioned earlier. A SupplierNames named range, ItemList named range and PO_Number named range.
Follow these steps to create the named ranges;
- Navigate to the ItemsDB worksheet and highlight the entire Item Description table column (excluding the header row) of the ItemsDB table.
- Navigate to the Formulas Tab and in the Defined Names group, click on the “Define Name” button. A pop-up will appear that lets you change the name and add some comments. In the name field, change the name to ItemList and a comment(optional) and click OK.
Note: When choosing a name for a named range, you cannot start the name with a number and you cannot have spaces between letters or words. If you have to use two or more words to give it more meaning, you should use an underscore between words (Item_List) or use the camel case (ItemList).
- Repeat the same for the Supplier Names column in the SupplierDB table and the PO Number column in the PODB table.
For a more in-depth guide on how to create the named ranges, please watch the video below.
Looking for project-driven supply chain management software?
Current SCM is the first of its kind – supply chain management software purpose-built to support the most complex procurement and materials management projects. With materials management and vendor document requirements uniquely integrated into the order, Current SCM provides a unified, collaborative platform to streamline the end-to-end process of project-driven procurement and materials management.
If you are engaged in any direct procurement, technical procurement, project procurement or third-party procurement, Current SCM will improve your procurement and materials management workflow. If you are engaged in all four, Current SCM will revolutionize the way you do business.
Contact our sales professionals at Current SCM today!