Formula to extract a set of values from a set of closed workbooks

[asmallh@gmail.com]

Timesheet Link

http://upload.tatis.com/download.php?file=05756699766e3486285186e3af9d7875


Roster Link

http://upload.tatis.com/download.php?file=aa9fe71881e54228cbf47764bd741168


Hi,


I am attempting to create a team time roster which will consolidate all of the my team's hours onto a single sheet.


Every week team members submit a timesheet. A sample of which can be downloaded from http://upload.tatis.com/download.php?file=05756699766e3486285186e3af9d7875


The time sheets are named using the convention 'Programme Name - Project - Date - Name'. The attached file is thus named 'XYZ Programme - Adobe - 20120504 - Jim Bean'.


The time sheets can be filed and stored on my machine in which ever way I choose.


Now I need to extract the value from cell P42 in the time sheet and place it in cell C2 of the Roster. The Roster can be downloaded from: http://upload.tatis.com/download.php?file=aa9fe71881e54228cbf47764bd741168


I am looking for a way to create a formula that will:

1. Allow the values to be extracted from closed workbooks to the roster

2. Be dynamic, meaning it will change automatically if dragged or copied based on the Name and Date.


Any help will be most appreciated.

 

[Luke M]

This is the best trick I've seen for getting values from a closed workbook:

http://www.rondebruin.nl/copy7.htm


You could certainly set up some sort of looping macro or something that would let at the different cells and their respective values and use that as the inputs to the GetRange macro so that different Names/Dates would fetch different things.