Formula to reference a tab

[Mexcel73]

Hello,

Is there a formula I can enter to reference a tab rather than having to change it manually? See file as an example.

Referencing Tab Formula Mr Excel.xlsx
	A	B	C	D	E	F	G
1	Weekly Box Office Sales
2
3	The below is changed manually each week to refer to the new tab
4
5		10/27/2024	11/3/2024	11/10/2024	11/17/2024	11/24/2024	12/1/2024
6
7	Gross Sales	3,500	4,200	3,150	3,500	4,900	4,550
8	Net Sales	3,325	3,990	2,993	3,325	4,655	4,323
9
10
11	I would like to have formulas in B16 to G17 to refer to the tab using row 13
12
13		10.27.24	11.03.24	11.10.24	11.17.24	11.24.24	12.01.24
14		10/27/2024	11/3/2024	11/10/2024	11/17/2024	11/24/2024	12/1/2024
15
16	Gross Sales
17	Net Sales
Summary

Cell Formulas
Range	Formula
C5:G5,C14:G14	C5	=B5+7
B7:B8	B7	='10.27.24'!I6
C7:C8	C7	='11.03.24'!I6
D7:D8	D7	='11.10.24'!I6
E7:E8	E7	='11.17.24'!I6
F7:F8	F7	='11.24.24'!I6
G7:G8	G7	='12.01.24'!I6

Thank you in advance.

Michael

 

[Mexcel73]

Adding image of the sheet setup:

 

[Nikata]

I think this would work for you:

=INDIRECT("'"&B13&"'!"&I6)

B13 is the tab name, I6 is the info on that tab you want shown.

 

[Nikata]

=INDIRECT("'"&B13&"'!"&"I6")

Forgot the brackets on I6

 

[Mexcel73]

Thank you so much! I didn't realize it would be a lot more difficult to do the same if the formula was referencing a different workbook saved in a different folder. Can you help with this as well?
And will the formula show up when the workbook it is referencing is closed? Or does it have to be open to show the results?

 

[Nikata]

Might be easier to make a macro in that case:

Sub OpenAndCopyFromFile()
    Dim filePath As String
    Dim fileName As String
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    
    Set wb1 = ThisWorkbook
    'get the file path and name from cells - replace with where you place the path
    filePath = wb1.Worksheets("Summary").Range("I1").Value
    fileName = wb1.Worksheets("Summary").Range("I2").Value
    
    'open the source workbook    
    Set wb2 = Workbooks.Open(filePath & "\" & fileName)
    'Copy values from the source sheet and range - replace with your sheetname and range
    wb2.Worksheets("Source sheet").Range("A3:I6").Copy
    wb1.Worksheets("Summary").Range("B16").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    'close the source workbook without saving
    wb2.Close False
End Sub