Formula using partial worksheet names to calculate results over multiple worksheets.

[jgrotter]

Hello,

I have Excel 2003. I'm trying to find a formula that will calculate results across multiple worksheets based on criteria such as (part) of the name of worksheets, and will continue to work without me touching it as I add more sheets to the workbook (will recalculate new sheets automatically).

I'm working on a reporting workbook that shows a days-worth of data (in this case, inbound calls) and summary information on daily worksheets, one sheet for each day, and has a final worksheet for month-to-date summary information: where each the information to date is summarized by 1) time of day (hour) and 2) Day of week (Mon/Tue/Wed, etc).

The worksheet will start with 5 sheets:
1. a "template" worksheet (named "sheet1") that has formulas & array-formulas and starts with all zero formula results (for daily data - I intend to make a copy of this worksheet and paste the raw data into it and have the formulas compute the summary),
2. a hidden "place-holder" spreadsheet (named "Spacer"; I use this as the consistent starting point of worksheets for formulas in the "month-to-date" worksheet that need to search through all daily sheets),
3. and a "month-to-date" worksheet with formulas that calculate the daily worksheet results - this one starts out with all zero formula results and grows as I add daily information,
4-5. two more summary/graph sheets that pull numbers off the month-to-date sheet (in a different format).

My process is that, at the beginning of the month when there's no data, all the formulas in the "template" sheet and "month-to-date" sheet are present, but computing zeros. On the first day of data (the 1st), I copy the "template" sheet and rename it by the weekday & date - i.e. "Thursday 1.1.09" - and then paste the raw data at the bottom: the formulas work and count the information on this worksheet. Each succeeding day, I add another worksheet in this manner, so that the workbook grows by one worksheet every day until months end.

The "month-to-date" worksheet summarizes all data with no problem with one group of exceptions: day of the week references. I need to collect the information regarding each week day: max number of reps per day for all Mondays / Tuesdays / Wednesday / (etc) in the month, total calls each week day, etc. On the first of January, there was only a Thursday - so it should count one Thursday and all other days should be zero. On the last day of the month it should be looking at 5 Thursdays, and all the other week days.

I don't want to touch any cells to re-calculate when I add a new day. What I'm stuck on first is "Max number or reps/day".



I've tried using formulas that look at sheet names and the cells I need using the "Max" function in various ways, and I found a wildcard that will compute "Thursday" sheets (regardless of the end date, i.e. 1.1.09/1.8.09) but once it computes it, the wildcard aspect goes away and the formula changes to specific sheet reference. Example:

1. (Daily) Sheet "Thursday 1.1.09" & "Thursday 1.8.09"
   • Column B = # of Reps
     • Cells B2:B16 = (All the Hourly Totals)
     • B18 = (The Daily Total) ( for the example sheets, it's "2" & "5" respectively)
2. Sheet "January-M2D":
   • Column A = Totals of Days
     • Cells A20 = "Monday", A21 = "Tuesday", etc
   • Column B = Max # of Reps
     • Cell B20:B26 = [Formula Here]

Formulas:

• "=MAX('Thursday*'!B18)" returns the value "5" (correct), but changes the cell formula to "=MAX('Monday 1.5.09'!B18)" (which makes it impossible to add worksheets and have it automatically re-adjust itself)
• "=MAX("'Monday*'!B18")" returns "#VALUE!"
• "=MAX("'"&A23&"*'!B18")" returns "#VALUE!"

I attempted to use "if" nest statements and add a "Day" cell in each daily sheet (i.e. in "Thursday 1.1.09" Cell C20 = "Thursday"), but it errors out:

• "=MAX(IF('Spacer:Sheet1'!C20=A23,'Spacer:Sheet1'!B18))"
  • returns "#REF!" and changes formula to "=MAX(IF(Spacer:Sheet1!C20='January-M2D'!A23,Spacer:Sheet1!B18))"
  • "Calculation Steps" shows that the very first " 'Spacer:Sheet1'!C20 " will result in an error.

I think that's about it. I'd like to post (part) of my workbook, if needed, I can do that a little later tonight/this weekend I will. Can someone help me with the formula that will calculate numbers based on the name of the worksheet(s)?

JG

 

[stanleydgromjr]

jgrotter,

Welcome to the MrExcel board.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/index.php?f=1

Instructions for using "Excel Jeanie HTML 4":
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[jgrotter]

Thank you for the welcome. I've been coming here a long time to lookup answers, but this is my first time posting.

Here's what that app came up with: the first is my "Month-to-date" summary, the second one is an example of the daily sheet "Sunday 1.11.09" (I cut out some of many rows of formulas because it makes the post extremely long (it's already long enough))....

