Replace Link

[Annette]

I have 83 workbooks with 5 sheets per book and 21 links per sheet - and I
need to replace - (basic link - ='C:\Users\Annette\Desktop\Annette Daily
Weekly\Daily\August\[1Charge Sheet.xlsx]Mon'!$B$12) - I need to replace the
month in every link. I know I can go page by page with find and replace but
I would like to know if there is any way to replace book by book. That would
mean 83 changes instead of over 400 - I am also very open to any suggestion
that would make this faster and easier. Thank you for any help you can give
me.

 

[Dave Peterson]

How about using:
Edit|links|Change Source

I have 83 workbooks with 5 sheets per book and 21 links per sheet - and I
need to replace - (basic link - ='C:\Users\Annette\Desktop\Annette Daily
Weekly\Daily\August\[1Charge Sheet.xlsx]Mon'!$B$12) - I need to replace the
month in every link. I know I can go page by page with find and replace but
I would like to know if there is any way to replace book by book. That would
mean 83 changes instead of over 400 - I am also very open to any suggestion
that would make this faster and easier. Thank you for any help you can give
me.

 

[Annette]

Thank you for coming back to me with a possible solution - BUT - I think I
need to explain better -
I have 83 different people - each person has one workbook per month with 5
sheets (one sheet for each possible week) and on each sheet are 5 "boxes"
(one for each day of the week) with cells that are linked to a daily charge
sheet - there is a total of 21 links per sheet. This allows the information
on each daily charge sheet to flow to the correct person's workbook. I have
standardized all the Charge Sheets and Workbooks so that all I need to do is
change the Month in the Linking Formula. But right now I have to go into
each workbook and change the Month on every sheet - this is over 400 (83x5)
times. I am hoping to find a way to change the month on each page (in each
workbook) without having to do it page by page - perhaps changing the entire
workbook all at once??? Is this possible???

How about using:
Edit|links|Change Source

I have 83 workbooks with 5 sheets per book and 21 links per sheet - and I
need to replace - (basic link - ='C:\Users\Annette\Desktop\Annette Daily
Weekly\Daily\August\[1Charge Sheet.xlsx]Mon'!$B$12) - I need to replace the
month in every link. I know I can go page by page with find and replace but
I would like to know if there is any way to replace book by book. That would
mean 83 changes instead of over 400 - I am also very open to any suggestion
that would make this faster and easier. Thank you for any help you can give
me.

 

[Dave Peterson]

When you tried edit|links, did you have to use it for each worksheet in each
workbook?

Thank you for coming back to me with a possible solution - BUT - I think I
need to explain better -
I have 83 different people - each person has one workbook per month with 5
sheets (one sheet for each possible week) and on each sheet are 5 "boxes"
(one for each day of the week) with cells that are linked to a daily charge
sheet - there is a total of 21 links per sheet. This allows the information
on each daily charge sheet to flow to the correct person's workbook. I have
standardized all the Charge Sheets and Workbooks so that all I need to do is
change the Month in the Linking Formula. But right now I have to go into
each workbook and change the Month on every sheet - this is over 400 (83x5)
times. I am hoping to find a way to change the month on each page (in each
workbook) without having to do it page by page - perhaps changing the entire
workbook all at once??? Is this possible???

How about using:
Edit|links|Change Source

I have 83 workbooks with 5 sheets per book and 21 links per sheet - and I
need to replace - (basic link - ='C:\Users\Annette\Desktop\Annette Daily
Weekly\Daily\August\[1Charge Sheet.xlsx]Mon'!$B$12) - I need to replace the
month in every link. I know I can go page by page with find and replace but
I would like to know if there is any way to replace book by book. That would
mean 83 changes instead of over 400 - I am also very open to any suggestion
that would make this faster and easier. Thank you for any help you can give
me.

 

[Annette]

yes - in fact it took me to a window that opened the "computer", giving me
options of those files and documents - there was nowhere I could type in what
changes I wanted to make

When you tried edit|links, did you have to use it for each worksheet in each
workbook?

Thank you for coming back to me with a possible solution - BUT - I think I
need to explain better -
I have 83 different people - each person has one workbook per month with 5
sheets (one sheet for each possible week) and on each sheet are 5 "boxes"
(one for each day of the week) with cells that are linked to a daily charge
sheet - there is a total of 21 links per sheet. This allows the information
on each daily charge sheet to flow to the correct person's workbook. I have
standardized all the Charge Sheets and Workbooks so that all I need to do is
change the Month in the Linking Formula. But right now I have to go into
each workbook and change the Month on every sheet - this is over 400 (83x5)
times. I am hoping to find a way to change the month on each page (in each
workbook) without having to do it page by page - perhaps changing the entire
workbook all at once??? Is this possible???

How about using:
Edit|links|Change Source

Annette wrote:

I have 83 workbooks with 5 sheets per book and 21 links per sheet - and I
need to replace - (basic link - ='C:\Users\Annette\Desktop\Annette Daily
Weekly\Daily\August\[1Charge Sheet.xlsx]Mon'!$B$12) - I need to replace the
month in every link. I know I can go page by page with find and replace but
I would like to know if there is any way to replace book by book. That would
mean 83 changes instead of over 400 - I am also very open to any suggestion
that would make this faster and easier. Thank you for any help you can give
me.

 

[Dave Peterson]

I've never seen excel show me that dialog for each worksheet in my workbook--I
think we're miscommunicating.

And after you click the "change source" button, you'll see a dialog where you
can choose what should be the replacement file for the file in the link you
selected. You don't type it at all.

But you should not be prompted for each worksheet in your workbook.

yes - in fact it took me to a window that opened the "computer", giving me
options of those files and documents - there was nowhere I could type in what
changes I wanted to make

When you tried edit|links, did you have to use it for each worksheet in each
workbook?

Thank you for coming back to me with a possible solution - BUT - I think I
need to explain better -
I have 83 different people - each person has one workbook per month with 5
sheets (one sheet for each possible week) and on each sheet are 5 "boxes"
(one for each day of the week) with cells that are linked to a daily charge
sheet - there is a total of 21 links per sheet. This allows the information
on each daily charge sheet to flow to the correct person's workbook. I have
standardized all the Charge Sheets and Workbooks so that all I need to do is
change the Month in the Linking Formula. But right now I have to go into
each workbook and change the Month on every sheet - this is over 400 (83x5)
times. I am hoping to find a way to change the month on each page (in each
workbook) without having to do it page by page - perhaps changing the entire
workbook all at once??? Is this possible???

:

How about using:
Edit|links|Change Source

Annette wrote:

I have 83 workbooks with 5 sheets per book and 21 links per sheet - and I
need to replace - (basic link - ='C:\Users\Annette\Desktop\Annette Daily
Weekly\Daily\August\[1Charge Sheet.xlsx]Mon'!$B$12) - I need to replace the
month in every link. I know I can go page by page with find and replace but
I would like to know if there is any way to replace book by book. That would
mean 83 changes instead of over 400 - I am also very open to any suggestion
that would make this faster and easier. Thank you for any help you can give
me.