Tracking hours with multiple calculations for various time spans

S

surveyair

New Member
Joined
Jul 24, 2011
Messages
6
Hi, I am looking to make a sheet to track pilot hours over a period of years and for each day calculate the hours flown in the last 7 days, 30 days, 42 days, 90 days, 180 days and 365 days. I am looking to make it multiple years since we need to track it for years without needing to add another year and then reenter all the previous information.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Have you already got a lot of data and/or have you an idea of how the data is laid out?
 
Upvote 0
We are starting this form from scratch, basically for January 1st 2011. We would need a column for the date then one to input the flight hours, after that we just need to make one column for each of the calculations.
 
Upvote 0
If I understand correctly what you want, so this can help you:

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Flight hours</td><td style="font-weight: bold;text-align: right;;">7</td><td style="font-weight: bold;text-align: right;;">30</td><td style="font-weight: bold;text-align: right;;">42</td><td style="font-weight: bold;text-align: right;;">90</td><td style="font-weight: bold;text-align: right;;">180</td><td style="font-weight: bold;text-align: right;;">365</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1/6/2011</td><td style="text-align: right;;">7</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFF00;;">1/7/2011</td><td style="text-align: right;background-color: #FFFF00;;">4</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1/8/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">43</td><td style="text-align: right;;">53</td><td style="text-align: right;;">53</td><td style="text-align: right;;">53</td><td style="text-align: right;;">53</td><td style="text-align: right;;">53</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">1/29/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">37</td><td style="text-align: right;;">167</td><td style="text-align: right;;">167</td><td style="text-align: right;;">167</td><td style="text-align: right;;">167</td><td style="text-align: right;;">167</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;background-color: #FFFF00;;">1/30/2011</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;background-color: #FFFF00;;">32</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">1/31/2011</td><td style="text-align: right;;">1</td><td style="text-align: right;;">29</td><td style="text-align: right;;">163</td><td style="text-align: right;;">173</td><td style="text-align: right;;">173</td><td style="text-align: right;;">173</td><td style="text-align: right;;">173</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">2/10/2011</td><td style="text-align: right;;">9</td><td style="text-align: right;;">47</td><td style="text-align: right;;">177</td><td style="text-align: right;;">238</td><td style="text-align: right;;">238</td><td style="text-align: right;;">238</td><td style="text-align: right;;">238</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;background-color: #FFFF00;;">2/11/2011</td><td style="text-align: right;background-color: #FFFF00;;">4</td><td style="text-align: right;background-color: #FFFF00;;">49</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">242</td><td style="text-align: right;background-color: #FFFF00;;">242</td><td style="text-align: right;background-color: #FFFF00;;">242</td><td style="text-align: right;background-color: #FFFF00;;">242</td><td style="text-align: right;;">42</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">2/12/2011</td><td style="text-align: right;;">6</td><td style="text-align: right;;">51</td><td style="text-align: right;;">171</td><td style="text-align: right;;">238</td><td style="text-align: right;;">248</td><td style="text-align: right;;">248</td><td style="text-align: right;;">248</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">90</td><td style="text-align: right;;">3/30/2011</td><td style="text-align: right;;">7</td><td style="text-align: right;;">29</td><td style="text-align: right;;">144</td><td style="text-align: right;;">197</td><td style="text-align: right;;">464</td><td style="text-align: right;;">464</td><td style="text-align: right;;">464</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">91</td><td style="text-align: right;background-color: #FFFF00;;">3/31/2011</td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;">28</td><td style="text-align: right;background-color: #FFFF00;;">142</td><td style="text-align: right;background-color: #FFFF00;;">196</td><td style="text-align: right;background-color: #FFFF00;;">465</td><td style="text-align: right;background-color: #FFFF00;;">465</td><td style="text-align: right;background-color: #FFFF00;;">465</td><td style="text-align: right;;">90</td></tr><tr ><td style="color: #161120;text-align: center;">92</td><td style="text-align: right;;">4/1/2011</td><td style="text-align: right;;">8</td><td style="text-align: right;;">36</td><td style="text-align: right;;">140</td><td style="text-align: right;;">201</td><td style="text-align: right;;">463</td><td style="text-align: right;;">473</td><td style="text-align: right;;">473</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">180</td><td style="text-align: right;;">6/28/2011</td><td style="text-align: right;;">8</td><td style="text-align: right;;">45</td><td style="text-align: right;;">158</td><td style="text-align: right;;">213</td><td style="text-align: right;;">453</td><td style="text-align: right;;">917</td><td style="text-align: right;;">917</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">181</td><td style="text-align: right;background-color: #FFFF00;;">6/29/2011</td><td style="text-align: right;background-color: #FFFF00;;">7</td><td style="text-align: right;background-color: #FFFF00;;">49</td><td style="text-align: right;background-color: #FFFF00;;">163</td><td style="text-align: right;background-color: #FFFF00;;">213</td><td style="text-align: right;background-color: #FFFF00;;">459</td><td style="text-align: right;background-color: #FFFF00;;">924</td><td style="text-align: right;background-color: #FFFF00;;">924</td><td style="text-align: right;;">180</td></tr><tr ><td style="color: #161120;text-align: center;">182</td><td style="text-align: right;;">6/30/2011</td><td style="text-align: right;;">8</td><td style="text-align: right;;">50</td><td style="text-align: right;;">167</td><td style="text-align: right;;">216</td><td style="text-align: right;;">459</td><td style="text-align: right;;">922</td><td style="text-align: right;;">932</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">206</td><td style="text-align: right;;">7/24/2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">23</td><td style="text-align: right;;">155</td><td style="text-align: right;;">218</td><td style="text-align: right;;">445</td><td style="text-align: right;;">896</td><td style="text-align: right;;">1042</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">207</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">208</td><td style=";">*********</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</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">Flight</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">C2</th><td style="text-align:left">=SUM(<font color="Blue">INDEX(<font color="Red">$B:$B,MAX(<font color="Green">ROW(<font color="Purple"></font>)-(<font color="Purple">C$1-1</font>),1</font>)</font>):INDEX(<font color="Red">$B:$B,ROW(<font color="Green"></font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
Thanks, that works great.

