How can I extract rows of information from multiple sheets by typing in a date

Question

I need to extract data from multiple sheets in excel. I have made one excel workbook with different repair schedules for different properties. On the first sheet I need to be able to type in a date for example 12/21/2011 and then have it pull each row of information for that date from all the properties. I need to be able to pull each person's information for that specific date so that I can see each repair and that needs to be done on that day. This would be so that it can be printed and then taken for repairs to see the location, numbers, times, etc. Or to be able to see what repairs where done on specific dates.

The colum headings are date-- time --resident name--phone number--address--reason for visit---visual meter read--MTU meter read--MTU ID--Account number--Action Taken--

I have figured out how to record a macr, but I can't figure out how to pull the information. I did try to use Vlookup but it doesn't work for different sheets. If anyone could help I would really appreciate it

Answer 1

Advanced filter (AF) would be one way to do this. It does require a small bit of manual work, however, and the data would all be on one sheet.

This example assumes your data starts in column A on a sheet named Sheet1 with the headers running from A1:K1 (based on information from your question).

1- Name your input range Database like this:

=OFFSET(Sheet1!$A$1,0,0,11,COUNTA(Sheet1!$A:$A))

This will ensure that each time you run AF, it will always pick up the latest data added to the bottom of the existing list.

2- Copy the headers from the data sheet to other columns in the same sheet. Copy only the headers. Ex: if your columns spanned from A1:K1, copy A1:K1 to M1:W1.
3- Hide columns N through W.
4- In cell M2, put the date you want to search for.
5- See below for filter criteria.

Example:

Columns A through K have sample meter data:

Columns M through W contain the criteria range, with columns N through W hidden:

In cell M2, put the date you want to return values for:

Go to Data » Filter » Advanced Filter (or however you do it in your version of Excel, mine is 2003) and use the following criteria:

• List Range should always be Database to capture any new records that may have been added since the last time AF was run.
• Criteria Range should always be $M$1:$W$2 although you can put this anywhere and even name it if you prefer.
• Copy to should be wherever you want to place the matching records, however it must be on the same sheet (sorry, I did say this was only one possible solution)

When you click 'OK' you should see the matching records for that date put into columns Z:AJ. Change the date and go to Data » Filter » Advanced Filter again to filter records based on the new date. If you add new records you should see them in the filter because we used a dynamic range name (Database) to grab them.

HTH