I THINK i've been naughty, as I have just put this into the FORMULA thread, BUT I think that this could end up having MACRO in this?????

As the title says - I have one sheet which is used daily, but this will have data in certain cells which will be pulled from different work books. Reason for this, some idiot in work thinks its a great idea to the same job twice, but I don't like the idea of spending half an hour to an hour finding all this data. So I would like to do this automatically!

BUT...... (This is not as simple as putting a formula link into a cell)

I have different variables which will determine where and what files to look in!
This may even have to be macro rather than formulas????

Say for Arguments sake my main sheet which will collect all the data will be called "Data collection" And the current WK number is 50

1. Some files will ALWAYS be in example: C:Documents\work\Audit ......... But the sheet name will change weekly. If Its week 50, it will look on TAB "WK50" and this week number will be either: Be a WEEK NUM formula in A2 or I will write WK50.

In this sheet I will have to then look up a figure at the bottom of the sheet, depending on the day I will have to lookup a certain cell, I have a macro on this sheet that opens up a certain page on a certain day/time, so I might be able to modify this to do this part?????


2. Some links will ALWAYS be in the same cell, but the file/workbook name will vary by week. E.G if the week is week 50, then the file will be called WK50. Again this figure will be in A2 on the collating sheet. So this link would kind of look like C:\Documents\Work\Waste\"THEN THE VALUE IN A2" as the folder will be called WK50

3. Some links will ALWAYS be the same file name, but the TAB in this sheet will be a shift name and a date. E.G "3 DAYS 19.12.2012" these two name parts will be also written on this sheet as a reference. "3Days" would be in A3 and the date will be in A4.

4. Exactly as above but, depending on the DAY will look up a certain cell range. E.G Friday will open the above sheet, and look up E1 Saturday will look up E2 Sunday E3

And to top all this off!!!! When I have tried doing links to other workbooks, The collating sheet needs to have the VALUES pasted in! as I have had the actual formula pasted in last time I tried and obviously them figure don't exist in the current workbook.

This also shouldn't make a difference but, it's on a network, so the C: I have been using as an example, won't be a drive name, it will start off as something like CALSQ, which shouldn't cause a problem????

Any one of these answers will help a great deal, I can link basic things up, but this has alwyas been live data, but this is totally different which is now new to me!

Thanks.