Indirect??

[RandyD123]

I have multiple sheets that I want to create a master tab. It needs to pull one cell value (J55) from each sheet. Not sure the easiest way to go about it. See example: column B,D,F,H....have to pull the day of month (each sheet is named for each day of month) Column C,E,G,I..... have to pull cell (J55) from each sheet. I'm sure I need a formula in each of those cells and I need to be able to just copy it down for all the days. See example sheet here

 

[RandyD123]

actual sheet is here

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G
1
2		May		June		July
3		1	2414	1	1734	1
4		2	2467	2	2271	2
5		3	2494	3	2540	3
6		4	2045	4	2044	4
7		5	2295	5	2050	5
8		6	2414	6	2244	6
9		7	2047	7	2473	7
10		8	2224	8	1903	8
11		9	2551	9	1052	9
12		10	2459	10	1133	10
13		11	2073	11	0	11
14		12	2539	12	0	12
15		13	3043	13	1028	13
16		14	2448	14	0	14
17		15	2415	15	2297	15
18		16	2626	16	2523	16
19		17	2815	17	2651	17
20		18	0	18	2509	18
21		19	2670	19	2554	19
22		20	2961	20	2509	20
23		21	2444	21	0	21
24		22	2596	22	0	22
25		23	2589	23	0	23
26		24	2829	24	0	24
27		25	2121	25	0	25
28		26	1263	26	0	26
29		27	2019	27	0	27
30		28	2722	28	0	28
31		29	2041	29	0	29
32		30	2103	30		30
33		31	2282			31
Sheet2

Cell Formulas
Range	Formula
C3	=INDIRECT("'"&TEXT(DATE(2019,MONTH(1&LEFT(B$2,3)),B3),"ddd mmm ")&TEXT(B3,"00")&" 2019'!$J$55")

 

[Special-K99]

This should work, fiddly to test properly since your sheet is protected and I didnt fancy copying all the sheets across to a new spreadsheet.

in Sheet1!C3
=INDIRECT("'"&TEXT(B3&"/"&B$2,"ddd")&" "&TEXT(B3&"/"&B$2,"mmm dd yyyy")&"'!J55")
copy down the column
copy to other columns

 

[RandyD123]

Thank You Very Much. Just one last question, If the sheets don't exist I get a #REF ! error. How can I avoid this error or have the cell show a blank value. I don't mind the "0" value for sheets that exist and there is in fact a "0" value in that cell. Just trying to avoid #REF ! errors for my columns for the future months.

 

[AlanY]

can use this to trap the error

=iferror(INDIRECT("'"&TEXT(DATE(2019,MONTH(1&LEFT(B$2,3)),B3),"ddd mmm ")&TEXT(B3,"00")&" 2019'!$J$55"),"")

 

[RandyD123]

PERFECT, Thank You Very Much!!!

 

[AlanY]

you're welcome