Hyperlink Sheet Reference Changes

[liquidmettle]

Hello Everyone,

I have a dilemma: I am creating a data entry dashboard where there will be a worksheet for each month. I set up January exactly as I will want each sheet and then proceeded to duplicate it for February.

The problem I have is that all the hyperlinks in February's tab still link back to January. Each day of the month has a hyperlink to quickly jump to its data entry area (and you can imagine I don't want to fix 334 hyperlinks one at a time).

Is there any quick and easy way to change the hyperlink sheet reference without changing the ranges?

If this could be done by macro that'd be great, unless there is another solution.

Thanks!

[FDibbins]

Have you considedred using only 1 sheet for ALL data entry, and then use other sheets for your summaries/extracts etc? This will keep data entry simple, and enable the use of some of the powerful analysis tools in excel (like Pivot tables)

[liquidmettle]

Hi FBibbins,

I'm changing the post I had here because you did inspire a better way to organize the data.

I'm still going to keep the sheets separated, but I'll revert back to my original question about the hyperlinks.

You've definitely saved me a lot of time and headache with your inspiration though!

[liquidmettle]

I deleted this post because I figured out my subsequent question.

[liquidmettle]

I have figured out a workable solution and am marking this thread solved.

Using concatenate, I combined all components of each hyperlink into the hyperlink formula then copied the range, paste special-ed as just data, and now I can run a Find & Replace on just the sheet's name and it will make each of the hyperlinks go to the desired range on each sheet.