Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hate to double post but have not really got a good answer, more lo
I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hate to double post but have not really got a good answer, more lo
Try this array formula** :
G1 = 177721 H1 = 6-1-2009 =INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Returns: 401912 -- Biff Microsoft Excel MVP " wrote in message ... I have quesiton that has has not really been answered. I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hate to double post but have not really got a good answer, more lo
You **might** be able to use this normally entered version **if** the office
code is always a numeric value. =SUMPRODUCT(--(A1:A10=G1),--(E1:E10=H1),D1:D10) The other array version is more flexible as it handles any data type. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this array formula** : G1 = 177721 H1 = 6-1-2009 =INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Returns: 401912 -- Biff Microsoft Excel MVP " wrote in message ... I have quesiton that has has not really been answered. I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hate to double post but have not really got a good answer, mor
The H1 column dates are random. I tried using a formula very
similar to this and would not reconize random dates. I have been trying to do some type of if( < less than function, but cannot get it to jive. Will this formula MATCH the date to date and return a values or will will it work like if the date is the office code date it will produce the correct response, please advsie ie what would happen is the date were 2-28-2008 would it produce the code in associated with that random date in time 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2008 "T. Valko" wrote: Try this array formula** : G1 = 177721 H1 = 6-1-2009 =INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Returns: 401912 -- Biff Microsoft Excel MVP " wrote in message ... I have quesiton that has has not really been answered. I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hate to double post but have not really got a good answer, mor
Hi,
You problem is going to be that you really aren't looking at a specific date but a range of dates, so you are going to need to create another table which associates dates and agent #. This will only work if this isn't completely random. Suppose you set up a table with Agent #'s in column L, Start Date in M and End Date in N and Office Code in O =SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10) Where the agent # is in A1 and the date in B1 -- If this helps, please click the Yes button Cheers, Shane Devenshire " wrote: The H1 column dates are random. I tried using a formula very similar to this and would not reconize random dates. I have been trying to do some type of if( < less than function, but cannot get it to jive. Will this formula MATCH the date to date and return a values or will will it work like if the date is the office code date it will produce the correct response, please advsie ie what would happen is the date were 2-28-2008 would it produce the code in associated with that random date in time 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2008 "T. Valko" wrote: Try this array formula** : G1 = 177721 H1 = 6-1-2009 =INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Returns: 401912 -- Biff Microsoft Excel MVP " wrote in message ... I have quesiton that has has not really been answered. I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hate to double post but have not really got a good answer, mor
so would I have a two entry on each agent #, and sort by agent # or does sorting even matter ie agent # start end code 1234 1-1-2008 5-31-2008 4567 1234 6-1-2008 12-31-2008 8910 and then the next agetn and so on, And then to make it even more complex, do I need to have an ending date???? or should I carry the entry way out and the adjust it to suit. Please advise "Shane Devenshire" wrote: Hi, You problem is going to be that you really aren't looking at a specific date but a range of dates, so you are going to need to create another table which associates dates and agent #. This will only work if this isn't completely random. Suppose you set up a table with Agent #'s in column L, Start Date in M and End Date in N and Office Code in O =SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10) Where the agent # is in A1 and the date in B1 -- If this helps, please click the Yes button Cheers, Shane Devenshire " wrote: The H1 column dates are random. I tried using a formula very similar to this and would not reconize random dates. I have been trying to do some type of if( < less than function, but cannot get it to jive. Will this formula MATCH the date to date and return a values or will will it work like if the date is the office code date it will produce the correct response, please advsie ie what would happen is the date were 2-28-2008 would it produce the code in associated with that random date in time 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2008 "T. Valko" wrote: Try this array formula** : G1 = 177721 H1 = 6-1-2009 =INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Returns: 401912 -- Biff Microsoft Excel MVP " wrote in message ... I have quesiton that has has not really been answered. I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hate to double post but have not really got a good answer, mor
Can you better explain what you date matching criteria is?
-- Biff Microsoft Excel MVP " wrote in message ... so would I have a two entry on each agent #, and sort by agent # or does sorting even matter ie agent # start end code 1234 1-1-2008 5-31-2008 4567 1234 6-1-2008 12-31-2008 8910 and then the next agetn and so on, And then to make it even more complex, do I need to have an ending date???? or should I carry the entry way out and the adjust it to suit. Please advise "Shane Devenshire" wrote: Hi, You problem is going to be that you really aren't looking at a specific date but a range of dates, so you are going to need to create another table which associates dates and agent #. This will only work if this isn't completely random. Suppose you set up a table with Agent #'s in column L, Start Date in M and End Date in N and Office Code in O =SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10) Where the agent # is in A1 and the date in B1 -- If this helps, please click the Yes button Cheers, Shane Devenshire " wrote: The H1 column dates are random. I tried using a formula very similar to this and would not reconize random dates. I have been trying to do some type of if( < less than function, but cannot get it to jive. Will this formula MATCH the date to date and return a values or will will it work like if the date is the office code date it will produce the correct response, please advsie ie what would happen is the date were 2-28-2008 would it produce the code in associated with that random date in time 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2008 "T. Valko" wrote: Try this array formula** : G1 = 177721 H1 = 6-1-2009 =INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Returns: 401912 -- Biff Microsoft Excel MVP " wrote in message ... I have quesiton that has has not really been answered. I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hate to double post but have not really got a good answer, mor
OK the basic way it works is the agent # gets paid for some form
of production, when the agent # get paid a portion of the payment goes to the office code, The agent # will never change, the office code is more like a manager hierachy. So lets say BOB started on JAN 1 2008, his agent # is 1234 and is under office code 4567 so if he did somehting that would generate lets say $100.00 $50.00 would go to the agent # and 50.00 would go to the office code. So on 5-15 we decide to close the office code 4567 and start a new profit code 8910 OK so to make it easy. The column would be a: Agent # b: writting date c: office code. I need the correct office code to fall the in the range of dates the agent # acctually dated the produciton. was profiled to that code agent # start end code 1234 1-1-2008 5-31-2008 4567 1234 6-1-2008 12-31-2008 8910 so If the writting date were 2-25, or 5-09 the office code produced would be 4567 if the writting date were 6-09 or 9-27 the off code would procude 8910 SO the writting date would random, the agent # will stay unique "T. Valko" wrote: Can you better explain what you date matching criteria is? -- Biff Microsoft Excel MVP " wrote in message ... so would I have a two entry on each agent #, and sort by agent # or does sorting even matter ie agent # start end code 1234 1-1-2008 5-31-2008 4567 1234 6-1-2008 12-31-2008 8910 and then the next agetn and so on, And then to make it even more complex, do I need to have an ending date???? or should I carry the entry way out and the adjust it to suit. Please advise "Shane Devenshire" wrote: Hi, You problem is going to be that you really aren't looking at a specific date but a range of dates, so you are going to need to create another table which associates dates and agent #. This will only work if this isn't completely random. Suppose you set up a table with Agent #'s in column L, Start Date in M and End Date in N and Office Code in O =SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10) Where the agent # is in A1 and the date in B1 -- If this helps, please click the Yes button Cheers, Shane Devenshire " wrote: The H1 column dates are random. I tried using a formula very similar to this and would not reconize random dates. I have been trying to do some type of if( < less than function, but cannot get it to jive. Will this formula MATCH the date to date and return a values or will will it work like if the date is the office code date it will produce the correct response, please advsie ie what would happen is the date were 2-28-2008 would it produce the code in associated with that random date in time 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2008 "T. Valko" wrote: Try this array formula** : G1 = 177721 H1 = 6-1-2009 =INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Returns: 401912 -- Biff Microsoft Excel MVP " wrote in message ... I have quesiton that has has not really been answered. I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hate to double post but have not really got a good answer, mor
Ok, if you have a date range then:
G1 = agent lookup number H1 = start date I1 = end date Array entered** : =INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(B1:B10<=H1)*(C1 :C10=I1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP " wrote in message ... OK the basic way it works is the agent # gets paid for some form of production, when the agent # get paid a portion of the payment goes to the office code, The agent # will never change, the office code is more like a manager hierachy. So lets say BOB started on JAN 1 2008, his agent # is 1234 and is under office code 4567 so if he did somehting that would generate lets say $100.00 $50.00 would go to the agent # and 50.00 would go to the office code. So on 5-15 we decide to close the office code 4567 and start a new profit code 8910 OK so to make it easy. The column would be a: Agent # b: writting date c: office code. I need the correct office code to fall the in the range of dates the agent # acctually dated the produciton. was profiled to that code agent # start end code 1234 1-1-2008 5-31-2008 4567 1234 6-1-2008 12-31-2008 8910 so If the writting date were 2-25, or 5-09 the office code produced would be 4567 if the writting date were 6-09 or 9-27 the off code would procude 8910 SO the writting date would random, the agent # will stay unique "T. Valko" wrote: Can you better explain what you date matching criteria is? -- Biff Microsoft Excel MVP " wrote in message ... so would I have a two entry on each agent #, and sort by agent # or does sorting even matter ie agent # start end code 1234 1-1-2008 5-31-2008 4567 1234 6-1-2008 12-31-2008 8910 and then the next agetn and so on, And then to make it even more complex, do I need to have an ending date???? or should I carry the entry way out and the adjust it to suit. Please advise "Shane Devenshire" wrote: Hi, You problem is going to be that you really aren't looking at a specific date but a range of dates, so you are going to need to create another table which associates dates and agent #. This will only work if this isn't completely random. Suppose you set up a table with Agent #'s in column L, Start Date in M and End Date in N and Office Code in O =SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10) Where the agent # is in A1 and the date in B1 -- If this helps, please click the Yes button Cheers, Shane Devenshire " wrote: The H1 column dates are random. I tried using a formula very similar to this and would not reconize random dates. I have been trying to do some type of if( < less than function, but cannot get it to jive. Will this formula MATCH the date to date and return a values or will will it work like if the date is the office code date it will produce the correct response, please advsie ie what would happen is the date were 2-28-2008 would it produce the code in associated with that random date in time 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2008 "T. Valko" wrote: Try this array formula** : G1 = 177721 H1 = 6-1-2009 =INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Returns: 401912 -- Biff Microsoft Excel MVP " wrote in message ... I have quesiton that has has not really been answered. I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Good post | Excel Worksheet Functions | |||
How much I hate the ribbon | Excel Discussion (Misc queries) | |||
Worksheet looks good in print, not so good on-screen | Excel Discussion (Misc queries) | |||
Can someone answer to my post please | Excel Discussion (Misc queries) | |||
Why is the answer to my formula double what it should be? | Excel Discussion (Misc queries) |