Doing find and replace in links on two spreadsheets

[BRONXTARHEEL]

Every month I have to complete a spreadsheet called Trends. I pull data by linking to a spreadsheet called Share and subtracting year to date data from year to date for the previous month.
For example in May on Trends, I enter a link share\ytd May\$D$27 - share\ytd April\$D$27.
And then in June and July, etc it is the same link, subtracting year to date data for the previous month from current YTD data.
Question is whether I could do a find and replace on the link, for example find May and replace with June
Thank you to all

SRM
NC USA

 

[Smallman]

Hi Bronxtarhee

Best not to link the two workbooks at all. Best to have a sheet originally titled PriorMonth and paste the data in this tab each month. Your process on a monthly basis would be to Cut Prior month out of its sheet put a new sheet in and call it say April, paste the data in there. Now Paste May's data in prior month and your summary will be updated.

Linking workbooks is problematic and I have found it much easier to create a robust process that you follow such as the one above. Keeps it simple and avoids problematic linking of workbooks.

Take care

Smallman

 

[BRONXTARHEEL]

Dear Smallman,
Thank you for your comments. I am beginning to think the answer is to combine the spreadsheets and enter data within just once on one spreadsheet. I need to prepare one sheet for each file for a month-end report and it should be easy to just print two worksheets and keep everything in one file. In fact it allows me to put a reconciliation factor in the spreadsheet to make sure I am in balance.

I have to convince my boss because he designed the whole process and doesn't like changes.

Thanks again, turns me onto a good idea.

SRM
North Carolina USA