| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve data when multiple criteria need to be met
Hi there! I have 2 spreadsheets that I'm working on. Spreadsheet A
has data that is being exported from an Access query. I would like to be able to use Spreadsheet B to relay the information from A (sort of a template/dashboard). Spreadsheet A has each month listed and the data for 3 different locations. Also, as the year progresses Spreadsheet A will change so I cannot simply link Spreadsheet B to a particular cell in Spreadsheet A. Does anyone have any ideas on what formula I could use in Spreadsheet B to only get data for a certain month? I'd like to be able to enter the month and it automatically update the data for the 3 locations? Any information would be greatly appreciated. Spreadsheet A LOCATION MONTH PROCESSED PAID Burbank 1/1/08 110190 32104 Burbank 2/1/08 152601 49090 Fairfield 1/1/08 128009 51313 Fairfield 2/1/08 92301 23115 Fresno 1/1/08 141253 50143 Fresno 2/1/08 160561 50548 So, for example, I would like to enter a date and it automatically populate each location with the Processed and Paid for a given month. Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve data when multiple criteria need to be met
A few questions...
When you say Spreadsheet A and Spreadsheet B, are you referring to 2 separate *files* or 2 worksheets within the *same* file? Does each location have only 1 entry per month as your sample data demonstrates? Approximately how many rows of data does Spreadsheet A contain? -- Biff Microsoft Excel MVP wrote in message ... Hi there! I have 2 spreadsheets that I'm working on. Spreadsheet A has data that is being exported from an Access query. I would like to be able to use Spreadsheet B to relay the information from A (sort of a template/dashboard). Spreadsheet A has each month listed and the data for 3 different locations. Also, as the year progresses Spreadsheet A will change so I cannot simply link Spreadsheet B to a particular cell in Spreadsheet A. Does anyone have any ideas on what formula I could use in Spreadsheet B to only get data for a certain month? I'd like to be able to enter the month and it automatically update the data for the 3 locations? Any information would be greatly appreciated. Spreadsheet A LOCATION MONTH PROCESSED PAID Burbank 1/1/08 110190 32104 Burbank 2/1/08 152601 49090 Fairfield 1/1/08 128009 51313 Fairfield 2/1/08 92301 23115 Fresno 1/1/08 141253 50143 Fresno 2/1/08 160561 50548 So, for example, I would like to enter a date and it automatically populate each location with the Processed and Paid for a given month. Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve data when multiple criteria need to be met
On Jun 11, 7:12*pm, "T. Valko" wrote:
A few questions... When you say Spreadsheet A and Spreadsheet B, are you referring to 2 separate *files* or 2 worksheets within the *same* file? Does each location have only 1 entry per month as your sample data demonstrates? Approximately how many rows of data does Spreadsheet A contain? -- Biff Microsoft Excel MVP wrote in message ... Hi there! *I have 2 spreadsheets that I'm working on. *Spreadsheet A has data that is being exported from an Access query. *I would like to be able to use Spreadsheet B to relay the information from A (sort of a template/dashboard). *Spreadsheet A has each month listed and the data for 3 different locations. *Also, as the year progresses Spreadsheet A will change so I cannot simply link Spreadsheet B to a particular cell in Spreadsheet A. *Does anyone have any ideas on what formula I could use in Spreadsheet B to only get data for a certain month? *I'd like to be able to enter the month and it automatically update the data for the 3 locations? *Any information would be greatly appreciated. Spreadsheet A LOCATION * * * * *MONTH * * * * *PROCESSED * * * * *PAID Burbank * * * * * * *1/1/08 * * * * * * 110190 * * * * * * * * * 32104 Burbank * * * * * * *2/1/08 * * * * * * 152601 * * * * * * * * * 49090 Fairfield * * * * * * * 1/1/08 * * * * * * 128009 51313 Fairfield * * * * * * * 2/1/08 * * * * * * *92301 23115 Fresno * * * * * * * *1/1/08 * * * * * * *141253 50143 Fresno * * * * * * * *2/1/08 * * * * * * *160561 50548 So, for example, I would like to enter a date and it automatically populate each location with the Processed and Paid for a given month. Thanks!- Hide quoted text - - Show quoted text - They are 2 separate workbooks. Yes, each location only has one entry per month. Currently my spreadsheet has 15 rows of data (3 locations x each completed month in 2008) but it will become longer as the year progresses. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve data when multiple criteria need to be met
Try this:
SPA = spreadsheet A, data on sheet1 in the range A1:Dn. Row 1 are column headers. In spreadsheet B, B1 = a date you enter such as 1/1/2008. A2:C2 = headers: Location, Processed, Paid A3:An = locations Enter this formula in B3 and copy across to C3 then down as needed: =SUMPRODUCT(--([SPA.xls]Sheet1!$A$2:$A$100=$A3),--(MONTH([SPA.xls]Sheet1!$B$2:$B$100)=MONTH($A$1)),[SPA.xls]Sheet1!C$2:C$100) Adjust ranges to suit. To make it easier have spreadsheet A open while you enter the formula. That way Excel will add the path for you. -- Biff Microsoft Excel MVP wrote in message ... On Jun 11, 7:12 pm, "T. Valko" wrote: A few questions... When you say Spreadsheet A and Spreadsheet B, are you referring to 2 separate *files* or 2 worksheets within the *same* file? Does each location have only 1 entry per month as your sample data demonstrates? Approximately how many rows of data does Spreadsheet A contain? -- Biff Microsoft Excel MVP wrote in message ... Hi there! I have 2 spreadsheets that I'm working on. Spreadsheet A has data that is being exported from an Access query. I would like to be able to use Spreadsheet B to relay the information from A (sort of a template/dashboard). Spreadsheet A has each month listed and the data for 3 different locations. Also, as the year progresses Spreadsheet A will change so I cannot simply link Spreadsheet B to a particular cell in Spreadsheet A. Does anyone have any ideas on what formula I could use in Spreadsheet B to only get data for a certain month? I'd like to be able to enter the month and it automatically update the data for the 3 locations? Any information would be greatly appreciated. Spreadsheet A LOCATION MONTH PROCESSED PAID Burbank 1/1/08 110190 32104 Burbank 2/1/08 152601 49090 Fairfield 1/1/08 128009 51313 Fairfield 2/1/08 92301 23115 Fresno 1/1/08 141253 50143 Fresno 2/1/08 160561 50548 So, for example, I would like to enter a date and it automatically populate each location with the Processed and Paid for a given month. Thanks!- Hide quoted text - - Show quoted text - They are 2 separate workbooks. Yes, each location only has one entry per month. Currently my spreadsheet has 15 rows of data (3 locations x each completed month in 2008) but it will become longer as the year progresses. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve data when multiple criteria need to be met
Typo:
In spreadsheet B, B1 = a date you enter such as 1/1/2008. Should be: In spreadsheet B, A1 = a date you enter such as 1/1/2008. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: SPA = spreadsheet A, data on sheet1 in the range A1:Dn. Row 1 are column headers. In spreadsheet B, B1 = a date you enter such as 1/1/2008. A2:C2 = headers: Location, Processed, Paid A3:An = locations Enter this formula in B3 and copy across to C3 then down as needed: =SUMPRODUCT(--([SPA.xls]Sheet1!$A$2:$A$100=$A3),--(MONTH([SPA.xls]Sheet1!$B$2:$B$100)=MONTH($A$1)),[SPA.xls]Sheet1!C$2:C$100) Adjust ranges to suit. To make it easier have spreadsheet A open while you enter the formula. That way Excel will add the path for you. -- Biff Microsoft Excel MVP wrote in message ... On Jun 11, 7:12 pm, "T. Valko" wrote: A few questions... When you say Spreadsheet A and Spreadsheet B, are you referring to 2 separate *files* or 2 worksheets within the *same* file? Does each location have only 1 entry per month as your sample data demonstrates? Approximately how many rows of data does Spreadsheet A contain? -- Biff Microsoft Excel MVP wrote in message ... Hi there! I have 2 spreadsheets that I'm working on. Spreadsheet A has data that is being exported from an Access query. I would like to be able to use Spreadsheet B to relay the information from A (sort of a template/dashboard). Spreadsheet A has each month listed and the data for 3 different locations. Also, as the year progresses Spreadsheet A will change so I cannot simply link Spreadsheet B to a particular cell in Spreadsheet A. Does anyone have any ideas on what formula I could use in Spreadsheet B to only get data for a certain month? I'd like to be able to enter the month and it automatically update the data for the 3 locations? Any information would be greatly appreciated. Spreadsheet A LOCATION MONTH PROCESSED PAID Burbank 1/1/08 110190 32104 Burbank 2/1/08 152601 49090 Fairfield 1/1/08 128009 51313 Fairfield 2/1/08 92301 23115 Fresno 1/1/08 141253 50143 Fresno 2/1/08 160561 50548 So, for example, I would like to enter a date and it automatically populate each location with the Processed and Paid for a given month. Thanks!- Hide quoted text - - Show quoted text - They are 2 separate workbooks. Yes, each location only has one entry per month. Currently my spreadsheet has 15 rows of data (3 locations x each completed month in 2008) but it will become longer as the year progresses. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve data when multiple criteria need to be met
On Jun 12, 7:48*pm, "T. Valko" wrote:
Typo: In spreadsheet B, B1 = a date you enter such as 1/1/2008. Should be: In spreadsheet B, A1 = a date you enter such as 1/1/2008. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: SPA = spreadsheet A, data on sheet1 in the range A1:Dn. Row 1 are column headers. In spreadsheet B, B1 = a date you enter such as 1/1/2008. A2:C2 = headers: Location, Processed, Paid A3:An = locations Enter this formula in B3 and copy across to C3 then down as needed: =SUMPRODUCT(--([SPA.xls]Sheet1!$A$2:$A$100=$A3),--(MONTH([SPA.xls]Sheet1!$B*$2:$B$100)=MONTH($A$1)),[SPA.xls]Sheet1!C$2:C$100) Adjust ranges to suit. To make it easier have spreadsheet A open while you enter the formula. That way Excel will add the path for you. -- Biff Microsoft Excel MVP wrote in message ... On Jun 11, 7:12 pm, "T. Valko" wrote: A few questions... When you say Spreadsheet A and Spreadsheet B, are you referring to 2 separate *files* or 2 worksheets within the *same* file? Does each location have only 1 entry per month as your sample data demonstrates? Approximately how many rows of data does Spreadsheet A contain? -- Biff Microsoft Excel MVP wrote in message ... Hi there! I have 2 spreadsheets that I'm working on. Spreadsheet A has data that is being exported from an Access query. I would like to be able to use Spreadsheet B to relay the information from A (sort of a template/dashboard). Spreadsheet A has each month listed and the data for 3 different locations. Also, as the year progresses Spreadsheet A will change so I cannot simply link Spreadsheet B to a particular cell in Spreadsheet A. Does anyone have any ideas on what formula I could use in Spreadsheet B to only get data for a certain month? I'd like to be able to enter the month and it automatically update the data for the 3 locations? Any information would be greatly appreciated. Spreadsheet A LOCATION MONTH PROCESSED PAID Burbank 1/1/08 110190 32104 Burbank 2/1/08 152601 49090 Fairfield 1/1/08 128009 51313 Fairfield 2/1/08 92301 23115 Fresno 1/1/08 141253 50143 Fresno 2/1/08 160561 50548 So, for example, I would like to enter a date and it automatically populate each location with the Processed and Paid for a given month. Thanks!- Hide quoted text - - Show quoted text - They are 2 separate workbooks. *Yes, each location only has one entry per month. *Currently my spreadsheet has 15 rows of data (3 locations x each completed month in 2008) but it will become longer as the year progresses.- Hide quoted text - - Show quoted text - It Worked!!! Thanks so much!!!! You saved me such much time!!!!! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve data when multiple criteria need to be met
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP wrote in message ... On Jun 12, 7:48 pm, "T. Valko" wrote: Typo: In spreadsheet B, B1 = a date you enter such as 1/1/2008. Should be: In spreadsheet B, A1 = a date you enter such as 1/1/2008. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: SPA = spreadsheet A, data on sheet1 in the range A1:Dn. Row 1 are column headers. In spreadsheet B, B1 = a date you enter such as 1/1/2008. A2:C2 = headers: Location, Processed, Paid A3:An = locations Enter this formula in B3 and copy across to C3 then down as needed: =SUMPRODUCT(--([SPA.xls]Sheet1!$A$2:$A$100=$A3),--(MONTH([SPA.xls]Sheet1!$B*$2:$B$100)=MONTH($A$1)),[SPA.xls]Sheet1!C$2:C$100) Adjust ranges to suit. To make it easier have spreadsheet A open while you enter the formula. That way Excel will add the path for you. -- Biff Microsoft Excel MVP wrote in message ... On Jun 11, 7:12 pm, "T. Valko" wrote: A few questions... When you say Spreadsheet A and Spreadsheet B, are you referring to 2 separate *files* or 2 worksheets within the *same* file? Does each location have only 1 entry per month as your sample data demonstrates? Approximately how many rows of data does Spreadsheet A contain? -- Biff Microsoft Excel MVP wrote in message ... Hi there! I have 2 spreadsheets that I'm working on. Spreadsheet A has data that is being exported from an Access query. I would like to be able to use Spreadsheet B to relay the information from A (sort of a template/dashboard). Spreadsheet A has each month listed and the data for 3 different locations. Also, as the year progresses Spreadsheet A will change so I cannot simply link Spreadsheet B to a particular cell in Spreadsheet A. Does anyone have any ideas on what formula I could use in Spreadsheet B to only get data for a certain month? I'd like to be able to enter the month and it automatically update the data for the 3 locations? Any information would be greatly appreciated. Spreadsheet A LOCATION MONTH PROCESSED PAID Burbank 1/1/08 110190 32104 Burbank 2/1/08 152601 49090 Fairfield 1/1/08 128009 51313 Fairfield 2/1/08 92301 23115 Fresno 1/1/08 141253 50143 Fresno 2/1/08 160561 50548 So, for example, I would like to enter a date and it automatically populate each location with the Processed and Paid for a given month. Thanks!- Hide quoted text - - Show quoted text - They are 2 separate workbooks. Yes, each location only has one entry per month. Currently my spreadsheet has 15 rows of data (3 locations x each completed month in 2008) but it will become longer as the year progresses.- Hide quoted text - - Show quoted text - It Worked!!! Thanks so much!!!! You saved me such much time!!!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to retrieve multiple rows with one criteria | Excel Discussion (Misc queries) | |||
| How to retrieve multiple values in multiple rows with one criteria | Excel Discussion (Misc queries) | |||
| retrieve multiple results with one criteria... | Excel Discussion (Misc queries) | |||
| Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
| How can I retrieve multiple rows that match one criteria, i.e. a d | Excel Worksheet Functions | |||
Linear Mode
