| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does anyone know how to create a lookup that will do this?
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 a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
Why not just use filters?
-- HTH... Jim Thomlinson "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 a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
As long as the values are unique for Date, Loc and Acct (no more than one row
per combination) if the table is on sheet2!A1:F200 and on the report sheet Date: B6 Loc: B7 Acct: B8 to return the Activity Amount for that combination: =sumproduct(--(Sheet2!$A$2:$A$200=B6),--(Sheet2!$B$2:$B$200=B7),--(Sheet2!$C$2$C$200=B8),$E$2:$E$200) -- 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 a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
Hi Tom,
Thanks for the formula. I can't seem to get it to work. I only need the the amount in one column (for example the activity amount) But I want it to search in the right month, LOC, & Acct. I can create control cells but i don't know how to do the formula. let me know if you have any other ideas. Thanks Mascot "Tom Ogilvy" wrote: As long as the values are unique for Date, Loc and Acct (no more than one row per combination) if the table is on sheet2!A1:F200 and on the report sheet Date: B6 Loc: B7 Acct: B8 to return the Activity Amount for that combination: =sumproduct(--(Sheet2!$A$2:$A$200=B6),--(Sheet2!$B$2:$B$200=B7),--(Sheet2!$C$2$C$200=B8),$E$2:$E$200) -- 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 a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
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$200)=MONTH(B7)),--(Sheet2!$B$2:$B$200=B8),--(Sheet2!$C$2:$C$200=B9),Sheet2!$E$2:$E$200) I tested it with your data and it worked for me. -- Regards, Tom Ogilvy "Mascot" wrote: Hi Tom, Thanks for the formula. I can't seem to get it to work. I only need the the amount in one column (for example the activity amount) But I want it to search in the right month, LOC, & Acct. I can create control cells but i don't know how to do the formula. let me know if you have any other ideas. Thanks Mascot "Tom Ogilvy" wrote: As long as the values are unique for Date, Loc and Acct (no more than one row per combination) if the table is on sheet2!A1:F200 and on the report sheet Date: B6 Loc: B7 Acct: B8 to return the Activity Amount for that combination: =sumproduct(--(Sheet2!$A$2:$A$200=B6),--(Sheet2!$B$2:$B$200=B7),--(Sheet2!$C$2$C$200=B8),$E$2:$E$200) -- 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 a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
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 "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 a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
When I corrected and tested the formula, I put the values in B7, B8 and B9
rather than B6, B7, B8. -- 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 "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 a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
|
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do a create a lookup using first and last name | Excel Worksheet Functions | |||
| How to create a macro to help with lookup. | Excel Worksheet Functions | |||
| Does anyone know how to create a lookup that will do this? | Excel Worksheet Functions | |||
| Does anyone know how to create a lookup that will do this? | Excel Discussion (Misc queries) | |||
| how do i create a lookup | Excel Discussion (Misc queries) | |||
Linear Mode
