Editing links between worksheets..!

[daddydavid]

I have several excel files inside one folder lets say named employee 1 to employee 7. In book 8 cell A1, I link to several cells in the employee 1 book. In book 8 cell B1 I need to link to all the same cells as in cell A1 but from the next workbook employee 2. I can cut and paste the link from A1 to B1 and then manually edit all the employee 1's and change them to employee 2. But there must be a quicker way of doing this does anyone know.......Thankyou....Dave

 

[GlennUK]

Edit/Links ( or Prepare/ Edit Links ), and change source.

 

[daddydavid]

Hi edit links only works with all the links on the entire sheet, it is not cell specific, or am I incorrect...? Sorry let me try to explain. Book 8 cell A1 links to several locations in a worksheet named employee 1. Book 8 cell B1 needs to link to the same locations but in a different worksheet named employee 2.

=SUM('\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$L$27+'\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$L$28+'\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$L$29+'\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$L$30+'\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$L$31+'\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$U$27+'\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$U$28+'\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$U$29+'\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$U$30+'\\davidturkhud\Users\David\Desktop\Wages 11-12\G&P Transport\[Employee 10.xlsx]P11'!$U$31)

The above is the formula in cell A1, all I need to do is the same formula in cell B1 but change the link to [Employee 2.xlsx] easy enough to do manually but can it be done automatically......Dave

 

[GlennUK]

In that case use edit/replace on the range of cells whose link you want to change.

 

[mancemonster]

Copy the original formula into the equired cell.

Highlight the cell with the formula (and at least one other), and use find/replace.

Employee 1
with
Employee 2

As they are all saved in the same folder, this is the only element of the link which needs to be changed, as long as the sheets are in the same format... It should work..

Regards

Roger

 

[daddydavid]

Thankyou very much roger....Worked a treat......