Forum Discussion
Updating month contained in link without changing the rest of the existing link.
- I completely agree with John's comment above and highly recommend you discuss with him ways to improve the sheets and whole process. A singly input sheet and creating output tables/reports/pivot tables ect... can not only make this ongoing process much better, more efficient but may let you do analysis over months that you never realized you could do.
That all said, the simple answer to your question (although I almost think I shouldn't tell you so you take the above suggestions more seriously) is to use Search and Replace and click replace all. In your example a search and replace all for:
2023 NOVEMBER\[4. NOV SOUTH
replace with
2023 DECEMBER\[4. DEC SOUTH
should be specific enough to never have any accidental changes but you could just include more of the link to be sure.
Excel has a lot of ability to extract monthly data from a single ongoing database of transactions on such things as different projects. You've said enough that I want at least to suggest that one far more efficient way to do this would be to re-design the whole process so that you're not having to create and modify a new sheet for each month.
Is that possible? Are you in a position to make such a change?
The mere fact that you now have to change around 100 links each and every month suggests that not only is this inefficient; it's also potentially a recipe for disaster. What happens if you miss one of those monthly formula changes, or mistype it in such a way that it still produces a result but an erroneous one?
- I completely agree with John's comment above and highly recommend you discuss with him ways to improve the sheets and whole process. A singly input sheet and creating output tables/reports/pivot tables ect... can not only make this ongoing process much better, more efficient but may let you do analysis over months that you never realized you could do.
That all said, the simple answer to your question (although I almost think I shouldn't tell you so you take the above suggestions more seriously) is to use Search and Replace and click replace all. In your example a search and replace all for:
2023 NOVEMBER\[4. NOV SOUTH
replace with
2023 DECEMBER\[4. DEC SOUTH
should be specific enough to never have any accidental changes but you could just include more of the link to be sure.I received a number of terrific suggestions, but at this time and place I'm looking for a somewhat quick solution. Of them all, your solution was the least terrifying (I have nightmares about this workbook). To be safe, I made a copy of the gnarliest of my spreadsheets to try your find/replace method. It turns out that sheet alone has 527 links! In two clicks, every one was updated. It was that simple! I can't thank you enough
.To everyone who took time out of their busy day to respond to my question, I send sincere thanks and wishes for a wonderful New Year.
I'm glad that the suggestion by my friend Matt ( mtarler ) worked for you. Now that you've reported that there are 527 such links that need to be changed each month, you're underscoring the shared concern that Matt, MelinaLouzada , and I voiced.
It's no surprise that you have nightmares over this workbook; it sounds like something that has mushroomed over years, becoming both a figurative and actual nightmare to maintain. And, as such, especially given that it involves financial reporting, you (your organization) really should be talking with an expert--I am NOT volunteering myself; I retired over 20 years ago, and just answer questions here, from time to time, to offer what little expertise I might have.
When I say "talk with an expert" I have in mind retaining a consultant in person, somebody who knows how to design an effective and efficient workbook for collecting and reporting financial data. You do NOT want to have your organization become a case-study in how over-reliance on a bad spreadsheet design created a financial disaster. That sort of thing has happened. (That is an advertisement for a company that does spreadsheet design, but it's reporting actual events at major firms and they are good cautionary accounts.)
