How to cross reference to a closed workbook with Excel function or VBA?

[Justic3G]

I am kinda new to VBA, appreciate your help on the subject.

So, I would like to use a User Defined Functions (or better if can be done with Excel function), to achieve the following cross reference:
[Book1, Sheet1, Cell A1] formula is something like UDF([Book2, Sheet2, Cell B1]), and it returns the sheet name of the cross reference worksheet, in this case [Sheet2], while Book2 is closed.
(While in Book2, Sheet2, Cell B1, the formula will be something like UDF([Book1, Sheet1, CellA1], and it returns Sheet1.)

If Book2 is opened, I can just returns the cross reference sheet by using the [selected cell].Parent.Name, to Book1. But it returns #VALUE! if Book2 is closed.

 

[dnorm]

Hi Justic£g

While in Book1, on the Excel ribbon under "View" on the far right is "Macros" there click "Record Macros" it will ask you to the function a name or you can leave it as "Macro1" (the default).
Then perform everything you need to do (open Book2, do your referencing, and close Book2)
Then in Book1 again: View, Macros and Click on "Stop Recording"
Then click on view macros and select the one you created.
Add the "Application.ScreenUpdating = True/False" to the start and end.

How to get your macro to run.
I do not know how to the "auto" run on update (sorry)
Create a text box (this works with shapes too) in Book1 near where you want the results (or at the top the sheet). Enter any text you like (i.e. "Run Cross Reference") then right click and "Assign Macro..."

The Macro you create should look something like this:

Sub_Macro1

'This is where to add "Application.ScreenUpdating = False"

Workbooks.Open Filename:=("Book2")     'the file path to Book2 will appear here

Windows("Book1.xlsm").Activate    'this will be you switching back to Book1 to carry out task

'Everything you do in Book1 will appear here

Windows("Book2").Activate   'This will be you going to close Book2

ActiveWindow.Close SaveChanges:=True    'Or False if no changes are to be made to Book2 when you close it

'This is where to add "Application.ScreenUpdating = True"

End Sub

Just add screen updating lines as show above to the start and end to hide this happening - anyone who does not know will not see anything happen on their screen.

Application.ScreenUpdating = False 'Add at the start of the VBA. This prevents the opening, updating and close from being seen.
Application.ScreenUpdating =True 'Add at the very end of the VBA. This line then allows normal updating.