Event macro to extract data based on value of 2 cells - help
 

Does anyone know how I can accomplish this?
I have a spreadsheet with many columns in sheet1. In sheet2, I would like
to create a "search box" type of thing, where when a month & year is entered
into a cell, it automatically pulls up certain data from rows that have the
same month and year in sheet1. I say certain data, because I only want the
value of cells C and J to populate in sheet2. To make it more tricky,
there's one more criteria; I also want it to pull up data only from rows in
which cell F reads "Closed".
For example:
If sheet1 has rows 1,3,4,5, and 9 that cell F says "Closed" and it has a
date (cell K) of 1/xx/07, then when sheet2, cell D9 reads "January-07" it
will pull cells C and J from sheet1, rows 1,3,4,5, and 9, and list them in
D10-D14 and E10-E14. If sheet1, rows 11,13,14, and 15 have cell F saying
"Closed" and a date (cell K) of 2/xx/07, then when sheet2, cell D9 reads
"February-07" it will pull cells C and J from sheet1, rows 11,13,14, and 15,
and list them in D10-D13 and E10-E13.
I hope this is clear.
Much thanks to all,
Scott

Event macro to extract data based on value of 2 cells - help
 

Just an approach:

1. set up, or have VBA set up, Autofilters on sheet1
2. use VBA to set the criteria1 values on the different columns based on
sheet2 data
3. copy/paste the visible cells of interest
--
Gary's Student


"Scott Marcus" wrote:

Does anyone know how I can accomplish this?
I have a spreadsheet with many columns in sheet1. In sheet2, I would like
to create a "search box" type of thing, where when a month & year is entered
into a cell, it automatically pulls up certain data from rows that have the
same month and year in sheet1. I say certain data, because I only want the
value of cells C and J to populate in sheet2. To make it more tricky,
there's one more criteria; I also want it to pull up data only from rows in
which cell F reads "Closed".
For example:
If sheet1 has rows 1,3,4,5, and 9 that cell F says "Closed" and it has a
date (cell K) of 1/xx/07, then when sheet2, cell D9 reads "January-07" it
will pull cells C and J from sheet1, rows 1,3,4,5, and 9, and list them in
D10-D14 and E10-E14. If sheet1, rows 11,13,14, and 15 have cell F saying
"Closed" and a date (cell K) of 2/xx/07, then when sheet2, cell D9 reads
"February-07" it will pull cells C and J from sheet1, rows 11,13,14, and 15,
and list them in D10-D13 and E10-E13.
I hope this is clear.
Much thanks to all,
Scott