I have an inventory-type spreadsheet where i wish to create unique identifiers for each stock item.
The source of these items names is on a "DropDownLists" tab in the table column "Item Name". The Unique identifiers will be generated in the column "Internal Code" on the same tab via a formula and it is hoped this self replicates as items are added to the "Item Name" column as does the rest of my formulas in the table.
On the tab "Item Lists" the "Item Names" are replicated via formula, in a table that also tabulates "Vendors" etc. I would like to create an "Internal Code" on the "DropDownLists" tab that looks at the "Item Name" on the "DropDownLists" tab, checks where this is on the "Item Lists" tab, uses its corresponding "Vendor" name on the "Item Lists" tab (using 3 letters, all capitals), then adds a unique number (using 3 numbers). If there is only two letters in the Vendor then the blank can be a Zero. eg HP0-001, PEA-001.
I suspect a combination of Index and match is needed with text and countif functions but cannot seem to get it to work. Any help would be greatly appreciated. I have uploaded a copy of my spreadsheet so far.