Pull data from another sheet based on reference in cell

[kevinb11]

Looking for formula that will pull data from sheet named 4-24 (Data in Cells B1 to B3) Tried INDIRECT but it didn't work. The sheet I am pulling from will change every week. ie... 4-24 will be 5-1next week.
Thanks for any help you can provide.

DATE	4-24	4-25	4-26
LOADS	This cell to pull Sheet4-24 cell B1	This cell to pull Sheet4-25 cell B1
LATES	This cell to pull Sheet4-24 cell B2
OTD	This cell to pull Sheet4-24 cell B3

 

[SanjayGMusafir]

Tried INDIRECT but it didn't work

Check this and revert -

Assuming you are putting formula in Cell B2 of current sheet

=Indirect(B$1&"!B1")

Challenge is -

• It will update with changing sheet name.
• But for cell address it has to be manual.

So you call pull across but not pull down to autofill.

 

[SanjayGMusafir]

To pull across and down Create a helper column with cell address

Check this and revert -

Book1
	A	B	C
1		Direct	Indirect
2		Sheet2	Sheet2
3	B4	My Value	My Value
4	B5	New Value	New Value
5	B6	Test Value	Test Value
Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	=Sheet2!B4:B6
C3:C5	C3	=INDIRECT(C$2&"!"&$A3)
Dynamic array formulas.

 

[Snakehips]

@kevinb11 Given your date related sheet names I think you will need to have the surrounding single quote marks in the indirect sheet name string.

Below example shows 2 possible formulas that will drag.
B2:D4 if 'DATE' cells A2:A4 are formatted as TEXT
or
F2:H2 if 'DATE' cells are entered as date but custom formatted 'm-d'

Picking Teams.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	4-24	5-1	8-1	<<TEXT	4-24	5-1	5-8	<<Date
2	LOADS	ColB	Bee1			ColB	Bee1
3	LATES	Name	Bee2			Name	Bee2
4	OTD	Beee333	Bee3			Beee333	Bee2
Sheet6

Cell Formulas
Range	Formula
B2:D4	B2	=IFERROR(INDIRECT("'"&B$1&"'"&"!B"&ROWS(B$1:B1)),"")
F2:H3,F4	F2	=IFERROR(INDIRECT("'"&MONTH(F$1)&"-"&DAY(F$1)&"'"&"!B"&ROWS(D$1:D1)),"")
G4:H4	G4	=IFERROR(INDIRECT("'"&MONTH(G$1)&"-"&DAY(G$1)&"'"&"!B"&ROWS(E$1:E2)),"")

Hope that helps.