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!

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!

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.

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.

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.

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!!!!!

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!!!!!