Using indirect

[Pickygame]

I am using indirect to select a sourcefile containing data and use this set of event subs in to open the dependent file when the workbook is opened, and to close the dependent file when the workbook is closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.Windows(" Anonymised Data.xlsx").Activate
    ActiveWindow.Close
End Sub

Private Sub Workbook_Open()
    Workbooks.Open Filename:= _
        "D:\ Anonymised Data.xlsx"
    Application.Windows("TSCF_Loan_Application_2010_ver1.xlsm").Activate
End Sub

My problem is that the original file (TSCF_Loan_Application_2010_ver1.xlsm) is a read only file. When it is used we save the file with another file name such as 1234 abc.xlsm. This is then available for future reference and is easily identified. However when it is reopened excel looks for TSCF_Loan_Application_2010_ver1.xlsm and not the newly named file.

How can i solve this?

Thanks

[TMS]

You could set a flag somewhere in the "master" workbook to indicate that the file has been saved and anonymised.

When you open the master workbook, check the flag; if it is not set, look for the Read Only file; if it is, look for the anonymised file.

Regards

[Marcol]

It might be useful to give an example of your INDIRECT formula.

[Pickygame]

I have supplied the two workbooks.

The Conaccess Anonymised Data file is the Source File.

The TSCF Loan Application Ver1 has 3 sheets. A data files sheet that i use to select the location of the source file used for the Indirect function, an intro sheet that users populate first. This is where the main Indirect formuals are and an Initial inquiry sheet that is used for completing the form. I have stripped out the rest of the worksheets

Thanks