summary spreadsheet table

[cthorn112]

Hello,

I am looking to create a summary report based on a detailed worksheet. I played around with pivot table and was not having much luck. I know I can reference cells from the detailed worksheet into the summary worksheet =SUM(Sheet1.1!B2:E2)/4 but every time I add a column in the detailed worksheet, the formulas in the summary worksheet change. The fields are dynamic and columns will added (each column is a date) and sometimes even rows (for new people). Since I cannot attach files I will show a sample. Any suggestions on the best the way to tackle this would be greatly appreciated. Thanks in advance
This is the detailed worksheet

<table width="559" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:6034;width:124pt" width="165"> <col style="mso-width-source:userset;mso-width-alt:1353;width:28pt" width="37"> <col style="mso-width-source:userset;mso-width-alt:1792; width:37pt" width="49" span="2"> <col style="mso-width-source:userset;mso-width-alt:1682;width:35pt" width="46"> <col style="mso-width-source:userset;mso-width-alt:1462;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:1682;width:35pt" width="46"> <col style="mso-width-source:userset;mso-width-alt:1609; width:33pt" width="44" span="2"> <col style="mso-width-source:userset;mso-width-alt:1426;width:29pt" width="39"> </colgroup><tbody><tr style="mso-height-source:userset;height:15.0pt" height="20"> <td class="xl72" style="height:15.0pt;width:124pt" width="165" height="20">STAFF</td> <td class="xl73" style="border-left:none;width:28pt" width="37">3-Jun</td> <td class="xl73" style="border-left:none;width:37pt" width="49">27-May</td> <td class="xl73" style="border-left:none;width:37pt" width="49">20-May</td> <td class="xl73" style="border-left:none;width:35pt" width="46">13-May</td> <td class="xl73" style="border-left:none;width:30pt" width="40">6-May</td> <td class="xl73" style="border-left:none;width:35pt" width="46">29-Apr</td> <td class="xl73" style="border-left:none;width:33pt" width="44">22-Apr</td> <td class="xl73" style="border-left:none;width:33pt" width="44">15-Apr</td> <td class="xl73" style="border-left:none;width:29pt" width="39">1-Apr</td> </tr> <tr style="mso-height-source:userset;height:16.5pt" height="22"> <td class="xl65" style="height:16.5pt;border-top:none" height="22">Amanda</td> <td class="xl66" style="border-left:none">1</td> <td class="xl67" style="border-left:none">0</td> <td class="xl68" style="border-left:none">1</td> <td class="xl67" style="border-left:none">0</td> <td class="xl67" style="border-left:none">0</td> <td class="xl66" style="border-left:none">1</td> <td class="xl68" style="border-left:none">1</td> <td class="xl68" style="border-left:none">1</td> <td class="xl66" style="border-left:none">1</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt;border-top:none" height="20">Anthony
</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl67" style="border-top:none;border-left:none">0</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl67" style="border-top:none;border-left:none">0</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt;border-top:none" height="20">Athena
</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl66" style="border-top:none;border-left:none">1</td> <td class="xl67" style="border-top:none;border-left:none">0</td> </tr> </tbody></table>

This is what I would like the summary worksheet to look like.

<table width="271" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2048;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:2852; width:59pt" width="78" span="2"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> </colgroup><tbody><tr style="height:30.0pt" height="40"> <td class="xl65" style="height:30.0pt;width:42pt" width="56" height="40">Name</td> <td class="xl65" style="width:59pt" width="78">Invites Past Week</td> <td class="xl65" style="width:59pt" width="78">Invites Past 4 Weeks</td> <td class="xl65" style="width:44pt" width="59">Invites Lifetime</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Amanda</td> <td class="xl66">1</td> <td class="xl66">0.5</td> <td class="xl66">0.46154</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Anthony
</td> <td class="xl66">1</td> <td class="xl66">0.5</td> <td class="xl66">0.66667</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Athena
</td> <td class="xl66">1</td> <td class="xl66">1</td> <td class="xl66">0.92308</td> </tr> </tbody></table>

-Chris

 

[pbornemeier]

Use dynamic named ranges define the columns you need to deal with and the INDIRECT function to prevent formulas from changing when rows/columns are inserted.

Assumptions:
1) "Staff" is in A1
2) Current week is inserted in Column B and the date for that week is in B1
3) Summary sheet names are on same row as detail sheet names
4) Summary Col D is the average of all dates. (Sample data does not reflect this, Amanda should be 0.6666667)
5) No filled cells in Detailed Row 1 after last date

Define Named Range
ColCount
=COUNTA(Detailed!$1:$1)-1

On the Summary page
A2=
=INDIRECT("Detailed!A2")
B2=
=INDIRECT("Detailed!B2")
C2=
=AVERAGE(INDIRECT("Detailed!B2:E2"))
D2=
=AVERAGE(INDIRECT("Detailed!RC2:RC[" & ColCount & "]",FALSE))

 

[cthorn112]

Phil,

thank you very much for the advice. I never heard of the function INDIRECT. I looked around and found the combination of OFFSET and MATCH to do the trick. I have another similar request from an employee so I will use your approach for that. As the saying goes there are several ways to skin a cat. thanks again.

-Chris