Using VLOOKUP across multiple files

[Dave T at home]

I have weekly files for the teams that manage.

I keep a members list containingg columns of: name (first and last); Month
Week 1 Date, Team selected for and when player confirmed; Month Week 2 date,
team selected for and when player confirmed, etc for the month.

I have a second file containing each team and the names of who has been
selected for what team.

I am currently using a vlookup to get the required information from file 2
workbook and puting it into file1 as required.

=IF(A23="","",VLOOKUP(A23,'C:\Documents and Settings\Dave and Alison\My
Documents\Hockey - Men\League - 2005.6\League\[Team Sheet 05.11.19 -
1.xls]Work Sheet-1'!$A$1:$C$134,3,FALSE))

This works OK but I have to change the formula for every week of the month
and for every month. I have to change the date of the team sheet from e.g.
Team Sheet 05.11.19 to Team Sheet 05.11.26.

I have looked at a previous question that suggests placing the workbook name
in a cell and reference the cell. I have placed the file/workbook name ih
cell H2 and changed the formula to:
=VLOOKUP(A3,INDIRECT("'"&H2&"'!$A$1:$C$134"),3,0)

This works but the file needs to be open for the data to be retrieved or I
receive an error.

I would like the previous weeks / months data to be available so I can have
a history of which team a player has played for without opening very file.

Can anyone help me to refine / update / make better the original formula
which means I don't have to edit teh formula every week?

Your help would be appreciated. Sorry for the long winded explanation but
ir felt the easiest way to explain what I wanted.

Dave

 

[Don Guillett]

You can use find/replace. If often, set up a macro and assign to a
button/shape