beans19,
Alright, I think I know what you're getting at. You want the Managers and Divisions to populate automatically based on Sales# and Date of Sale. This one is tricker because there are potentially multiple Managers and Divisions for a specific Sales# and Date of Sale.
Attached is version 3.
In the Sales tab, cell G2 (Managers) and copied down is this formula:
In the Sales tab, cell H2 (Divisions) and copied down, is this formula:
Note that the two formulas are practically identical except for what the Index location is. The Mangers formula indexes Allocation!$E$1:$E$12 and the Divions formula indexes Allocation!$F$1:$F$12.
Its also important to note that, in the formula bar, the formula is surrounded in curly brackets {=formula}. This is because the formula is an array formula. To enter a formula as an array formula, it must be entered with Ctrl+Shift+Enter (CSE). Doing so will automatically put the curly braces around the formula (don't try to put them in manually).
In the Sales tab, row 16, you'll notice that there is no Manager or Division. This is because that row is a 4th entry for Sales# 111 and Date of Sale 3/1/2011, but there are only three managers and divisions for that combination, so the formula doesn't return anything.
Is something like that what you're looking for?
Bookmarks