I'm creating a Timesheet where Customer and Projects are entered by the users via data validation (thanks previously to VoG) and from the text entries I want to lookup codes for entry into the billing system. Customer is no problem - standard vlookup table. Projects is another story. In order to generate a hierarchical data validation, i.e only select projects available to that customer, I have the following lookup tables:
1 - Customer, Customer Code - simple columnar table 2 - Projects by Customer - Customer along the top, with each project below the customer. At the right hand side of this table is the Project Code.
When looking up the project code from the customer name, I need to identify the column (customer) and then the row (project) and from the row identifies the code, which is appended to the Customer/Project code to make a unique ref.
I thought Match would do it but have had no joy. I hope my explanation is clear and welcome any advice.
I didn't completely follow your example but it did set me off down a different track - if I use Cust names as the range names for each column of Projects, making sure that they're spelt correctly, then I can use Indirect(CustName) as the array to match the Project Name against and this seems to be working!
Many thanks for your help.
This thread is now locked and can not be replied to.