January-M2D

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 186px"><COL style="WIDTH: 104px"><COL style="WIDTH: 146px"><COL style="WIDTH: 134px"><COL style="WIDTH: 107px"><COL style="WIDTH: 151px"><COL style="WIDTH: 33px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Totals of Times</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Max # CCR's</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Total Calls / Tech</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Answered</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Unanswered</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Unanswered %</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">07:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">19.50</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">17%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">08:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">39.00</TD><TD style="TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">17%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">09:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">36.83</TD><TD style="TEXT-ALIGN: center">67</TD><TD style="TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: center">30%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">10:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">45.83</TD><TD style="TEXT-ALIGN: center">94</TD><TD style="TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: center">24%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">11:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">37.50</TD><TD style="TEXT-ALIGN: center">87</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">16%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">12:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">33.50</TD><TD style="TEXT-ALIGN: center">75</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">18%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">13:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">33.13</TD><TD style="TEXT-ALIGN: center">83</TD><TD style="TEXT-ALIGN: center">33</TD><TD style="TEXT-ALIGN: center">28%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">14:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">42.13</TD><TD style="TEXT-ALIGN: center">110</TD><TD style="TEXT-ALIGN: center">39</TD><TD style="TEXT-ALIGN: center">26%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">15:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">27.88</TD><TD style="TEXT-ALIGN: center">74</TD><TD style="TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: center">25%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">16:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">35.17</TD><TD style="TEXT-ALIGN: center">65</TD><TD style="TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: center">29%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">17:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">24.00</TD><TD style="TEXT-ALIGN: center">63</TD><TD style="TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: center">26%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">18:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">17.75</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">37%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">19:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">22.25</TD><TD style="TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">34%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">20:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">11.00</TD><TD style="TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">32%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">21:00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">6.75</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">45%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">Total</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">249.80</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">826</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">282</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">25%</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Arial; TEXT-ALIGN: center">target=5% not answered</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Totals of Days</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center"># CCR's</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Avg Calls / Tech</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Answered</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Unanswered</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Unanswered %</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Monday</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">5.00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">#VALUE!</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Tuesday</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">Jan</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Wednesday</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">0.00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Thursday</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">0.00</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">#REF!</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Friday</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Saturday</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Sunday</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=MAX(Spacer:Sheet1!B3)</TD></TR><TR><TD>C2</TD><TD>=SUM(((D2+E2)+E2/2)/B2)</TD></TR><TR><TD>D2</TD><TD>=SUM(Spacer:Sheet1!D3)</TD></TR><TR><TD>E2</TD><TD>=SUM(Spacer:Sheet1!E3)</TD></TR><TR><TD>F2</TD><TD>=IF(SUM(D2:E2)>0,SUM(E2/(D2+E2)),0)</TD></TR><TR><TD>D20</TD><TD>=MAX("'Monday*'!B18")</TD></TR><TR><TD>C21</TD><TD>=MID(CELL("filename"),(FIND("]",CELL("filename"))+1),3)</TD></TR><TR><TD>D21</TD><TD>=MAX('Monday 1.5.09'!B18)</TD></TR><TR><TD>C22</TD><TD>{=MAX(IF(MID(CELL("filename"),(FIND("]",CELL("filename"))+1),LEN(A22))=A22,'Spacer:Thursday 1.1.09'!B2:B17))}</TD></TR><TR><TD>D22</TD><TD>=SUM('Wednesday 1.7.09'!B18)</TD></TR><TR><TD>C23</TD><TD>=MAX("'"&MID(CELL("filename"),(FIND("]",CELL("filename"))+1),LEN(A22))=A22&"!B2:B17")</TD></TR><TR><TD>D23</TD><TD>=MAX(IF(Spacer:Sheet1!C20=A23,Spacer:Sheet1!B18))</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!


