Batch Editing Hyperlinks in Excel

Question

I have a spreadsheet with weekly expense reports on ~52 tabs.  Easch tab is labelled with the "week ending" date.  I have another tabbed "Summary" sheet with a summary of the total of each sheet so I can track what each total is, when it is submitted for payment and when payment is reimbursed.  The date ("week ending" entries) in my "Summary" sheet are each hyperlinks that refer to the corresponding expense report sheet for that date.  Everything works well until the end of the year, or more correctly, when a new year starts.  I then have to go to each tab and modify the week ending date to the new one, and then go to my summary sheet to change each hyperlink.

Is there a way I can globally or batch edit the hyperlinks to all be updated to the new tab names without having to edit each hyperlink manually?

Answer 1

What are the chances that you could send me a copy of the workbook directly as an email attachment?  It would be treated as confidential.  If it's not over about 18MB it should fit through the mail system.

If you can, send it to (put this all on 1 line to make a valid email address)

HelpFrom

@

JLathamSite

.

com

Just let me know in the body of the email that you are Argee7 at this site, and maybe a link to this discussion.  That link would be:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/batch-editing-hyperlinks-in-excel/aa466a24-b807-4300-b458-c865f3db3b23

It shouldn't take long at all to provide a solution.  I need to see the overall structure of the workbook to include all sheets in it and other things I haven't thought of yet that would take a lot of back-and-forth questions and answers to get running with.

Answer 2

So how does what I sent to you work now?

Answer 3

The screen capture causes the message to be too long.

Edit Hyperlink dialogue box:

Link To: Place in this document

Cell Reference A1

Select a place in this document: '26-01-14'

Answer 4

Thanks for your reply JL.

I've done a screen capture to hopefully show how I am doing the hyperlinks,  The particular tab name I am looking at for this example is "26-01-14".  Quotation marks have been added for this document only.   (The screen capture apparently made the message too long so I'll submit this and follow up with the screen capture if I can.)

When I hover over the hyperlink, the bubble comes up reading:

        file:///D:\Expenses\Expenses-2014.xlsx -

        '26-01-14'!A1

Column A of my summary sheet is headed Week Ending (weeks end on Sundays)

Row 2 is the first date (05-01-14) and sequentially on to the end of the year with one tab per week.  Most of my expenses are in CDN dollars.  I do have some weeks when I have US expenses also.  Last week was such a week so that tab is labeled "09-02-14 (US)"

Answer 5

It could probably be done with a small macro - one question would be how do the dates appear in the summary sheet and how do they look as part of the tab name.  Need examples of both exactly as they appear in your workbook.

Also need to know what column the dates are in on your Summary sheet and what row they start on.

Finally, need to know how the hyperlinks are created on the Summary sheet; did you:

Select each one and use the 'Hyperlink' (or Edit Link) function to then select the appropriate weekly sheet,

or do they use a

=HYPERLINK()

formula?