Workbooks Changing Reference Locations

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
90
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
Just save the 'Master' file then use the VBA method SaveCopyAs for the day backup so nothin' to edit obviously …​
 

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
90
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
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.​
 
Solution

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
90

ADVERTISEMENT

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

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 23, 2017
Messages
90

ADVERTISEMENT

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

Board Regular
Joined
Nov 23, 2017
Messages
90
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 23, 2017
Messages
90
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.
 

Forum statistics

Threads
1,148,282
Messages
5,745,836
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top