Indirect??

RandyD123

Board Regular
Joined
Dec 4, 2013
Messages
145
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
try this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">May</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">June</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">July</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;background-color: #E2EFDA;;">2414</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;">1734</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">2</td><td style="text-align: right;border-right: 1px solid black;;">2467</td><td style="text-align: right;border-left: 1px solid black;;">2</td><td style="text-align: right;border-right: 1px solid black;;">2271</td><td style="text-align: right;border-left: 1px solid black;;">2</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">3</td><td style="text-align: right;border-right: 1px solid black;;">2494</td><td style="text-align: right;border-left: 1px solid black;;">3</td><td style="text-align: right;border-right: 1px solid black;;">2540</td><td style="text-align: right;border-left: 1px solid black;;">3</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">4</td><td style="text-align: right;border-right: 1px solid black;;">2045</td><td style="text-align: right;border-left: 1px solid black;;">4</td><td style="text-align: right;border-right: 1px solid black;;">2044</td><td style="text-align: right;border-left: 1px solid black;;">4</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">5</td><td style="text-align: right;border-right: 1px solid black;;">2295</td><td style="text-align: right;border-left: 1px solid black;;">5</td><td style="text-align: right;border-right: 1px solid black;;">2050</td><td style="text-align: right;border-left: 1px solid black;;">5</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">6</td><td style="text-align: right;border-right: 1px solid black;;">2414</td><td style="text-align: right;border-left: 1px solid black;;">6</td><td style="text-align: right;border-right: 1px solid black;;">2244</td><td style="text-align: right;border-left: 1px solid black;;">6</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">7</td><td style="text-align: right;border-right: 1px solid black;;">2047</td><td style="text-align: right;border-left: 1px solid black;;">7</td><td style="text-align: right;border-right: 1px solid black;;">2473</td><td style="text-align: right;border-left: 1px solid black;;">7</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">8</td><td style="text-align: right;border-right: 1px solid black;;">2224</td><td style="text-align: right;border-left: 1px solid black;;">8</td><td style="text-align: right;border-right: 1px solid black;;">1903</td><td style="text-align: right;border-left: 1px solid black;;">8</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">9</td><td style="text-align: right;border-right: 1px solid black;;">2551</td><td style="text-align: right;border-left: 1px solid black;;">9</td><td style="text-align: right;border-right: 1px solid black;;">1052</td><td style="text-align: right;border-left: 1px solid black;;">9</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">10</td><td style="text-align: right;border-right: 1px solid black;;">2459</td><td style="text-align: right;border-left: 1px solid black;;">10</td><td style="text-align: right;border-right: 1px solid black;;">1133</td><td style="text-align: right;border-left: 1px solid black;;">10</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">11</td><td style="text-align: right;border-right: 1px solid black;;">2073</td><td style="text-align: right;border-left: 1px solid black;;">11</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">11</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">12</td><td style="text-align: right;border-right: 1px solid black;;">2539</td><td style="text-align: right;border-left: 1px solid black;;">12</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">12</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">13</td><td style="text-align: right;border-right: 1px solid black;;">3043</td><td style="text-align: right;border-left: 1px solid black;;">13</td><td style="text-align: right;border-right: 1px solid black;;">1028</td><td style="text-align: right;border-left: 1px solid black;;">13</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">14</td><td style="text-align: right;border-right: 1px solid black;;">2448</td><td style="text-align: right;border-left: 1px solid black;;">14</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">14</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">15</td><td style="text-align: right;border-right: 1px solid black;;">2415</td><td style="text-align: right;border-left: 1px solid black;;">15</td><td style="text-align: right;border-right: 1px solid black;;">2297</td><td style="text-align: right;border-left: 1px solid black;;">15</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">16</td><td style="text-align: right;border-right: 1px solid black;;">2626</td><td style="text-align: right;border-left: 1px solid black;;">16</td><td style="text-align: right;border-right: 1px solid black;;">2523</td><td style="text-align: right;border-left: 1px solid black;;">16</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">17</td><td style="text-align: right;border-right: 1px solid black;;">2815</td><td style="text-align: right;border-left: 1px solid black;;">17</td><td style="text-align: right;border-right: 1px solid black;;">2651</td><td style="text-align: right;border-left: 1px solid black;;">17</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">18</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">18</td><td style="text-align: right;border-right: 1px solid black;;">2509</td><td style="text-align: right;border-left: 1px solid black;;">18</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">19</td><td style="text-align: right;border-right: 1px solid black;;">2670</td><td style="text-align: right;border-left: 1px solid black;;">19</td><td style="text-align: right;border-right: 1px solid black;;">2554</td><td style="text-align: right;border-left: 1px solid black;;">19</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">20</td><td style="text-align: right;border-right: 1px solid black;;">2961</td><td style="text-align: right;border-left: 1px solid black;;">20</td><td style="text-align: right;border-right: 1px solid black;;">2509</td><td style="text-align: right;border-left: 1px solid black;;">20</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">21</td><td style="text-align: right;border-right: 1px solid black;;">2444</td><td style="text-align: right;border-left: 1px solid black;;">21</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">21</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">22</td><td style="text-align: right;border-right: 1px solid black;;">2596</td><td style="text-align: right;border-left: 1px solid black;;">22</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">22</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">23</td><td style="text-align: right;border-right: 1px solid black;;">2589</td><td style="text-align: right;border-left: 1px solid black;;">23</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">23</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">24</td><td style="text-align: right;border-right: 1px solid black;;">2829</td><td style="text-align: right;border-left: 1px solid black;;">24</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">24</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">25</td><td style="text-align: right;border-right: 1px solid black;;">2121</td><td style="text-align: right;border-left: 1px solid black;;">25</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">25</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">26</td><td style="text-align: right;border-right: 1px solid black;;">1263</td><td style="text-align: right;border-left: 1px solid black;;">26</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">26</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">27</td><td style="text-align: right;border-right: 1px solid black;;">2019</td><td style="text-align: right;border-left: 1px solid black;;">27</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">27</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">28</td><td style="text-align: right;border-right: 1px solid black;;">2722</td><td style="text-align: right;border-left: 1px solid black;;">28</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">28</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">29</td><td style="text-align: right;border-right: 1px solid black;;">2041</td><td style="text-align: right;border-left: 1px solid black;;">29</td><td style="text-align: right;border-right: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;">29</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">30</td><td style="text-align: right;border-right: 1px solid black;;">2103</td><td style="text-align: right;border-left: 1px solid black;;">30</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">30</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;">31</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;">2282</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;">31</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=INDIRECT(<font color="Blue">"'"&TEXT(<font color="Red">DATE(<font color="Green">2019,MONTH(<font color="Purple">1&LEFT(<font color="Teal">B$2,3</font>)</font>),B3</font>),"ddd mmm "</font>)&TEXT(<font color="Red">B3,"00"</font>)&" 2019'!$J$55"</font>)</td></tr></tbody></table></td></tr></table><br />
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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

Board Regular
Joined
Dec 4, 2013
Messages
145
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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"),"")
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,099,754
Messages
5,470,581
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top