Sum across multiple tabs, criteria on each tab

[Sharon in Denver]

I have over a hundred tabs that are identical in format and data type. Each tab belongs to one of two categories, A or B. On a summary tab with the same formats all the others, I have in corresponding cells the formula =Sum('first:Last'I4) for many columns and many rows. What I'd like to do is create two more summary tabs (A and B) and be able to perform that same Sum, but based on the criteria on each tab in H1. I've researched Sumproduct(sumif and I don't think that is what I need, since my criteria is on the individal tabs, not a separate, summary tab. But I'm not sure.

I would prefer to do this with a formula, if at all possible.

Thanks in advance!

 

[Sharon in Denver]

When adjusted like that, the individual cell reference does increment, but the result is still FALSE because no numbers are availalbe, until it gets to a point where there should be numbers, then the result is #VALUE!

Maybe I need to go about it from a different angle?

 

[markmzz]

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">      All     </td><td style="font-weight: bold;text-align: center;;">              </td><td style="font-weight: bold;text-align: center;;">              </td><td style="font-weight: bold;text-align: center;;">              </td><td style="font-weight: bold;text-align: center;;">      A     </td><td style="font-weight: bold;text-align: center;;">              </td><td style="font-weight: bold;text-align: center;;">              </td><td style="font-weight: bold;text-align: center;;">              </td><td style="font-weight: bold;text-align: center;;">      B     </td><td style="font-weight: bold;text-align: center;;">              </td><td style="font-weight: bold;text-align: center;;">              </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">Col01</td><td style="font-weight: bold;text-align: center;;">Col02</td><td style="font-weight: bold;text-align: center;;">Col03</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Col01</td><td style="font-weight: bold;text-align: center;;">Col02</td><td style="font-weight: bold;text-align: center;;">Col03</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Col01</td><td style="font-weight: bold;text-align: center;;">Col02</td><td style="font-weight: bold;text-align: center;;">Col03</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1,834</td><td style="text-align: center;;">1,547</td><td style="text-align: center;;">881</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,309</td><td style="text-align: center;;">1,098</td><td style="text-align: center;;">510</td><td style="text-align: right;;"></td><td style="text-align: center;;">525</td><td style="text-align: center;;">449</td><td style="text-align: center;;">371</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">884</td><td style="text-align: center;;">1,768</td><td style="text-align: center;;">1,674</td><td style="text-align: center;;"></td><td style="text-align: center;;">484</td><td style="text-align: center;;">884</td><td style="text-align: center;;">874</td><td style="text-align: center;;"></td><td style="text-align: center;;">400</td><td style="text-align: center;;">884</td><td style="text-align: center;;">800</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">1,245</td><td style="text-align: center;;">1,102</td><td style="text-align: center;;">1,214</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,001</td><td style="text-align: center;;">775</td><td style="text-align: center;;">644</td><td style="text-align: center;;"></td><td style="text-align: center;;">244</td><td style="text-align: center;;">327</td><td style="text-align: center;;">570</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">1,009</td><td style="text-align: center;;">1,367</td><td style="text-align: center;;">1,021</td><td style="text-align: center;;"></td><td style="text-align: center;;">843</td><td style="text-align: center;;">793</td><td style="text-align: center;;">430</td><td style="text-align: center;;"></td><td style="text-align: center;;">166</td><td style="text-align: center;;">574</td><td style="text-align: center;;">591</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">1,692</td><td style="text-align: center;;">1,088</td><td style="text-align: center;;">1,570</td><td style="text-align: center;;"></td><td style="text-align: center;;">831</td><td style="text-align: center;;">445</td><td style="text-align: center;;">1,451</td><td style="text-align: center;;"></td><td style="text-align: center;;">861</td><td style="text-align: center;;">643</td><td style="text-align: center;;">119</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">1,229</td><td style="text-align: center;;">1,329</td><td style="text-align: center;;">2,362</td><td style="text-align: center;;"></td><td style="text-align: center;;">955</td><td style="text-align: center;;">1,092</td><td style="text-align: center;;">1,549</td><td style="text-align: center;;"></td><td style="text-align: center;;">274</td><td style="text-align: center;;">237</td><td style="text-align: center;;">813</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">1,377</td><td style="text-align: center;;">1,248</td><td style="text-align: center;;">1,714</td><td style="text-align: center;;"></td><td style="text-align: center;;">543</td><td style="text-align: center;;">760</td><td style="text-align: center;;">862</td><td style="text-align: center;;"></td><td style="text-align: center;;">834</td><td style="text-align: center;;">488</td><td style="text-align: center;;">852</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">647</td><td style="text-align: center;;">1,793</td><td style="text-align: center;;">1,109</td><td style="text-align: center;;"></td><td style="text-align: center;;">538</td><td style="text-align: center;;">1,561</td><td style="text-align: center;;">569</td><td style="text-align: center;;"></td><td style="text-align: center;;">109</td><td style="text-align: center;;">232</td><td style="text-align: center;;">540</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Master</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">I3</th><td style="text-align:left">=SUM(<font color="Blue">Sheet001:Sheet003!I3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J3</th><td style="text-align:left">=SUM(<font color="Blue">Sheet001:Sheet003!J3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K3</th><td style="text-align:left">=SUM(<font color="Blue">Sheet001:Sheet003!K3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&CPool&"'!H1"</font>),"A",INDIRECT(<font color="Green">"'"&CPool&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">I3</font>),COLUMN(<font color="Teal">I3</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&CPool&"'!H1"</font>),"A",INDIRECT(<font color="Green">"'"&CPool&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">J3</font>),COLUMN(<font color="Teal">J3</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&CPool&"'!H1"</font>),"A",INDIRECT(<font color="Green">"'"&CPool&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">K3</font>),COLUMN(<font color="Teal">K3</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Q3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&CPool&"'!H1"</font>),"B",INDIRECT(<font color="Green">"'"&CPool&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">I3</font>),COLUMN(<font color="Teal">I3</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">R3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&CPool&"'!H1"</font>),"B",INDIRECT(<font color="Green">"'"&CPool&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">J3</font>),COLUMN(<font color="Teal">J3</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">S3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&CPool&"'!H1"</font>),"B",INDIRECT(<font color="Green">"'"&CPool&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">K3</font>),COLUMN(<font color="Teal">K3</font>)</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">CPool</th><td style="text-align:left">={"Sheet001","Sheet002","Sheet003"}</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Sharon in Denver]

Mark, I get a FALSE or #VALUE! return on that too.

 

[Sharon in Denver]

Let me clarify... I did it a bit different than what you have laid out. I can't have use the columns like you have. I have to have the exact same format for each tab as well as the summary tabs. But let me look closer and see if I can get some ideas from your suggestion. Thank you!

 

[markmzz]

Mark, I get a FALSE or #VALUE! return on that too.

Look at your PM.

Markmzz

 

[Sharon in Denver]

Forgive me Mark, what do you mean by "look at your PM"?

 

[markmzz]

Forgive me Mark, what do you mean by "look at your PM"?

I send to you a Private Message.

Look at your Inbox.

Markmzz

 

[T. Valko]

When adjusted like that, the individual cell reference does increment, but the result is still FALSE because no numbers are availalbe, until it gets to a point where there should be numbers, then the result is #VALUE!

Maybe I need to go about it from a different angle?

Is there any way that you can post a SMALL sample file so we can see EXACTLY what you're trying to do?

We don't need a file with 100's of sheets, just a "few" will do.

You can use a free file hosting site if need be.

 

[T. Valko]

Try this:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH><TH>Q</TH><TH>R</TH><TH>S</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> All </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> A </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> B </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"> </TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col01</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col02</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col03</TD><TD style="TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col01</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col02</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col03</TD><TD style="TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col01</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col02</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col03</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1,834</TD><TD style="TEXT-ALIGN: center">1,547</TD><TD style="TEXT-ALIGN: center">881</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">1,309</TD><TD style="TEXT-ALIGN: center">1,098</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">525</TD><TD style="TEXT-ALIGN: center">449</TD><TD style="TEXT-ALIGN: center">371</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">884</TD><TD style="TEXT-ALIGN: center">1,768</TD><TD style="TEXT-ALIGN: center">1,674</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">484</TD><TD style="TEXT-ALIGN: center">884</TD><TD style="TEXT-ALIGN: center">874</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">400</TD><TD style="TEXT-ALIGN: center">884</TD><TD style="TEXT-ALIGN: center">800</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">1,245</TD><TD style="TEXT-ALIGN: center">1,102</TD><TD style="TEXT-ALIGN: center">1,214</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">1,001</TD><TD style="TEXT-ALIGN: center">775</TD><TD style="TEXT-ALIGN: center">644</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">244</TD><TD style="TEXT-ALIGN: center">327</TD><TD style="TEXT-ALIGN: center">570</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">1,009</TD><TD style="TEXT-ALIGN: center">1,367</TD><TD style="TEXT-ALIGN: center">1,021</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">843</TD><TD style="TEXT-ALIGN: center">793</TD><TD style="TEXT-ALIGN: center">430</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">166</TD><TD style="TEXT-ALIGN: center">574</TD><TD style="TEXT-ALIGN: center">591</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">1,692</TD><TD style="TEXT-ALIGN: center">1,088</TD><TD style="TEXT-ALIGN: center">1,570</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">831</TD><TD style="TEXT-ALIGN: center">445</TD><TD style="TEXT-ALIGN: center">1,451</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">861</TD><TD style="TEXT-ALIGN: center">643</TD><TD style="TEXT-ALIGN: center">119</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">1,229</TD><TD style="TEXT-ALIGN: center">1,329</TD><TD style="TEXT-ALIGN: center">2,362</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">955</TD><TD style="TEXT-ALIGN: center">1,092</TD><TD style="TEXT-ALIGN: center">1,549</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">274</TD><TD style="TEXT-ALIGN: center">237</TD><TD style="TEXT-ALIGN: center">813</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">1,377</TD><TD style="TEXT-ALIGN: center">1,248</TD><TD style="TEXT-ALIGN: center">1,714</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">543</TD><TD style="TEXT-ALIGN: center">760</TD><TD style="TEXT-ALIGN: center">862</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">834</TD><TD style="TEXT-ALIGN: center">488</TD><TD style="TEXT-ALIGN: center">852</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">647</TD><TD style="TEXT-ALIGN: center">1,793</TD><TD style="TEXT-ALIGN: center">1,109</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">538</TD><TD style="TEXT-ALIGN: center">1,561</TD><TD style="TEXT-ALIGN: center">569</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">109</TD><TD style="TEXT-ALIGN: center">232</TD><TD style="TEXT-ALIGN: center">540</TD></TR></TBODY></TABLE>

Master​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>I3</TH><TD style="TEXT-ALIGN: left">=SUM(Sheet001:Sheet003!I3)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>J3</TH><TD style="TEXT-ALIGN: left">=SUM(Sheet001:Sheet003!J3)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>K3</TH><TD style="TEXT-ALIGN: left">=SUM(Sheet001:Sheet003!K3)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>M3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"A",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(I3),COLUMN(I3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>N3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"A",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(J3),COLUMN(J3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>O3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"A",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(K3),COLUMN(K3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>Q3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"B",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(I3),COLUMN(I3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>R3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"B",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(J3),COLUMN(J3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>S3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"B",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(K3),COLUMN(K3)))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Workbook Defined Names<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>CPool</TH><TD style="TEXT-ALIGN: left">={"Sheet001","Sheet002","Sheet003"}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

If they insert new rows/columns on the sheet that holds the formula then that can cause those formulas to fail.

ADDRESS(ROW(I3),COLUMN(I3))

Is not a very good method.

 

[Sharon in Denver]

Biff, it looks like that might be necessary. I'll work on that.