</TD></TR></TBODY></TABLE>
Sunday 1.11.09

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 39px"><COL style="WIDTH: 88px"><COL style="WIDTH: 97px"><COL style="WIDTH: 70px"><COL style="WIDTH: 64px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center" rowSpan=2>Hour</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center" rowSpan=2># of CCR's</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center" rowSpan=2>Avg Calls / Tech / Hour</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center" rowSpan=2>Answered</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center" rowSpan=2>Unans</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center" rowSpan=2>Unans %</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">07:00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0.00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">08:00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0.00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">09:00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">4.50</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1 </TD><TD style="TEXT-ALIGN: center">20%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">10:00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">5.00</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">11:00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2.00</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">12:00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">3.50</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1 </TD><TD style="TEXT-ALIGN: center">25%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">13:00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">3.00</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">14:00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2.00</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">15:00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1.00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">16:00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2.00</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">17:00</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">0.00</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">18:00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0.00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">19:00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0.00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">20:00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0.00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">21:00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0.00</TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">- </TD><TD style="TEXT-ALIGN: center">0%</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">Totals</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">1 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">2.56</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">22 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">2 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">8%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Total Calls</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">22</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">9.09%</TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B3</TD><TD>{=SUM(IF(FREQUENCY(IF(C$28:C$1004>=TIME(7,0,0),IF(C$28:C$1004<TIME(8,0,0),IF(ISNUMBER(E$28:E$1004),MATCH(E$28:E$1004,E$28:E$1004,0)))),ROW(E$28:E$1004)-ROW(E$28)+1),1))}</TD></TR><TR><TD>C3</TD><TD>=IF(B3<>VALUE("0"),SUM(((D3+E3)+D3)/2)/B3,0)</TD></TR><TR><TD>D3</TD><TD>{=SUM(IF(LEFT($F$28:$F$1004,4)="comp",IF($C$28:$C$1004>TIME(7,0,0),IF($C$28:$C$1004<TIME(8,0,0),1,0))))}</TD></TR><TR><TD>E3</TD><TD>{=SUM(IF(LEFT($F$28:$F$1004,4)<>"comp",IF($C$28:$C$1004>TIME(7,0,0),IF($C$28:$C$1004<TIME(8,0,0),1,0))))}</TD></TR><TR><TD>F3</TD><TD>=IF(SUM(D3:E3)<>0,SUM(E3/(D3+E3)),0)</TD></TR><TR><TR><TD>B18</TD><TD>{=SUM(IF(FREQUENCY($E$25:$E$1000, $E$25:$E$1000)>0,1))}</TD></TR><TR><TD>C18</TD><TD>=IF(B18<>VALUE("0"),SUM((((D18+E18)+D18)/2)/B18)/COUNTIF(B3:B17,"<>0"),0)</TD></TR><TR><TD>D18</TD><TD>=SUM(D3:D17)</TD></TR><TR><TD>E18</TD><TD>=SUM(E3:E17)</TD></TR><TR><TD>F18</TD><TD>=IF(SUM(D18:E18)<>0,SUM(E18/(D18+E18)),0)</TD></TR><TR><TD>D21</TD><TD>=SUM(D5:D13)</TD></TR><TR><TD>E21</TD><TD>=SUM(E5:E13)</TD></TR><TR><TD>E22</TD><TD>=SUM(E21/D21)</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[jgrotter]

Lots of posts in the forum yesterday.

 

[jgrotter]

Bump.

No response yet. (I understand how busy it is.)

 

[stanleydgromjr]

BUMP: Re: Formula using partial worksheet names to calculate results over multiple worksheets.

jgrotter,

Sorry. I just can not understand your requirements.

I hope someone else can assist you.


Have a great day,
Stan

 

[jgrotter]

I kept searching for an answer and still haven't found it - I can't seem to find out how to search through specific worksheets using the name of the worksheet in the cell formula - like searching only all the "Monday*" worksheets (and not "Sunday*"/"Saturday*", etc) for values.

However, I did find a useable work-around (using the info listed here ) that, while work-able, isn't the prettiest and requires me to keep adding worksheet names to my "Spacer" worksheet.


Now, whenever I add a new day/worksheet, I'll unhide "Spacer" and insert a new row with the worksheet name in Column A and an Indirect function to my new worksheet's value in Column B, like so:

• =INDIRECT("'"&$A2&"'!B18")

On my MTD sheet, i added the following (array) formula:

• =MAX(IF(LEFT(Spacer!$A$2:$A$33,4)=LEFT($A26,4),Spacer!$B$2:$B$33))

Now it will effectively search all weekdays and grab the maximum number of reps for a specific weekday using the (first 4 characters of the) name of the day.

I'd still like to not do that manual adding in my "Spacer" worksheet - but it works for now.

Thanks for trying, Stanley.

 

[Dave Patton]

I did not follow all of your formulas etc; the following may give you some ideas.

I named the relevant sheets for Sunday "Sun".
Sheets like Sunday20090104


One example aggregating for 9:00


=SUMPRODUCT(SUMIF(INDIRECT("'"&Sun&"'!A2:A100"), A4, INDIRECT("'"&Sun&"'!C2:C100")))