Changing Huge Numbers of Hyperlinks (formulas)

Hi Everybody!

Well, i´m with a big problem and only you could help me!
I´ve got a main workbook (excel 2007) with a main spreadsheet where i group all the information (text and values) about brands and models linked to 10 workbooks (brands) with several spreadsheets (one spreadsheet=one model).

On the main spreadsheet i´ve got 16 lines for each model which they have the follow formulas, wich are linked to other workbook / spreadsheet (Mar/Gato)

1)

IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE));"";VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE))

and will return text values


2)

IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;82;FALSE));"";VLOOKUP(V844;[Mar.xlsx]G!$A$3:$CE$78;82;FALSE))

And will return number values


3)

IF(M844<>"";HLOOKUP($V$3;[Mar.xlsx]Gato!$A$2:$CE$78;2;FALSE);"")

And will return number values
The return value (;2 goes from 2 to 16.


This process repeats to all the 10 workbooks e and 250 spreadsheets

4) Table Array of the model spreadsheet

With regard to formula 1) and 2)

The problem is that the table array (on the model spreadsheet) could have diferent dimensions (becouse the launch year of the model) like this:

A$3:$CE$78 (2009 - Launch year)
A$3:$BR$78 (2010 - Launch year)
A$3:$BE$78 (2011 - Launch year)
A$3:$AR$78 (2012 - Launch year)
A$3:$AE$78 (2013 - Launch year)

And the return column (value) for formula 1) is always ;3;

And the return column (value) for formula 2) are like this:

;82; (2009 - Launch year)
;69; (2010 - Launch year)
;56; (2011 - Launch year)
;43; (2012 - Launch year)
;30; (2013 - Launch year)
;17; (2014 - Launch year)

The diference between above values is 13, it corresponds 12 months and the total column (12+1)

For the formula 3) the table array its the same, the only difference is instead of starting A$3, starts in A$2

5) The update of the formulas 1) 2) 3) for 2014

I´m doing this manually...(its a punishment!!)
The formulas are updated like this,for 2013, i´ve got table array A$3:$AE$78 and the return value (;17, then for 2014 i will have a table array A$3:$AR$78 and the return value will be (;30, and so on



5) Change the network drive

For other reasons i´ve to change my files to another network drive, something like this:

Q:\KPI\2014\Main File

6) Main Goal

Well, my knowledge of programimg in VBA / Macros is very poor that´s why i´m asking for your help.
I was thinking using Excel's regular Find and Replace feature to change:


- The network drive designation
- the table array area
- The return value

But i don´t know if this process ( Find and Replace) is safe and clean.
I´m doing this manually and I´ve got on the main worbook/sheet 4000 lines with 3 formulas its 12000 operations!I´m on the 350 line....!
But, I remenber there are here in the forum great experts in macros that could know how to resolve this problem!
The main goal is to update the all formulas (links) to "brand" workbooks and inside "model" spreadsheets automatically with a macro.
It is possible?

Kindly Regards

Nuno

Sixthsense,

Becouse i was running without time , i did the Find and Replace procedure.
Thanks for your concern.
Nuno