I'm sure this has been asked many times, but I can't seem to turn up an applicable solution...
I have a workbook with tables on separate worksheets:
On a third sheet, which will have a table of transactions, I want to create new records by first picking a CompanyID (again, via validation list tied to the "CompanyID" field in the "Companies" table <-- this I can do already) and then associate the transaction with that Company's Sales Rep who initiated it (<-- this is where I need some help). That is, I want to pick a SRepID from a validation list that is sourced from the SRepID field on the SalesReps table. But, I want that validation pick list to be filtered to only show the Sales Reps who work for the chosen Company. For example, if the transaction record is associated with the 123-AcmeInc company, I want the pick list domain for that table's SRepID field to be constrained to only list AJones01, BSmith01, JDavis01, BSmith02, and MThomas01.
To enable that conditional (filtered, based on CompanyID) validation list for transaction records, I think there are at least 2 obstacles. First, the sources are "living" tables, with new Companies and new Sales Reps being added daily to their respective tables. Secondly, the SalesReps table is not sorted, so a given company's Sales Reps' records are not grouped into a contiguous range. Thus, I presume, deriving the validation list source will involved using array formulas, named ranges with the INDIRECT function, and - possibly - MATCH and OFFSET techniques, but I am unable to solve this myself.
Thanks in advance!
SDL
I have a workbook with tables on separate worksheets:
- Sheet named "CompanyData" has a (formally designated) table named "Companies" that includes a data field named "CompanyID". There is one record per company, each with a unique "CompanyID".
- Sheet named "SalesRepData" has a (formal) table named "SalesReps" that includes data fields named "CompanyID" and "SRepID". For each Sales Rep's record in this table, I pick the his/her "CompanyID" (using Excel's built-in data validation tool, referenced to a list whose source is the "CompanyID" field in the "Companies" table <-- this I can do already), and then create and assign a unique "SRepID". This table is not sorted, and there can be multiple Sales Reps associated with any given Company. For example, there may be 5 records (SRepIDs: AJones01, BSmith01, JDavis01, BSmith02, and MThomas01) that collectively document the Sales Reps who all work for a particular company (CompanyID: 123-AcmeInc)
On a third sheet, which will have a table of transactions, I want to create new records by first picking a CompanyID (again, via validation list tied to the "CompanyID" field in the "Companies" table <-- this I can do already) and then associate the transaction with that Company's Sales Rep who initiated it (<-- this is where I need some help). That is, I want to pick a SRepID from a validation list that is sourced from the SRepID field on the SalesReps table. But, I want that validation pick list to be filtered to only show the Sales Reps who work for the chosen Company. For example, if the transaction record is associated with the 123-AcmeInc company, I want the pick list domain for that table's SRepID field to be constrained to only list AJones01, BSmith01, JDavis01, BSmith02, and MThomas01.
To enable that conditional (filtered, based on CompanyID) validation list for transaction records, I think there are at least 2 obstacles. First, the sources are "living" tables, with new Companies and new Sales Reps being added daily to their respective tables. Secondly, the SalesReps table is not sorted, so a given company's Sales Reps' records are not grouped into a contiguous range. Thus, I presume, deriving the validation list source will involved using array formulas, named ranges with the INDIRECT function, and - possibly - MATCH and OFFSET techniques, but I am unable to solve this myself.
Thanks in advance!
SDL
