Update formula based on cell selection

Hi All,

firstly, apologies if this has already been asked but each time I try to search, I get a web error

I have a data validation in cell A2 which contains a list of people initials. In cells A4:IM12 I have a formula linking to an existing workbook on my network. Each member contained in A2 has their own workbook called [Initials] Task Planner.xls

What I am trying to do is each time a name is selected in A2 is for the formulae in A4:IM12 to automatically update so that the cells point to the correct workbook.

Any suggestions on how to do this please as it has me completely stumped.

Cheers in advance!

Mav

The easiest way would probably be to effectively automate the Edit Links function. Do you have any other links to external files in the workbook?

Then you'll need to store the last Initials value, either in a cell or in a named range. You can then use a Worksheet_Change event to compare the new value of the cell with the last set of initials chosen, and update the links if required. Something like this:

Please Login or Register  to view this content.

Change this line:

Please Login or Register  to view this content.

to use A3 instead.

I forgot to mention that you need to define a name called LastInitials and assign it the value of the initials currently in use by your formula.