finding and copying data from one workbook with multiple worksheets to another

D

demoss7777

I have two workbooks. Workbook A has multiple worksheets. Each of the
worksheets represents one week of the year. For example, worksheet
"week ending 7-30-05" has data for each day of that week. Row 1-5 of
the worksheet has data for 7/24/05. Cell "A1" shows the date, 7/24/05.

Row 6-10 has data from 7/25/05. Cell "A6" shows the date, 7/25/05.


Workbook B has today's date in cell "A1"
I would like to have the formulae to have Workbook B automatically
search for the appropriate tab and date in Workbook A and then when it
finds the right date, copy the appropriate data (rows) into the cells
below A1

Can anyone suggest formulae to be placed in cells below A1 that will
use A1's date and copy the correct data into those cells?
Thanks.
 
E

Earl Kiosterud

Demoss,

Unless there's a compelling reason to have the weeks in different
worksheets, you will probably fare much better now, and for future
requirements by putting all the stuff in one worksheet. You're saying "no,
no" right now. Read "Data across multiple worksheets" at
www.smokeylake.com/excel/excel_truths.htm.

If you do this, you can use simple VLOOKUPs to get the row of the desired
date, or if there are multiple matches, use Autofilter or Advanced filter.
On rereading, I see the latter is your case.

If you're not willing to consolidate the sheets, you'll need a macro written
to weed through the sheets, I'm pretty sure. Excel has little in tools for
working with multiple sheets, and normal database design precludes such
separate tables of similar data anyway.
 
Top