| 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 |
| 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) | |||
Hybrid Mode
