got a tough on and I am confused were to start

D

dlotz

Ok I have a table array that is preety in depth.
It works off of an agent # unique and sortable.

A vlookup formula popluates the agent name and
additional info based off the #.
Ok here is the problem, the agent # is assigned to an office office code.
So in other words the agent produciton is paid into that office code. well
from time to time we re-profile these guy under new office codes.
The code has an inception date, so on
JAN 1st agent 177721 might be under office code 401895 and
then aug he has moved to office code 401913. His producion is dated
as well.
Question, how can I stucture this were data intry for the date of
production will return the correct office code by the codes inception dates??

please ask quesitons if you do no understand mine.

lets say column b is the date of the production and col
c is the agent #, col d-g, are random data agent name ect.
and col. h is the office code.
how can incorpoate the date in the array to return the correct offic code
 
S

smartin

Ok I have a table array that is preety in depth.
It works off of an agent # unique and sortable.

A vlookup formula popluates the agent name and
additional info based off the #.
Ok here is the problem, the agent # is assigned to an office office code.
So in other words the agent produciton is paid into that office code. well
from time to time we re-profile these guy under new office codes.
The code has an inception date, so on
JAN 1st agent 177721 might be under office code 401895 and
then aug he has moved to office code 401913. His producion is dated
as well.
Question, how can I stucture this were data intry for the date of
production will return the correct office code by the codes inception dates??

please ask quesitons if you do no understand mine.

lets say column b is the date of the production and col
c is the agent #, col d-g, are random data agent name ect.
and col. h is the office code.
how can incorpoate the date in the array to return the correct offic code

I don't understand your layout, but this works.

With "master data" in Sheet2!A2:Cx (AgentID, Office, Inception Date) and
data entry in the current sheet (AgentID, transaction date, and
office=???) this array formula* in C2 and fill down will do it:

=LARGE(IF((A2=Sheet2!$A$2:$A$4)*(B2>=Sheet2!$C$2:$C$4),Sheet2!$B$2:$B$4),1)

*Commit array formula with Ctrl+Shift+Enter (not just Enter).
Adjust range references on Sheet2 to suit.

It is important that the inception date be sorted by inception date
ascending.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top