# Thread: Indirect?? Thanks:  1 Post #5296424 (1) Likes:  1 Post #5296424 (1)

1. ## Indirect??

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

2. ## Re: Indirect??

actual sheet is here

3. ## Re: Indirect??

try this

ABCDEFG
1
2MayJuneJuly
312414117341
422467222712
532494325403
642045420444
752295520505
862414622446
972047724737
1082224819038
1192551910529
1210245910113310
1311207311011
1412253912012
1513304313102813
1614244814014
1715241515229715
1816262616252316
1917281517265117
2018018250918
2119267019255419
2220296120250920
2321244421021
2422259622022
2523258923023
2624282924024
2725212125025
2826126326026
2927201927027
3028272228028
3129204129029
323021033030
3331228231

Sheet2

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

4. ## Re: Indirect??

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

5. ## Re: Indirect??

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.

6. ## Re: Indirect??

can use this to trap the error

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

7. ## Re: Indirect??

PERFECT, Thank You Very Much!!!

8. ## Re: Indirect??

you're welcome