Pull data from yyyymmmmdd.xls

[Mike Punko]

OK this is a bit lengthy so bare with me.

'2005 Daily Production.xls' is setup with sheets for each month. Within each
month it breaks down by day. Presently the data for these days comes from
'2005September01.xls' (yyyymmmmdd.xls) but via manual copy and paste. I
would like for the '2005 Daily Production.xls' to automatically pull the data
from the '2005September01.xls','2005September02.xls', '2005September03.xls'
etc.. sheets. The '2005September01.xls' are created first each day then the
'2005 Daily Production.xls' is opened.

'2005 Daily Production.xls'

A B C D E
DATE BBLs tPHs BBLs tPHs
01-Sep 423 8245 323 8500
02-Sep 450 8200 350 8450
03-Sep


'2005September01.xls'
A B C
01-Sep T1 T2
BBLs 423 323
tPHs 8245 8500

'2005September01.xls'
A B C
01-Sep T1 T2
BBLs 450 350
tPHs 8200 8450

If you have any ideals or questions please email me directly at
(e-mail address removed) since I believe this will be a lot of coding or
explanation required.

 

[Mike Punko]

Forgot to mention that I did this with the following code.

=IF(ISERROR(IF(OR($A4=$B$2,$A4<$B$2),'W:\Aurora Daily Production
Report\2005\09_Sep\[2005September01.xls]Daily
Report'!$E$5,""))=TRUE,"",IF(OR($A4=$B$2,$A4<$B$2),'W:\Aurora Daily
Production Report\2005\09_Sep\[2005September01.xls]Daily Report'!$E$5,""))

But what I require is that I can use the DATE to referance the correct file
name.
So if A2=02-Sep then it will look at 2005September02.xls I will also need it
to referance the directory 'W:\Aurora Daily Production
Report\2005\09_Sep\[2005September01.xls] so as you can see there will be a
YYYY\dd_mmm\yyyymmmmdd.xls refeance required.