SOLVED

Updating month contained in link without changing the rest of the existing link.

Copper Contributor

Each month, I prepare financial reports on all current projects. We copy the workbook from one month to the next so initially the report contains the info from the previous month. I then have to update all of the links so that the data is drawn from current information. The only thing that changes is the month.

So, the link to my data starts as

='M:\Cash Flows - Budgets\2023 NOVEMBER\[4. NOV SOUTH Project Cash Flow.xlsx]Alexandria, VA'!G71

IT NEEDS TO BE:

='M:\Cash Flows - Budgets\2023 DECEMBER\[4. DEC SOUTH Project Cash Flow.xlsx]Alexandria, VA'!G71

There are around 100 links following this same format. Copying and dragging is helpful, but it would be great if there was a more efficient way to perform this task. HELP!

5 Replies

@SukiLuLu 

 

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?

best response confirmed by SukiLuLu (Copper Contributor)
Solution
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.

@SukiLuLu I agree with the previous comments that it is not the best practice to have a sheet with external links, particularly that many. 

A better approach would be to import the data using Power Query, and then you could change the source file path each month, you could even use more advanced Power Query techniques in having the source file path in a named range cell. 

 

Other simpler options, but not as effective or considered best practices would be to use replace all as previously suggested. Or go to data menu and click on Edit Links and Change Source, this could work well if you only have 1 or not too many sources, but would be definitely easier than updating 100 links manually. 

@mtarler 

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 :stareyes:.

 

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.

@SukiLuLu 

 

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.)

1 best response

Accepted Solutions
best response confirmed by SukiLuLu (Copper Contributor)
Solution
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.

View solution in original post