If I split each year up on a separate page how would make sure the formula still works, allowing it to look up the right amount of days from the past year?
 
Upvote 0
surveyair said:
Thanks, that works great.

If I split each year up on a separate page how would make sure the formula still works, allowing it to look up the right amount of days from the past year?
Click to expand...

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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Flight hours</td><td style="font-weight: bold;text-align: right;;">7</td><td style="font-weight: bold;text-align: right;;">30</td><td style="font-weight: bold;text-align: right;;">42</td><td style="font-weight: bold;text-align: right;;">90</td><td style="font-weight: bold;text-align: right;;">180</td><td style="font-weight: bold;text-align: right;;">365</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1/1/2010</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1/6/2010</td><td style="text-align: right;;">7</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFF00;;">1/7/2010</td><td style="text-align: right;background-color: #FFFF00;;">4</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1/8/2010</td><td style="text-align: right;;">3</td><td style="text-align: right;;">43</td><td style="text-align: right;;">53</td><td style="text-align: right;;">53</td><td style="text-align: right;;">53</td><td style="text-align: right;;">53</td><td style="text-align: right;;">53</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">1/29/2010</td><td style="text-align: right;;">3</td><td style="text-align: right;;">37</td><td style="text-align: right;;">167</td><td style="text-align: right;;">167</td><td style="text-align: right;;">167</td><td style="text-align: right;;">167</td><td style="text-align: right;;">167</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;background-color: #FFFF00;;">1/30/2010</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;background-color: #FFFF00;;">32</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">1/31/2010</td><td style="text-align: right;;">1</td><td style="text-align: right;;">29</td><td style="text-align: right;;">163</td><td style="text-align: right;;">173</td><td style="text-align: right;;">173</td><td style="text-align: right;;">173</td><td style="text-align: right;;">173</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">2/10/2010</td><td style="text-align: right;;">9</td><td style="text-align: right;;">47</td><td style="text-align: right;;">177</td><td style="text-align: right;;">238</td><td style="text-align: right;;">238</td><td style="text-align: right;;">238</td><td style="text-align: right;;">238</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;background-color: #FFFF00;;">2/11/2010</td><td style="text-align: right;background-color: #FFFF00;;">4</td><td style="text-align: right;background-color: #FFFF00;;">49</td><td style="text-align: right;background-color: #FFFF00;;">172</td><td style="text-align: right;background-color: #FFFF00;;">242</td><td style="text-align: right;background-color: #FFFF00;;">242</td><td style="text-align: right;background-color: #FFFF00;;">242</td><td style="text-align: right;background-color: #FFFF00;;">242</td><td style="text-align: right;;">42</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">2/12/2010</td><td style="text-align: right;;">6</td><td style="text-align: right;;">51</td><td style="text-align: right;;">171</td><td style="text-align: right;;">238</td><td style="text-align: right;;">248</td><td style="text-align: right;;">248</td><td style="text-align: right;;">248</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">90</td><td style="text-align: right;;">3/30/2010</td><td style="text-align: right;;">7</td><td style="text-align: right;;">29</td><td style="text-align: right;;">144</td><td style="text-align: right;;">197</td><td style="text-align: right;;">464</td><td style="text-align: right;;">464</td><td style="text-align: right;;">464</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">91</td><td style="text-align: right;background-color: #FFFF00;;">3/31/2010</td><td style="text-align: right;background-color: #FFFF00;;">1</td><td style="text-align: right;background-color: #FFFF00;;">28</td><td style="text-align: right;background-color: #FFFF00;;">142</td><td style="text-align: right;background-color: #FFFF00;;">196</td><td style="text-align: right;background-color: #FFFF00;;">465</td><td style="text-align: right;background-color: #FFFF00;;">465</td><td style="text-align: right;background-color: #FFFF00;;">465</td><td style="text-align: right;;">90</td></tr><tr ><td style="color: #161120;text-align: center;">92</td><td style="text-align: right;;">4/1/2010</td><td style="text-align: right;;">8</td><td style="text-align: right;;">36</td><td style="text-align: right;;">140</td><td style="text-align: right;;">201</td><td style="text-align: right;;">463</td><td style="text-align: right;;">473</td><td style="text-align: right;;">473</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">180</td><td style="text-align: right;;">6/28/2010</td><td style="text-align: right;;">8</td><td style="text-align: right;;">45</td><td style="text-align: right;;">158</td><td style="text-align: right;;">213</td><td style="text-align: right;;">453</td><td style="text-align: right;;">917</td><td style="text-align: right;;">917</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">181</td><td style="text-align: right;background-color: #FFFF00;;">6/29/2010</td><td style="text-align: right;background-color: #FFFF00;;">7</td><td style="text-align: right;background-color: #FFFF00;;">49</td><td style="text-align: right;background-color: #FFFF00;;">163</td><td style="text-align: right;background-color: #FFFF00;;">213</td><td style="text-align: right;background-color: #FFFF00;;">459</td><td style="text-align: right;background-color: #FFFF00;;">924</td><td style="text-align: right;background-color: #FFFF00;;">924</td><td style="text-align: right;;">180</td></tr><tr ><td style="color: #161120;text-align: center;">358</td><td style="text-align: right;;">12/23/2010</td><td style="text-align: right;;">8</td><td style="text-align: right;;">38</td><td style="text-align: right;;">123</td><td style="text-align: right;;">191</td><td style="text-align: right;;">456</td><td style="text-align: right;;">889</td><td style="text-align: right;;">1790</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">359</td><td style="text-align: right;;">12/24/2010</td><td style="text-align: right;;">9</td><td style="text-align: right;;">41</td><td style="text-align: right;;">124</td><td style="text-align: right;;">196</td><td style="text-align: right;;">460</td><td style="text-align: right;;">890</td><td style="text-align: right;;">1799</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">360</td><td style="text-align: right;;">12/25/2010</td><td style="text-align: right;;">3</td><td style="text-align: right;;">39</td><td style="text-align: right;;">118</td><td style="text-align: right;;">192</td><td style="text-align: right;;">463</td><td style="text-align: right;;">885</td><td style="text-align: right;;">1802</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">361</td><td style="text-align: right;;">12/26/2010</td><td style="text-align: right;;">10</td><td style="text-align: right;;">40</td><td style="text-align: right;;">126</td><td style="text-align: right;;">202</td><td style="text-align: right;;">470</td><td style="text-align: right;;">888</td><td style="text-align: right;;">1812</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">362</td><td style="text-align: right;;">12/27/2010</td><td style="text-align: right;;">0</td><td style="text-align: right;;">40</td><td style="text-align: right;;">126</td><td style="text-align: right;;">199</td><td style="text-align: right;;">462</td><td style="text-align: right;;">880</td><td style="text-align: right;;">1812</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">363</td><td style="text-align: right;;">12/28/2010</td><td style="text-align: right;;">9</td><td style="text-align: right;;">39</td><td style="text-align: right;;">134</td><td style="text-align: right;;">198</td><td style="text-align: right;;">469</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1821</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">364</td><td style="text-align: right;;">12/29/2010</td><td style="text-align: right;;">8</td><td style="text-align: right;;">47</td><td style="text-align: right;;">141</td><td style="text-align: right;;">198</td><td style="text-align: right;;">472</td><td style="text-align: right;;">883</td><td style="text-align: right;;">1829</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">365</td><td style="text-align: right;;">12/30/2010</td><td style="text-align: right;;">7</td><td style="text-align: right;;">46</td><td style="text-align: right;;">145</td><td style="text-align: right;;">195</td><td style="text-align: right;;">471</td><td style="text-align: right;;">885</td><td style="text-align: right;;">1836</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">366</td><td style="text-align: right;background-color: #FFFF00;;">12/31/2010</td><td style="text-align: right;background-color: #FFFF00;;">3</td><td style="text-align: right;background-color: #FFFF00;;">40</td><td style="text-align: right;background-color: #FFFF00;;">142</td><td style="text-align: right;background-color: #FFFF00;;">190</td><td style="text-align: right;background-color: #FFFF00;;">473</td><td style="text-align: right;background-color: #FFFF00;;">883</td><td style="text-align: right;background-color: #FFFF00;;">1839</td><td style="text-align: right;;">365</td></tr></tbody></table><p style="width: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">Flight2010</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">C2</th><td style="text-align:left">=SUM(<font color="Blue">INDEX(<font color="Red">$B:$B,MAX(<font color="Green">ROW(<font color="Purple"></font>)-(<font color="Purple">C$1-1</font>),1</font>)</font>):INDEX(<font color="Red">$B:$B,ROW(<font color="Green"></font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><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>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Flight hours</td><td style="font-weight: bold;text-align: right;;">7</td><td style="font-weight: bold;text-align: right;;">30</td><td style="font-weight: bold;text-align: right;;">42</td><td style="font-weight: bold;text-align: right;;">90</td><td style="font-weight: bold;text-align: right;;">180</td><td style="font-weight: bold;text-align: right;;">365</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">Rows of 2010:</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">366</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">39</td><td style="text-align: right;;">137</td><td style="text-align: right;;">186</td><td style="text-align: right;;">468</td><td style="text-align: right;;">879</td><td style="text-align: right;;">1831</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1/2/2011</td><td style="text-align: right;;">10</td><td style="text-align: right;;">39</td><td style="text-align: right;;">146</td><td style="text-align: right;;">195</td><td style="text-align: right;;">469</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1834</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1/3/2011</td><td style="text-align: right;;">4</td><td style="text-align: right;;">43</td><td style="text-align: right;;">145</td><td style="text-align: right;;">198</td><td style="text-align: right;;">465</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1831</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1/4/2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">34</td><td style="text-align: right;;">143</td><td style="text-align: right;;">188</td><td style="text-align: right;;">465</td><td style="text-align: right;;">883</td><td style="text-align: right;;">1822</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1/5/2011</td><td style="text-align: right;;">5</td><td style="text-align: right;;">31</td><td style="text-align: right;;">141</td><td style="text-align: right;;">185</td><td style="text-align: right;;">463</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1821</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1/6/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">26</td><td style="text-align: right;;">140</td><td style="text-align: right;;">178</td><td style="text-align: right;;">465</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1816</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFF00;;">1/7/2011</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;background-color: #FFFF00;;">28</td><td style="text-align: right;background-color: #FFFF00;;">135</td><td style="text-align: right;background-color: #FFFF00;;">181</td><td style="text-align: right;background-color: #FFFF00;;">462</td><td style="text-align: right;background-color: #FFFF00;;">883</td><td style="text-align: right;background-color: #FFFF00;;">1817</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1/8/2011</td><td style="text-align: right;;">4</td><td style="text-align: right;;">30</td><td style="text-align: right;;">139</td><td style="text-align: right;;">185</td><td style="text-align: right;;">466</td><td style="text-align: right;;">885</td><td style="text-align: right;;">1818</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">1/29/2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">16</td><td style="text-align: right;;">129</td><td style="text-align: right;;">202</td><td style="text-align: right;;">434</td><td style="text-align: right;;">877</td><td style="text-align: right;;">1798</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;background-color: #FFFF00;;">1/30/2011</td><td style="text-align: right;background-color: #FFFF00;;">9</td><td style="text-align: right;background-color: #FFFF00;;">23</td><td style="text-align: right;background-color: #FFFF00;;">135</td><td style="text-align: right;background-color: #FFFF00;;">202</td><td style="text-align: right;background-color: #FFFF00;;">434</td><td style="text-align: right;background-color: #FFFF00;;">886</td><td style="text-align: right;background-color: #FFFF00;;">1802</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">1/31/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">24</td><td style="text-align: right;;">136</td><td style="text-align: right;;">205</td><td style="text-align: right;;">433</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1804</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">2/10/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">28</td><td style="text-align: right;;">137</td><td style="text-align: right;;">189</td><td style="text-align: right;;">422</td><td style="text-align: right;;">882</td><td style="text-align: right;;">1787</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;background-color: #FFFF00;;">2/11/2011</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;background-color: #FFFF00;;">24</td><td style="text-align: right;background-color: #FFFF00;;">136</td><td style="text-align: right;background-color: #FFFF00;;">191</td><td style="text-align: right;background-color: #FFFF00;;">420</td><td style="text-align: right;background-color: #FFFF00;;">886</td><td style="text-align: right;background-color: #FFFF00;;">1788</td><td style="text-align: right;;">42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">2/12/2011</td><td style="text-align: right;;">7</td><td style="text-align: right;;">26</td><td style="text-align: right;;">137</td><td style="text-align: right;;">196</td><td style="text-align: right;;">427</td><td style="text-align: right;;">891</td><td style="text-align: right;;">1789</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">90</td><td style="text-align: right;;">3/30/2011</td><td style="text-align: right;;">6</td><td style="text-align: right;;">34</td><td style="text-align: right;;">141</td><td style="text-align: right;;">215</td><td style="text-align: right;;">437</td><td style="text-align: right;;">908</td><td style="text-align: right;;">1809</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">91</td><td style="text-align: right;background-color: #FFFF00;;">3/31/2011</td><td style="text-align: right;background-color: #FFFF00;;">2</td><td style="text-align: right;background-color: #FFFF00;;">26</td><td style="text-align: right;background-color: #FFFF00;;">136</td><td style="text-align: right;background-color: #FFFF00;;">214</td><td style="text-align: right;background-color: #FFFF00;;">436</td><td style="text-align: right;background-color: #FFFF00;;">909</td><td style="text-align: right;background-color: #FFFF00;;">1810</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">92</td><td style="text-align: right;;">4/1/2011</td><td style="text-align: right;;">6</td><td style="text-align: right;;">30</td><td style="text-align: right;;">141</td><td style="text-align: right;;">211</td><td style="text-align: right;;">440</td><td style="text-align: right;;">908</td><td style="text-align: right;;">1808</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">180</td><td style="text-align: right;;">6/28/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">28</td><td style="text-align: right;;">165</td><td style="text-align: right;;">200</td><td style="text-align: right;;">447</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1803</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">181</td><td style="text-align: right;background-color: #FFFF00;;">6/29/2011</td><td style="text-align: right;background-color: #FFFF00;;">3</td><td style="text-align: right;background-color: #FFFF00;;">26</td><td style="text-align: right;background-color: #FFFF00;;">164</td><td style="text-align: right;background-color: #FFFF00;;">202</td><td style="text-align: right;background-color: #FFFF00;;">448</td><td style="text-align: right;background-color: #FFFF00;;">884</td><td style="text-align: right;background-color: #FFFF00;;">1799</td><td style="text-align: right;;">180</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">182</td><td style="text-align: right;;">6/30/2011</td><td style="text-align: right;;">9</td><td style="text-align: right;;">30</td><td style="text-align: right;;">167</td><td style="text-align: right;;">205</td><td style="text-align: right;;">451</td><td style="text-align: right;;">891</td><td style="text-align: right;;">1800</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">206</td><td style="text-align: right;;">7/24/2011</td><td style="text-align: right;;">8</td><td style="text-align: right;;">44</td><td style="text-align: right;;">155</td><td style="text-align: right;;">239</td><td style="text-align: right;;">466</td><td style="text-align: right;;">910</td><td style="text-align: right;;">1825</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">207</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">208</td><td style=";">*********</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.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">Flight-2011</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">J1</th><td style="text-align:left">="Rows of "&YEAR(<font color="Blue">A2</font>)-1&":"</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K1</th><td style="text-align:left">=COUNTA(<font color="Blue">Flight2010!$B:$B</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">C$1>=ROW(<font color="Red"></font>),SUM(<font color="Red">INDEX(<font color="Green">Flight2010!$B:$B,$K$1-C$1+ROW(<font color="Purple"></font>)</font>):INDEX(<font color="Green">Flight2010!$B:$B,$K$1</font>)</font>),0</font>)+
SUM(<font color="Blue">INDEX(<font color="Red">$B:$B,MAX(<font color="Green">ROW(<font color="Purple"></font>)-(<font color="Purple">C$1-1</font>),1</font>)</font>):INDEX(<font color="Red">$B:$B,ROW(<font color="Green"></font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Markmzz
 
Last edited:
Upvote 0
We have named each of our pages simply by the year. Is there a way to replace Flight2010!$B:$B, where applicable in the formulas, with something that will call up the previous year? Example for the 2011 page have Flight2010!$B:$B automatically show up as 2010 in the formula
markmzz said:
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><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 style=" background-color: #E0E0F0;color: #161120" width="10px">J1</th><td style="text-align:left">="Rows of "&YEAR(A2)-1&":"</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">K1</th><td style="text-align:left">=COUNTA(Flight2010!$B:$B)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">C2</th><td style="text-align:left">=IF(C$1>=ROW(),SUM(INDEX(Flight2010!$B:$B,$K$1-C$1+ROW()):INDEX(Flight2010!$B:$B,$K$1)),0)+
SUM(INDEX($B:$B,MAX(ROW()-(C$1-1),1)):INDEX($B:$B,ROW()))</td></tr></tbody></table></td></tr></tbody></table>
Click to expand...
 
Upvote 0
surveyair said:
We have named each of our pages simply by the year. Is there a way to replace Flight2010!$B:$B, where applicable in the formulas, with something that will call up the previous year? Example for the 2011 page have Flight2010!$B:$B automatically show up as 2010 in the formula
Click to expand...

Like 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>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Flight hours</td><td style="font-weight: bold;text-align: right;;">7</td><td style="font-weight: bold;text-align: right;;">30</td><td style="font-weight: bold;text-align: right;;">42</td><td style="font-weight: bold;text-align: right;;">90</td><td style="font-weight: bold;text-align: right;;">180</td><td style="font-weight: bold;text-align: right;;">365</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">Rows of 2010:</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">366</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">39</td><td style="text-align: right;;">137</td><td style="text-align: right;;">186</td><td style="text-align: right;;">468</td><td style="text-align: right;;">879</td><td style="text-align: right;;">1831</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1/2/2011</td><td style="text-align: right;;">10</td><td style="text-align: right;;">39</td><td style="text-align: right;;">146</td><td style="text-align: right;;">195</td><td style="text-align: right;;">469</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1834</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1/3/2011</td><td style="text-align: right;;">4</td><td style="text-align: right;;">43</td><td style="text-align: right;;">145</td><td style="text-align: right;;">198</td><td style="text-align: right;;">465</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1831</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1/4/2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">34</td><td style="text-align: right;;">143</td><td style="text-align: right;;">188</td><td style="text-align: right;;">465</td><td style="text-align: right;;">883</td><td style="text-align: right;;">1822</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1/5/2011</td><td style="text-align: right;;">5</td><td style="text-align: right;;">31</td><td style="text-align: right;;">141</td><td style="text-align: right;;">185</td><td style="text-align: right;;">463</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1821</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1/6/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">26</td><td style="text-align: right;;">140</td><td style="text-align: right;;">178</td><td style="text-align: right;;">465</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1816</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFF00;;">1/7/2011</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;;">28</td><td style="text-align: right;;">135</td><td style="text-align: right;;">181</td><td style="text-align: right;;">462</td><td style="text-align: right;;">883</td><td style="text-align: right;;">1817</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1/8/2011</td><td style="text-align: right;;">4</td><td style="text-align: right;;">30</td><td style="text-align: right;;">139</td><td style="text-align: right;;">185</td><td style="text-align: right;;">466</td><td style="text-align: right;;">885</td><td style="text-align: right;;">1818</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">1/29/2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">16</td><td style="text-align: right;;">129</td><td style="text-align: right;;">202</td><td style="text-align: right;;">434</td><td style="text-align: right;;">877</td><td style="text-align: right;;">1798</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;background-color: #FFFF00;;">1/30/2011</td><td style="text-align: right;background-color: #FFFF00;;">9</td><td style="text-align: right;;">23</td><td style="text-align: right;;">135</td><td style="text-align: right;;">202</td><td style="text-align: right;;">434</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1802</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">1/31/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">24</td><td style="text-align: right;;">136</td><td style="text-align: right;;">205</td><td style="text-align: right;;">433</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1804</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">2/10/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">28</td><td style="text-align: right;;">137</td><td style="text-align: right;;">189</td><td style="text-align: right;;">422</td><td style="text-align: right;;">882</td><td style="text-align: right;;">1787</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;background-color: #FFFF00;;">2/11/2011</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;;">24</td><td style="text-align: right;;">136</td><td style="text-align: right;;">191</td><td style="text-align: right;;">420</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1788</td><td style="text-align: right;;">42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">2/12/2011</td><td style="text-align: right;;">7</td><td style="text-align: right;;">26</td><td style="text-align: right;;">137</td><td style="text-align: right;;">196</td><td style="text-align: right;;">427</td><td style="text-align: right;;">891</td><td style="text-align: right;;">1789</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">90</td><td style="text-align: right;;">3/30/2011</td><td style="text-align: right;;">6</td><td style="text-align: right;;">34</td><td style="text-align: right;;">141</td><td style="text-align: right;;">215</td><td style="text-align: right;;">437</td><td style="text-align: right;;">908</td><td style="text-align: right;;">1809</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">91</td><td style="text-align: right;background-color: #FFFF00;;">3/31/2011</td><td style="text-align: right;background-color: #FFFF00;;">2</td><td style="text-align: right;;">26</td><td style="text-align: right;;">136</td><td style="text-align: right;;">214</td><td style="text-align: right;;">436</td><td style="text-align: right;;">909</td><td style="text-align: right;;">1810</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">92</td><td style="text-align: right;;">4/1/2011</td><td style="text-align: right;;">6</td><td style="text-align: right;;">30</td><td style="text-align: right;;">141</td><td style="text-align: right;;">211</td><td style="text-align: right;;">440</td><td style="text-align: right;;">908</td><td style="text-align: right;;">1808</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">180</td><td style="text-align: right;;">6/28/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">28</td><td style="text-align: right;;">165</td><td style="text-align: right;;">200</td><td style="text-align: right;;">447</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1803</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">181</td><td style="text-align: right;background-color: #FFFF00;;">6/29/2011</td><td style="text-align: right;background-color: #FFFF00;;">3</td><td style="text-align: right;;">26</td><td style="text-align: right;;">164</td><td style="text-align: right;;">202</td><td style="text-align: right;;">448</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1799</td><td style="text-align: right;;">180</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">182</td><td style="text-align: right;;">6/30/2011</td><td style="text-align: right;;">9</td><td style="text-align: right;;">30</td><td style="text-align: right;;">167</td><td style="text-align: right;;">205</td><td style="text-align: right;;">451</td><td style="text-align: right;;">891</td><td style="text-align: right;;">1800</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">206</td><td style="text-align: right;;">7/24/2011</td><td style="text-align: right;;">8</td><td style="text-align: right;;">44</td><td style="text-align: right;;">155</td><td style="text-align: right;;">239</td><td style="text-align: right;;">466</td><td style="text-align: right;;">910</td><td style="text-align: right;;">1825</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">207</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">208</td><td style=";">*********</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:2.4em;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">2011</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">J1</th><td style="text-align:left">="Rows of "&YEAR(<font color="Blue">A2</font>)-1&":"</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K1</th><td style="text-align:left">=COUNTA(<font color="Blue">'2010'!$B:$B</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">C$1>=ROW(<font color="Red"></font>),SUM(<font color="Red">INDEX(<font color="Green">'2010'!$B:$B,$K$1-C$1+ROW(<font color="Purple"></font>)</font>):INDEX(<font color="Green">'2010'!$B:$B,$K$1</font>)</font>),0</font>)+
SUM(<font color="Blue">INDEX(<font color="Red">$B:$B,MAX(<font color="Green">ROW(<font color="Purple"></font>)-(<font color="Purple">C$1-1</font>),1</font>)</font>):INDEX(<font color="Red">$B:$B,ROW(<font color="Green"></font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Or 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>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Flight hours</td><td style="font-weight: bold;text-align: right;;">7</td><td style="font-weight: bold;text-align: right;;">30</td><td style="font-weight: bold;text-align: right;;">42</td><td style="font-weight: bold;text-align: right;;">90</td><td style="font-weight: bold;text-align: right;;">180</td><td style="font-weight: bold;text-align: right;;">365</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">Rows of 2010:</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">366</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">39</td><td style="text-align: right;;">137</td><td style="text-align: right;;">186</td><td style="text-align: right;;">468</td><td style="text-align: right;;">879</td><td style="text-align: right;;">1831</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1/2/2011</td><td style="text-align: right;;">10</td><td style="text-align: right;;">39</td><td style="text-align: right;;">146</td><td style="text-align: right;;">195</td><td style="text-align: right;;">469</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1834</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1/3/2011</td><td style="text-align: right;;">4</td><td style="text-align: right;;">43</td><td style="text-align: right;;">145</td><td style="text-align: right;;">198</td><td style="text-align: right;;">465</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1831</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1/4/2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">34</td><td style="text-align: right;;">143</td><td style="text-align: right;;">188</td><td style="text-align: right;;">465</td><td style="text-align: right;;">883</td><td style="text-align: right;;">1822</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1/5/2011</td><td style="text-align: right;;">5</td><td style="text-align: right;;">31</td><td style="text-align: right;;">141</td><td style="text-align: right;;">185</td><td style="text-align: right;;">463</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1821</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1/6/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">26</td><td style="text-align: right;;">140</td><td style="text-align: right;;">178</td><td style="text-align: right;;">465</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1816</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFF00;;">1/7/2011</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;;">28</td><td style="text-align: right;;">135</td><td style="text-align: right;;">181</td><td style="text-align: right;;">462</td><td style="text-align: right;;">883</td><td style="text-align: right;;">1817</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1/8/2011</td><td style="text-align: right;;">4</td><td style="text-align: right;;">30</td><td style="text-align: right;;">139</td><td style="text-align: right;;">185</td><td style="text-align: right;;">466</td><td style="text-align: right;;">885</td><td style="text-align: right;;">1818</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">1/29/2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">16</td><td style="text-align: right;;">129</td><td style="text-align: right;;">202</td><td style="text-align: right;;">434</td><td style="text-align: right;;">877</td><td style="text-align: right;;">1798</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;background-color: #FFFF00;;">1/30/2011</td><td style="text-align: right;background-color: #FFFF00;;">9</td><td style="text-align: right;;">23</td><td style="text-align: right;;">135</td><td style="text-align: right;;">202</td><td style="text-align: right;;">434</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1802</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">1/31/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">24</td><td style="text-align: right;;">136</td><td style="text-align: right;;">205</td><td style="text-align: right;;">433</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1804</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">2/10/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">28</td><td style="text-align: right;;">137</td><td style="text-align: right;;">189</td><td style="text-align: right;;">422</td><td style="text-align: right;;">882</td><td style="text-align: right;;">1787</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;background-color: #FFFF00;;">2/11/2011</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;;">24</td><td style="text-align: right;;">136</td><td style="text-align: right;;">191</td><td style="text-align: right;;">420</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1788</td><td style="text-align: right;;">42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">2/12/2011</td><td style="text-align: right;;">7</td><td style="text-align: right;;">26</td><td style="text-align: right;;">137</td><td style="text-align: right;;">196</td><td style="text-align: right;;">427</td><td style="text-align: right;;">891</td><td style="text-align: right;;">1789</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">90</td><td style="text-align: right;;">3/30/2011</td><td style="text-align: right;;">6</td><td style="text-align: right;;">34</td><td style="text-align: right;;">141</td><td style="text-align: right;;">215</td><td style="text-align: right;;">437</td><td style="text-align: right;;">908</td><td style="text-align: right;;">1809</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">91</td><td style="text-align: right;background-color: #FFFF00;;">3/31/2011</td><td style="text-align: right;background-color: #FFFF00;;">2</td><td style="text-align: right;;">26</td><td style="text-align: right;;">136</td><td style="text-align: right;;">214</td><td style="text-align: right;;">436</td><td style="text-align: right;;">909</td><td style="text-align: right;;">1810</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">92</td><td style="text-align: right;;">4/1/2011</td><td style="text-align: right;;">6</td><td style="text-align: right;;">30</td><td style="text-align: right;;">141</td><td style="text-align: right;;">211</td><td style="text-align: right;;">440</td><td style="text-align: right;;">908</td><td style="text-align: right;;">1808</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">180</td><td style="text-align: right;;">6/28/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">28</td><td style="text-align: right;;">165</td><td style="text-align: right;;">200</td><td style="text-align: right;;">447</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1803</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">181</td><td style="text-align: right;background-color: #FFFF00;;">6/29/2011</td><td style="text-align: right;background-color: #FFFF00;;">3</td><td style="text-align: right;;">26</td><td style="text-align: right;;">164</td><td style="text-align: right;;">202</td><td style="text-align: right;;">448</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1799</td><td style="text-align: right;;">180</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">182</td><td style="text-align: right;;">6/30/2011</td><td style="text-align: right;;">9</td><td style="text-align: right;;">30</td><td style="text-align: right;;">167</td><td style="text-align: right;;">205</td><td style="text-align: right;;">451</td><td style="text-align: right;;">891</td><td style="text-align: right;;">1800</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">206</td><td style="text-align: right;;">7/24/2011</td><td style="text-align: right;;">8</td><td style="text-align: right;;">44</td><td style="text-align: right;;">155</td><td style="text-align: right;;">239</td><td style="text-align: right;;">466</td><td style="text-align: right;;">910</td><td style="text-align: right;;">1825</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">207</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">208</td><td style=";">*********</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:2.4em;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">2011</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">J1</th><td style="text-align:left">="Rows of "&YEAR(<font color="Blue">A2</font>)-1&":"</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K1</th><td style="text-align:left">=COUNTA(<font color="Blue">_2010</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">C$1>=ROW(<font color="Red"></font>),SUM(<font color="Red">INDEX(<font color="Green">_2010,$K$1-C$1+ROW(<font color="Purple"></font>)</font>):INDEX(<font color="Green">_2010,$K$1</font>)</font>),0</font>)+
SUM(<font color="Blue">INDEX(<font color="Red">$B:$B,MAX(<font color="Green">ROW(<font color="Purple"></font>)-(<font color="Purple">C$1-1</font>),1</font>)</font>):INDEX(<font color="Red">$B:$B,ROW(<font color="Green"></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">_2010</th><td style="text-align:left">='2010'!$B:$B</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
markmzz said:
Like this:



<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><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 style=" background-color: #E0E0F0;color: #161120" width="10px">J1</th><td style="text-align:left">="Rows of "&YEAR(A2)-1&":"</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">K1</th><td style="text-align:left">=COUNTA('2010'!$B:$B)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">C2</th><td style="text-align:left">=IF(C$1>=ROW(),SUM(INDEX('2010'!$B:$B,$K$1-C$1+ROW()):INDEX('2010'!$B:$B,$K$1)),0)+
SUM(INDEX($B:$B,MAX(ROW()-(C$1-1),1)):INDEX($B:$B,ROW()))</td></tr></tbody></table></td></tr></tbody></table>


Markmzz
Click to expand...

I had thought of using the =COUNTA('2010'!$B:$B) like you suggest but then I will be forced to modify this formula for each year. Is there a formula that we can use to calculate the '2010' portion automatically?
 
Upvote 0
surveyair said:
I had thought of using the =COUNTA('2010'!$B:$B) like you suggest but then I will be forced to modify this formula for each year. Is there a formula that we can use to calculate the '2010' portion automatically?
Click to expand...

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>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Flight hours</td><td style="font-weight: bold;text-align: right;;">7</td><td style="font-weight: bold;text-align: right;;">30</td><td style="font-weight: bold;text-align: right;;">42</td><td style="font-weight: bold;text-align: right;;">90</td><td style="font-weight: bold;text-align: right;;">180</td><td style="font-weight: bold;text-align: right;;">365</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">Rows of 2010:</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">366</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">39</td><td style="text-align: right;;">137</td><td style="text-align: right;;">186</td><td style="text-align: right;;">468</td><td style="text-align: right;;">879</td><td style="text-align: right;;">1831</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1/2/2011</td><td style="text-align: right;;">10</td><td style="text-align: right;;">39</td><td style="text-align: right;;">146</td><td style="text-align: right;;">195</td><td style="text-align: right;;">469</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1834</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1/3/2011</td><td style="text-align: right;;">4</td><td style="text-align: right;;">43</td><td style="text-align: right;;">145</td><td style="text-align: right;;">198</td><td style="text-align: right;;">465</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1831</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1/4/2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">34</td><td style="text-align: right;;">143</td><td style="text-align: right;;">188</td><td style="text-align: right;;">465</td><td style="text-align: right;;">883</td><td style="text-align: right;;">1822</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1/5/2011</td><td style="text-align: right;;">5</td><td style="text-align: right;;">31</td><td style="text-align: right;;">141</td><td style="text-align: right;;">185</td><td style="text-align: right;;">463</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1821</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1/6/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">26</td><td style="text-align: right;;">140</td><td style="text-align: right;;">178</td><td style="text-align: right;;">465</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1816</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFF00;;">1/7/2011</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;;">28</td><td style="text-align: right;;">135</td><td style="text-align: right;;">181</td><td style="text-align: right;;">462</td><td style="text-align: right;;">883</td><td style="text-align: right;;">1817</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1/8/2011</td><td style="text-align: right;;">4</td><td style="text-align: right;;">30</td><td style="text-align: right;;">139</td><td style="text-align: right;;">185</td><td style="text-align: right;;">466</td><td style="text-align: right;;">885</td><td style="text-align: right;;">1818</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">1/29/2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">16</td><td style="text-align: right;;">129</td><td style="text-align: right;;">202</td><td style="text-align: right;;">434</td><td style="text-align: right;;">877</td><td style="text-align: right;;">1798</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;background-color: #FFFF00;;">1/30/2011</td><td style="text-align: right;background-color: #FFFF00;;">9</td><td style="text-align: right;;">23</td><td style="text-align: right;;">135</td><td style="text-align: right;;">202</td><td style="text-align: right;;">434</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1802</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">1/31/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">24</td><td style="text-align: right;;">136</td><td style="text-align: right;;">205</td><td style="text-align: right;;">433</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1804</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">2/10/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;;">28</td><td style="text-align: right;;">137</td><td style="text-align: right;;">189</td><td style="text-align: right;;">422</td><td style="text-align: right;;">882</td><td style="text-align: right;;">1787</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;background-color: #FFFF00;;">2/11/2011</td><td style="text-align: right;background-color: #FFFF00;;">5</td><td style="text-align: right;;">24</td><td style="text-align: right;;">136</td><td style="text-align: right;;">191</td><td style="text-align: right;;">420</td><td style="text-align: right;;">886</td><td style="text-align: right;;">1788</td><td style="text-align: right;;">42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">2/12/2011</td><td style="text-align: right;;">7</td><td style="text-align: right;;">26</td><td style="text-align: right;;">137</td><td style="text-align: right;;">196</td><td style="text-align: right;;">427</td><td style="text-align: right;;">891</td><td style="text-align: right;;">1789</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">90</td><td style="text-align: right;;">3/30/2011</td><td style="text-align: right;;">6</td><td style="text-align: right;;">34</td><td style="text-align: right;;">141</td><td style="text-align: right;;">215</td><td style="text-align: right;;">437</td><td style="text-align: right;;">908</td><td style="text-align: right;;">1809</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">91</td><td style="text-align: right;background-color: #FFFF00;;">3/31/2011</td><td style="text-align: right;background-color: #FFFF00;;">2</td><td style="text-align: right;;">26</td><td style="text-align: right;;">136</td><td style="text-align: right;;">214</td><td style="text-align: right;;">436</td><td style="text-align: right;;">909</td><td style="text-align: right;;">1810</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">92</td><td style="text-align: right;;">4/1/2011</td><td style="text-align: right;;">6</td><td style="text-align: right;;">30</td><td style="text-align: right;;">141</td><td style="text-align: right;;">211</td><td style="text-align: right;;">440</td><td style="text-align: right;;">908</td><td style="text-align: right;;">1808</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">180</td><td style="text-align: right;;">6/28/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">28</td><td style="text-align: right;;">165</td><td style="text-align: right;;">200</td><td style="text-align: right;;">447</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1803</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">181</td><td style="text-align: right;background-color: #FFFF00;;">6/29/2011</td><td style="text-align: right;background-color: #FFFF00;;">3</td><td style="text-align: right;;">26</td><td style="text-align: right;;">164</td><td style="text-align: right;;">202</td><td style="text-align: right;;">448</td><td style="text-align: right;;">884</td><td style="text-align: right;;">1799</td><td style="text-align: right;;">180</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">182</td><td style="text-align: right;;">6/30/2011</td><td style="text-align: right;;">9</td><td style="text-align: right;;">30</td><td style="text-align: right;;">167</td><td style="text-align: right;;">205</td><td style="text-align: right;;">451</td><td style="text-align: right;;">891</td><td style="text-align: right;;">1800</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">206</td><td style="text-align: right;;">7/24/2011</td><td style="text-align: right;;">8</td><td style="text-align: right;;">44</td><td style="text-align: right;;">155</td><td style="text-align: right;;">239</td><td style="text-align: right;;">466</td><td style="text-align: right;;">910</td><td style="text-align: right;;">1825</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">207</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">208</td><td style=";">*********</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:2.4em;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">2011</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">J1</th><td style="text-align:left">="Rows of "&YEAR(<font color="Blue">A2</font>)-1&":"</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K1</th><td style="text-align:left">=COUNTA(<font color="Blue">INDIRECT(<font color="Red">"'"&(<font color="Green">YEAR(<font color="Purple">A2</font>)-1</font>)&"'!$B:$B"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">C$1>=ROW(<font color="Red"></font>),SUM(<font color="Red">INDEX(<font color="Green">INDIRECT(<font color="Purple">"'"&(<font color="Teal">YEAR(<font color="#FF00FF">$A$2</font>)-1</font>)&"'!$B:$B"</font>),$K$1-C$1+ROW(<font color="Purple"></font>)</font>):INDEX(<font color="Green">INDIRECT(<font color="Purple">"'"&(<font color="Teal">YEAR(<font color="#FF00FF">$A$2</font>)-1</font>)&"'!$B:$B"</font>),$K$1</font>)</font>),0</font>)+
SUM(<font color="Blue">INDEX(<font color="Red">$B:$B,MAX(<font color="Green">ROW(<font color="Purple"></font>)-(<font color="Purple">C$1-1</font>),1</font>)</font>):INDEX(<font color="Red">$B:$B,ROW(<font color="Green"></font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Dynamic calculations for capacity
Replies
2
Views
79
Jali1992
J
J
  • Question Question
Shifting multiple cells left/right and ignoring certain columns?
Replies
2
Views
348
joshpep96
J
B
  • Solved
Looking to expand tool to track vacation pro-rations
Replies
1
Views
307
bh24524
B
R
  • Question Question
Leave Database Time Tracking to equal full day after 8 hours
Replies
6
Views
314
NoSparks
N
M
  • Question Question
Fantasy Calendar Creation stumbling..
Replies
2
Views
516
m26e4u
M

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top