Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with lOOKUP?
I have a table that looks like the following example.
Date LOC ACCT Beg Bal Activity Ending May-06 M101 1001 10.00 210.00 310.00 May-06 M101 1002 20.00 220.00 320.00 May-06 M101 1003 30.00 230.00 330.00 May-06 M101 1004 40.00 240.00 340.00 May-06 M101 1005 50.00 250.00 350.00 Jun-06 M101 1001 60.00 260.00 360.00 Jun-06 M101 1002 70.00 270.00 370.00 Jun-06 M101 1003 80.00 280.00 380.00 Jun-06 M101 1004 90.00 290.00 390.00 Jun-06 M101 1005 100.00 300.00 400.00 May-06 M102 1001 110.00 310.00 410.00 May-06 M102 1002 120.00 320.00 420.00 May-06 M102 1003 130.00 330.00 430.00 May-06 M102 1004 140.00 340.00 440.00 May-06 M102 1005 150.00 350.00 450.00 Jun-06 M102 1001 160.00 360.00 460.00 Jun-06 M102 1002 170.00 370.00 470.00 Jun-06 M102 1003 180.00 380.00 480.00 Jun-06 M102 1004 190.00 390.00 490.00 Jun-06 M102 1005 200.00 400.00 500.00 I have a spreadsheet that I need to fill out every month in a specific format so I need to have something that will pull info from this table so I dont have to type it in by hand every month. For example I may need to have the specific amount in the Activity column for a certain month location & account. So I need to have a lookup search through this table using the Month, LOC, & Acct as criteria so it can return the right amount in the activity column. SO I will have control cells with DATE, LOC, ACCT. Please help this is daunting task. Thanks Mascot |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with lOOKUP?
there was a typo in my formula - guess you didn't spot it. Also, this makes
the month match more robust as long as column A contains true date values: =SUMPRODUCT(--(MONTH(Sheet2!$A$2:$A$20)=MONTH(B7)),--(Sheet2!$B$2:$B$20=B8),--(Sheet2!$C$2:$C$20=B9),Sheet2!$E$2:$E$20) -- Regards, Tom Ogilvy "Mascot" wrote: I have a table that looks like the following example. Date LOC ACCT Beg Bal Activity Ending May-06 M101 1001 10.00 210.00 310.00 May-06 M101 1002 20.00 220.00 320.00 May-06 M101 1003 30.00 230.00 330.00 May-06 M101 1004 40.00 240.00 340.00 May-06 M101 1005 50.00 250.00 350.00 Jun-06 M101 1001 60.00 260.00 360.00 Jun-06 M101 1002 70.00 270.00 370.00 Jun-06 M101 1003 80.00 280.00 380.00 Jun-06 M101 1004 90.00 290.00 390.00 Jun-06 M101 1005 100.00 300.00 400.00 May-06 M102 1001 110.00 310.00 410.00 May-06 M102 1002 120.00 320.00 420.00 May-06 M102 1003 130.00 330.00 430.00 May-06 M102 1004 140.00 340.00 440.00 May-06 M102 1005 150.00 350.00 450.00 Jun-06 M102 1001 160.00 360.00 460.00 Jun-06 M102 1002 170.00 370.00 470.00 Jun-06 M102 1003 180.00 380.00 480.00 Jun-06 M102 1004 190.00 390.00 490.00 Jun-06 M102 1005 200.00 400.00 500.00 I have a spreadsheet that I need to fill out every month in a specific format so I need to have something that will pull info from this table so I dont have to type it in by hand every month. For example I may need to have the specific amount in the Activity column for a certain month location & account. So I need to have a lookup search through this table using the Month, LOC, & Acct as criteria so it can return the right amount in the activity column. SO I will have control cells with DATE, LOC, ACCT. Please help this is daunting task. Thanks Mascot |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with lOOKUP?
Hi Tom,
Maybe I am puting the formula in the wrong place. I have the data in sheet2 like you said. On the next sheet (sheet3) I put May06 (5/1/06) in B6, M101 in B7, 1001 in B8. I then put the formula in C7, but I am not getting the anything except VALUE#. What am I doing wrong? Mascot "Tom Ogilvy" wrote: there was a typo in my formula - guess you didn't spot it. Also, this makes the month match more robust as long as column A contains true date values: =SUMPRODUCT(--(MONTH(Sheet2!$A$2:$A$20)=MONTH(B7)),--(Sheet2!$B$2:$B$20=B8),--(Sheet2!$C$2:$C$20=B9),Sheet2!$E$2:$E$20) -- Regards, Tom Ogilvy "Mascot" wrote: I have a table that looks like the following example. Date LOC ACCT Beg Bal Activity Ending May-06 M101 1001 10.00 210.00 310.00 May-06 M101 1002 20.00 220.00 320.00 May-06 M101 1003 30.00 230.00 330.00 May-06 M101 1004 40.00 240.00 340.00 May-06 M101 1005 50.00 250.00 350.00 Jun-06 M101 1001 60.00 260.00 360.00 Jun-06 M101 1002 70.00 270.00 370.00 Jun-06 M101 1003 80.00 280.00 380.00 Jun-06 M101 1004 90.00 290.00 390.00 Jun-06 M101 1005 100.00 300.00 400.00 May-06 M102 1001 110.00 310.00 410.00 May-06 M102 1002 120.00 320.00 420.00 May-06 M102 1003 130.00 330.00 430.00 May-06 M102 1004 140.00 340.00 440.00 May-06 M102 1005 150.00 350.00 450.00 Jun-06 M102 1001 160.00 360.00 460.00 Jun-06 M102 1002 170.00 370.00 470.00 Jun-06 M102 1003 180.00 380.00 480.00 Jun-06 M102 1004 190.00 390.00 490.00 Jun-06 M102 1005 200.00 400.00 500.00 I have a spreadsheet that I need to fill out every month in a specific format so I need to have something that will pull info from this table so I dont have to type it in by hand every month. For example I may need to have the specific amount in the Activity column for a certain month location & account. So I need to have a lookup search through this table using the Month, LOC, & Acct as criteria so it can return the right amount in the activity column. SO I will have control cells with DATE, LOC, ACCT. Please help this is daunting task. Thanks Mascot |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with lOOKUP?
As written, the formula is looking for those values in B7, B8, B9 and you are
putting them in B6, b7, b8 When i use your numbers in the right cells the formula returns 210. (for activitiy) -- Regards, Tom Ogilvy "Mascot" wrote: Hi Tom, Maybe I am puting the formula in the wrong place. I have the data in sheet2 like you said. On the next sheet (sheet3) I put May06 (5/1/06) in B6, M101 in B7, 1001 in B8. I then put the formula in C7, but I am not getting the anything except VALUE#. What am I doing wrong? Mascot "Tom Ogilvy" wrote: there was a typo in my formula - guess you didn't spot it. Also, this makes the month match more robust as long as column A contains true date values: =SUMPRODUCT(--(MONTH(Sheet2!$A$2:$A$20)=MONTH(B7)),--(Sheet2!$B$2:$B$20=B8),--(Sheet2!$C$2:$C$20=B9),Sheet2!$E$2:$E$20) -- Regards, Tom Ogilvy "Mascot" wrote: I have a table that looks like the following example. Date LOC ACCT Beg Bal Activity Ending May-06 M101 1001 10.00 210.00 310.00 May-06 M101 1002 20.00 220.00 320.00 May-06 M101 1003 30.00 230.00 330.00 May-06 M101 1004 40.00 240.00 340.00 May-06 M101 1005 50.00 250.00 350.00 Jun-06 M101 1001 60.00 260.00 360.00 Jun-06 M101 1002 70.00 270.00 370.00 Jun-06 M101 1003 80.00 280.00 380.00 Jun-06 M101 1004 90.00 290.00 390.00 Jun-06 M101 1005 100.00 300.00 400.00 May-06 M102 1001 110.00 310.00 410.00 May-06 M102 1002 120.00 320.00 420.00 May-06 M102 1003 130.00 330.00 430.00 May-06 M102 1004 140.00 340.00 440.00 May-06 M102 1005 150.00 350.00 450.00 Jun-06 M102 1001 160.00 360.00 460.00 Jun-06 M102 1002 170.00 370.00 470.00 Jun-06 M102 1003 180.00 380.00 480.00 Jun-06 M102 1004 190.00 390.00 490.00 Jun-06 M102 1005 200.00 400.00 500.00 I have a spreadsheet that I need to fill out every month in a specific format so I need to have something that will pull info from this table so I dont have to type it in by hand every month. For example I may need to have the specific amount in the Activity column for a certain month location & account. So I need to have a lookup search through this table using the Month, LOC, & Acct as criteria so it can return the right amount in the activity column. SO I will have control cells with DATE, LOC, ACCT. Please help this is daunting task. Thanks Mascot |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with lOOKUP?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Lookup Ques - finding value within a string to lookup | Excel Programming | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |