Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling info from other spreadsheets based on a set of criteria...
Here are two example spreadsheets€¦
I need spreadsheet 1 to pull into column E1 the info from column E2 of spreadsheet 2, based on the Contract Number (Column C1 matching Column A2) & Dollars Rcvd. Date (Column D1 matching D2) Basically, if the plan number, dollar amount and date received matches spreadsheet 2, I need the corresponding date paid from spreadsheet 2. I will also need spreadsheet 1 to look at multiple spreadsheets at the same time to see if the information was entered on any of the forms. (Spreadsheet 1) A1 B1 C1 D1 E1 F1 G1 Amount Entires Contract # Date Paid Date Amount USRID 752.3 1 B274 2/1/2007 2/1/07 752.30 uaba 422.24 1 B274 2/1/2007 2/1/07 422.24 ujxc 181.35 1 A815 2/1/2007 2/2/07 181.35 ukhs (Spreadsheet 2) A2 B2 C2 D2 E2 F2 Contract# Paid Dollars Rcvd. Rcvd. Date Paid Date USRID B274 y 752.30 2/1/2007 2/1/2007 uaba B274 y 422.24 2/1/2007 2/1/2007 ujxc A815 y 181.35 2/1/07 2-2-07 ukhs |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling info from other spreadsheets based on a set of criteria...
Some thoughts for this part of it ..
.. spreadsheet 1 to pull into column E1 the info from column E2 of spreadsheet 2, based on the Contract Number (Column C1 matching Column A2) & Dollars Rcvd. Date (Column D1 matching D2) ... In Sheet1, Put in E2, then array-enter (press CTRL+SHIFT+ENTER): =INDEX(Sheet2!$E$2:$E$100,MATCH(1,(Sheet2!$A$2:$A$ 100=C2)*(Sheet2!$D$2:$D$100=D2),0)) Format as date to taste, then copy E2 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "watchtower" wrote: Here are two example spreadsheets€¦ I need spreadsheet 1 to pull into column E1 the info from column E2 of spreadsheet 2, based on the Contract Number (Column C1 matching Column A2) & Dollars Rcvd. Date (Column D1 matching D2) Basically, if the plan number, dollar amount and date received matches spreadsheet 2, I need the corresponding date paid from spreadsheet 2. I will also need spreadsheet 1 to look at multiple spreadsheets at the same time to see if the information was entered on any of the forms. (Spreadsheet 1) A1 B1 C1 D1 E1 F1 G1 Amount Entires Contract # Date Paid Date Amount USRID 752.3 1 B274 2/1/2007 2/1/07 752.30 uaba 422.24 1 B274 2/1/2007 2/1/07 422.24 ujxc 181.35 1 A815 2/1/2007 2/2/07 181.35 ukhs (Spreadsheet 2) A2 B2 C2 D2 E2 F2 Contract# Paid Dollars Rcvd. Rcvd. Date Paid Date USRID B274 y 752.30 2/1/2007 2/1/2007 uaba B274 y 422.24 2/1/2007 2/1/2007 ujxc A815 y 181.35 2/1/07 2-2-07 ukhs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct - Pulling info based on lenght of time between dates | Excel Worksheet Functions | |||
Lookup Value (and Total) Based on Two Criteria | Excel Worksheet Functions | |||
Lookup function based on 3 criteria | Excel Discussion (Misc queries) | |||
varying validation info based on preceding info | Excel Discussion (Misc queries) | |||
Returning Results Based on Two Criteria | Excel Worksheet Functions |