Help setting up an formula to automaticaly pull data from a spreed sheet when I sahve it.

[BTM88]

Right now for work I get an email every morning with two Excel documents, they are for employees numbers from the day before. What I need to do is pull certain data for some employees row, we do not use all of the data that . I am making an excel sheet for each employee tracking there work numbers day to day. What I am using now is vlookup so my formula looks like
=VLOOKUP("AGENT SUE", 'C:\Users\STATION01\Desktop\EXCEL SPREEDSHEETS\DATA\MARCH 2013\DAILY UNAVAILABLE\[mar-20.xlsx]Sheet1'!$A$1:$BK$59, 15, FALSE)
This makes it so I do not need to type in th numbers every day but I still need to paste the formula and change the file it pulls from. which would not be to bad put each of the 80 some employees has about 10 data points i need to pull.
So wheat I was wondering is that if there is any way that I could make a loop so that, when I save it to a date, the spreed sheets no automatically to pull from the saved numbers for each new day.

 

[sijpie]

There are several ways in which this can be improved.

The best method will be to (have someone) write a macro that gets the new date and then pulls all the data you need from the sheets and places them where you want it. It will be difficult to get the correct result just over this forum if you are not familiar with macro writing, and I would suggest to see if your boss is willing to have someone develop such a macro. The whole process could take you then less than a minute each morning, leaving plenty of time for you to do other things. So the cost will earn themselves back in no-time.

The other option is to make some simpler macros that semi-automate some of the things you do, such as copying down formulas. But that still leaves a fair amount of work, and is possibly more prone to errors.