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

Justic3G

New Member
Joined
Apr 20, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
98
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,757
Members
418,411
Latest member
Excellency

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