Change formula look up string (Excel 2003)

I have attached a sheet that i am working with.

Say i have 5 depots each that have this sheet which they put there information, and they will be creating a new sheet each week to put the weekly data onto.

I would then have a master sheet that would grab the data from each of the 5 depots sheets and will have all the information in one place (saving the whole copy and paste from each report.)

This is simple enough to do if this was for just one week.

What i would like to do to save the time of having to (CTRL+F) "replace" Week 1 to Week 2.

Is the a VBA or some sort of automatic macro that would change this for me.

for example if i have week 41 in A2 all the formulas would have the correct file path and would end with week 41 and on new week i changed A2 to week 42 it would then change end of the formula to week 42 and this would then update the report accordingly?

Is this at all possible or am i trying to pull of a bit more than excel can chew.

Thanks for any help in advance.

You said A2, but I assume you mean B1. Can you just use =WEEKNUM(NOW())?

Not sure that there would be much difference, but if I only want the date, I use TODAY(), and use NOW() if I need to include the date and time. It can make a difference if you are looking up a specific date.

For instance, assuming the date today is 11/12/14 and you have a range of cells with dates, 1 of which is 11/12/2014...
=VLOOKUP(TODAY(),M16:N18,2,0) will return the corresponding value from column N
=VLOOKUP(NOW(),M16:N18,2,0) will return an error because of the decimal places that NOW() returns

Fine, can you just use =WEEKNUM(TODAY())? Same results.

Sorry, natefarm, didnt mean to come across like I was putting you down, just making an observation and offering a suggestion

And I appreciate it. Sorry, for my reacting wrongly. I actually thought your post came from the initial requestor, and that he was getting picky about details but not answering my question. I can definitely see your point. Thanks for the better suggestion.

Hello sorry i have not replied have been testing this which works for selected data gathering but not for what i am trying to do i will try and explain in more detail of what i am trying to do and what is required.

Yes B1 is the cell which has the week number.

So i have a selection of folders.

Crew Details - Year - Month - Spreadsheets for weeks during that month.
Depot Details - Individual Depot - Year - Month - Spreadsheets for weeks during that month.

So in the crew details this will be where the master sheet is so on here i manually input Row A to Row C.
The depot details for this week would then grab these and then the individual depot will update the rest of the information for there set depot.
The crew details should then automatically grab the depot details and update.

for example
Crew Details - 2014 - October - Crew Details Week 41
Depot Details - Norwich - 2014 - October - Norwich Crew Details Week 41.


So what i would like the report to do which not sure if would be B1 or B2 that it would need to look up.
But all the cells will look up to the direct file.

e.g s:/Crew Numbers/Depot Details/Norwich/2014/October/Crew Details Week 41.xls

So if we change to Week 45 which is in November i would need the look up to change the month to November and the week number from 41 to 45.
and again for it to also do the year look up as well so when we go from 2014 to 2015 it changes that as will update that in the lookup.

Thanks again for any help in advance.

I am confused about many things in your request. However, the following will open two workbooks in two folders based on the date in B2 and the path and filenames you have described, and make them available for further processing. Perhaps you can take it from there.

Option Explicit
Dim DelDt As Date, WkbkFullPath As String, DepotName As String
Dim CrewWkbk As Workbook, DepotWkbk As Workbook
Const CrewPath = "s:\Crew Numbers\Crew Details\"
Const DepotPath = "s:\Crew Numbers\Depot Details\"

Sub ProcessWorkbooks()
    DelDt = Range("B2").Value

    WkbkFullPath = CrewPath & Format(DelDt, "yyyy") & "\" & Format(DelDt, "mmmm") & _
        "\Crew Details Week " & Format(DelDt, "ww") & ".xls"
    On Error GoTo FileNotFound
    Set CrewWkbk = Workbooks.Open(WkbkFullPath)
    On Error GoTo 0

    DepotName = "Norwich" ' Change this to extract the name from wherever it is
    WkbkFullPath = DepotPath & DepotName & "\" & Format(DelDt, "yyyy") & "\" & Format(DelDt, "mmmm") & _
        "\" & DepotName & " Crew Details Week " & Format(DelDt, "ww") & ".xls"
    On Error GoTo FileNotFound
    Set DepotWkbk = Workbooks.Open(WkbkFullPath)
    On Error GoTo 0

    ' continue with code to process workbook data

    Exit Sub

FileNotFound:
    MsgBox Err.Description
End Sub

Thanks Natefarm i will try this later and see where this gets me.

Again thanks for your help and support.