Workbooks Changing Reference Locations

[PlumGr8]

So, hope this is my last issue for a while. So i have one main "master" workbook that feeds information in to several other workbooks. Issues seems to be that every day we save the "master" file, then "save as" and name is the date & time, ex. Master 20210101 0600. Issue seems to be that if any of the other workbooks are open, that they will now automatically change their source from the "master" to the "Master 20210101 0600" workbook. Is there a way to edit the link so that they DO NOT change or update? Thanks.

 

[Marc L]

​

Just save the 'Master' file then use the VBA method SaveCopyAs for the day backup so nothin' to edit obviously …​

 

[PlumGr8]

Issue with that is i have to keep it as simple as possible. And it needs to remain close to the same procedure that dozens of people have been doing for a decade. I know it sounds odd, just what i have to work with is all. Just didn't know if there was a setting that would prevent the other workbooks for automatically changing their sources.

Only other solution, if its possible. Is there a way to create a macro button, that once we just click "save" to save it currently, that i can then hit a button that says "save as" and have it save with the correct name date and time stamp and in to the correct location?

 

[Marc L]

​

Yes it is what I stated : a VBA procedure saving the 'Master' workbook and creating the day backup via SaveCopyAs method.​

Another way could be to intercept the saving if other workbooks are still opened with a warning message "You must close other workbooks !" …​

​

Without any VBA procedure you must change all the formulas in other workbooks by using INDIRECT with the workbook name in a cell for example.​

 

[PlumGr8]

I love all the stuff VBA can do, i just have no experience on how to write stuff in VBA unfortunately. But also you mean on those other workbooks, if i use the INDIRECT function in those linked formulas, than they will not alter from the source i reference them too? I've seen the function, i just haven't used it personally.

 

[Marc L]

​

As the workbook reference will not be in the formula but in another cell as text …​

​

But let's wait if someone else has another idea …​

 

[PlumGr8]

Ok so

​

As the workbook reference will not be in the formula but in another cell as text …​

​

But let's wait if someone else has another idea …​

Ok so i just tried adjusting the formula with the indirect function and that as of now seems to be working as needed. I have about 10 sheets to fix, so I'll probably just do it to one, then copy and move it to each sheet and just change the one sheet reference number. It's a little me time for me, but should be the easiest most seamless way to accomplish it in the end. Thanks for the help.

 

[PlumGr8]

Ok, so another question. I used the indirect function and the worksheet in the other workbook and seems to be good. So i decided to proceed with the others, opened the next workbook and went to change the formula and now these other workbooks aren't allowing me to change. If i do =indirect("'[link to cell') etc its just showing the formula in the cell, its now actually showing what the cell is displaying. And when i try to revert it back to the regular formula that was in there it doesn't take that either? Any idea why other workbooks won't link back to the master source anymore?

 

[Marc L]

​

As I can't see it's difficult to foresee anything …​

​

You should post your previous formula and the new INDIRECT one in order any helper might see the light …​

 

[PlumGr8]

Original forumla

='[Master Status.xlsm]sheet1'!D6

New

=INDIRECT("'[Master Status.xlsm]sheet1'!D6

If i open the workbook and don't do anything, all the cells still work, as soon as i try to change anything it no longer will update.