Hey everyone,
So this is my project im working on. The second fund I edited the formula so you could have an example that showed up in the first table on the left.
Basically I want to be able to change between portfolios and the Sub-Port-IDs to change with them to their respective codes. I was hoping to do that by linking the Tickers together, but the ticker relys on the Sub-Port-ID thus creating a circular reference if used.
So my question to all of you is how can I solve my problem another way? I've been puzzled for quite some time and tried a few different ways. I'm running out of ideas.
I also don't want to manually type anything in as I want to click on a portfolio and everything loads for me accordingly.
Essentailly I'd like to create a VLOOKUP where the ticker is looked up in the top left table and if found gives the Sub-Port-ID # which would be A2-A5. If however it cannot find the ticker in the table, it will default to A1, which is the standard (ignore the ticker "SPEN" for now).
Thanks in advance for any help. Sorry such a long post.
Portfolios
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 151px"><COL style="WIDTH: 274px"><COL style="WIDTH: 98px"><COL style="WIDTH: 64px"><COL style="WIDTH: 9px"><COL style="WIDTH: 309px"><COL style="WIDTH: 72px"><COL style="WIDTH: 96px"><COL style="WIDTH: 74px"><COL style="WIDTH: 119px"></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><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Date</TD><TD style="TEXT-ALIGN: left">May 27, 2009</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="COLOR: #008000">Portfolio ID:</TD><TD style="COLOR: #000080; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center">A</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Portfolio:</TD><TD>Retirement Income Pension Plan</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Model:</TD><TD>ERISA Model</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Stance:</TD><TD style="TEXT-ALIGN: left">Portfolio Stance is Aggressive @ 75% of the Equity Range.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Equity Range:</TD><TD style="TEXT-ALIGN: left">Equity Target is 62.5%.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">Low</TD><TD style="TEXT-ALIGN: center">55.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">High</TD><TD style="TEXT-ALIGN: center">65.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Security Min:</TD><TD style="COLOR: #008000; TEXT-ALIGN: center">75%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Security Max:</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">105%</TD><TD></TD><TD></TD><TD></TD><TD></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">11</TD><TD># of Sub-Portfolios:</TD><TD style="TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></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">12</TD><TD style="FONT-WEIGHT: bold">Portfolios</TD><TD style="FONT-WEIGHT: bold"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BACKGROUND-COLOR: #0000ff"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">Ticker</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">1</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">2</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">SPEN</TD><TD style="COLOR: #800000">A1</TD><TD>A - SPEN</TD><TD style="TEXT-ALIGN: right">93,145,478 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">ALTH</TD><TD style="COLOR: #800000">A2</TD><TD>A - SALARIED - ALETHEIA</TD><TD style="TEXT-ALIGN: right">4,181,736 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">ABI</TD><TD style="COLOR: #800000">A3</TD><TD>A - SALARIED - ALLIANCE BERNSTEIN</TD><TD style="TEXT-ALIGN: right">8,671,005 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">NBGR</TD><TD style="COLOR: #800000">A4</TD><TD>A - SALARIED - NEUBERGER BERMAN</TD><TD style="TEXT-ALIGN: right">12,625,843 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">NWQ</TD><TD style="COLOR: #800000">A5</TD><TD>A - SPEN - NWQ</TD><TD style="TEXT-ALIGN: right">10,095,843 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">6</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">7</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">8</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: center">9</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">10</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></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">24</TD><TD></TD><TD></TD><TD>Total</TD><TD></TD><TD style="TEXT-ALIGN: right">128,719,905 </TD><TD></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">25</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></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">26</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000">Retirement Income Pension Plan - Actual</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000">Market Value as of May 27, 2009</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 44px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD></TD><TD>Bring tickers into table doing a VLOOKUP</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000">Name</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Ticker</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">CUSIP</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Sub-Port ID</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Market Value</TD></TR><TR style="HEIGHT: 8px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 38px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD></TD><TD>Then create if statement for Sub-Port ID saying, If one of tickers, result in either A2:A5, if not then A1 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 8px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #808000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #808000">Cash & Equivalents</TD><TD style="FONT-WEIGHT: bold; COLOR: #808000"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold; COLOR: #808000"></TD><TD style="TEXT-ALIGN: right">3,532,932 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #800000; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: left">Money Market Funds</TD><TD style="COLOR: #800000; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD><TD style="COLOR: #800000; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #808080; TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: left">Wells Fargo Advantage Prime</TD><TD style="COLOR: #808080; TEXT-ALIGN: center">NWRXX</TD><TD style="TEXT-ALIGN: right">vp4540002</TD><TD style="COLOR: #808080; TEXT-ALIGN: center">A1</TD><TD style="TEXT-ALIGN: right">3,532,932 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #808080; TEXT-ALIGN: center"></TD><TD></TD><TD style="COLOR: #808080"></TD><TD></TD><TD style="COLOR: #808080"></TD><TD></TD></TR><TR style="HEIGHT: 10px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">39</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #993300; TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold; COLOR: #993300">Fixed Income</TD><TD style="FONT-WEIGHT: bold; COLOR: #808080"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold; COLOR: #808080"></TD><TD style="TEXT-ALIGN: right">62,584,263 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">40</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #800000; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center"></TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: left">Investment Grade Bonds</TD><TD style="COLOR: #808080; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD><TD style="COLOR: #808080; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">41</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #808080; TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: left">SPEN - Neuberger Berman</TD><TD style="COLOR: #808080; TEXT-ALIGN: center">NBGR</TD><TD style="TEXT-ALIGN: right">SEP</TD><TD style="COLOR: #808080; TEXT-ALIGN: center">A4</TD><TD style="TEXT-ALIGN: right">12,625,843 </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>B1</TD><TD>='All Port Source Upload'!A4</TD></TR><TR><TD>B3</TD><TD>=VLOOKUP(B2,Accts!$M$108:$P$165,2,FALSE)</TD></TR><TR><TD>B4</TD><TD>=VLOOKUP(B2,Accts!M108:P165,Accts!O106)</TD></TR><TR><TD>B5</TD><TD>=HLOOKUP(B4,Models!C10:AC13,Models!B11,FALSE)</TD></TR><TR><TD>B6</TD><TD>=HLOOKUP(B4,Models!C10:AC13,Models!B13,FALSE)</TD></TR><TR><TD>B7</TD><TD>=HLOOKUP(B4&" - "&A7,Models!$B$14:$AC$32,Models!$B$22,FALSE)</TD></TR><TR><TD>B8</TD><TD>=HLOOKUP(B4&" - "&A8,Models!$B$14:$AC$32,Models!$B$22,FALSE)</TD></TR><TR><TD>B9</TD><TD>=Models!A7</TD></TR><TR><TD>B10</TD><TD>=Models!A8</TD></TR><TR><TD>B11</TD><TD>=COUNTIF(Accts!$C$3:$C$51,Portfolios!B2)</TD></TR><TR><TD>B14</TD><TD>=IF(D14="","",VLOOKUP(D14,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C14</TD><TD>=IF($A14>$B$11,"",VLOOKUP($B$2&$A14,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D14</TD><TD>=IF($A14>$B$11,"",VLOOKUP($B$2&$A14,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E14</TD><TD>=IF($A14>$B$11,0,VLOOKUP($B$2&$A14,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A15</TD><TD>=A14+1</TD></TR><TR><TD>B15</TD><TD>=IF(D15="","",VLOOKUP(D15,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C15</TD><TD>=IF($A15>$B$11,"",VLOOKUP($B$2&$A15,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D15</TD><TD>=IF($A15>$B$11,"",VLOOKUP($B$2&$A15,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E15</TD><TD>=IF($A15>$B$11,0,VLOOKUP($B$2&$A15,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A16</TD><TD>=A15+1</TD></TR><TR><TD>B16</TD><TD>=IF(D16="","",VLOOKUP(D16,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C16</TD><TD>=IF($A16>$B$11,"",VLOOKUP($B$2&$A16,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D16</TD><TD>=IF($A16>$B$11,"",VLOOKUP($B$2&$A16,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E16</TD><TD>=IF($A16>$B$11,0,VLOOKUP($B$2&$A16,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A17</TD><TD>=A16+1</TD></TR><TR><TD>B17</TD><TD>=IF(D17="","",VLOOKUP(D17,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C17</TD><TD>=IF($A17>$B$11,"",VLOOKUP($B$2&$A17,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D17</TD><TD>=IF($A17>$B$11,"",VLOOKUP($B$2&$A17,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E17</TD><TD>=IF($A17>$B$11,0,VLOOKUP($B$2&$A17,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A18</TD><TD>=A17+1</TD></TR><TR><TD>B18</TD><TD>=IF(D18="","",VLOOKUP(D18,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C18</TD><TD>=IF($A18>$B$11,"",VLOOKUP($B$2&$A18,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D18</TD><TD>=IF($A18>$B$11,"",VLOOKUP($B$2&$A18,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E18</TD><TD>=IF($A18>$B$11,0,VLOOKUP($B$2&$A18,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A19</TD><TD>=A18+1</TD></TR><TR><TD>B19</TD><TD>=IF(D19="","",VLOOKUP(D19,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C19</TD><TD>=IF($A19>$B$11,"",VLOOKUP($B$2&$A19,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D19</TD><TD>=IF($A19>$B$11,"",VLOOKUP($B$2&$A19,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E19</TD><TD>=IF($A19>$B$11,0,VLOOKUP($B$2&$A19,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A20</TD><TD>=A19+1</TD></TR><TR><TD>B20</TD><TD>=IF(D20="","",VLOOKUP(D20,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C20</TD><TD>=IF($A20>$B$11,"",VLOOKUP($B$2&$A20,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D20</TD><TD>=IF($A20>$B$11,"",VLOOKUP($B$2&$A20,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E20</TD><TD>=IF($A20>$B$11,0,VLOOKUP($B$2&$A20,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A21</TD><TD>=A20+1</TD></TR><TR><TD>B21</TD><TD>=IF(D21="","",VLOOKUP(D21,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C21</TD><TD>=IF($A21>$B$11,"",VLOOKUP($B$2&$A21,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D21</TD><TD>=IF($A21>$B$11,"",VLOOKUP($B$2&$A21,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E21</TD><TD>=IF($A21>$B$11,0,VLOOKUP($B$2&$A21,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A22</TD><TD>=A21+1</TD></TR><TR><TD>B22</TD><TD>=IF(D22="","",VLOOKUP(D22,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C22</TD><TD>=IF($A22>$B$11,"",VLOOKUP($B$2&$A22,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D22</TD><TD>=IF($A22>$B$11,"",VLOOKUP($B$2&$A22,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E22</TD><TD>=IF($A22>$B$11,0,VLOOKUP($B$2&$A22,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A23</TD><TD>=A22+1</TD></TR><TR><TD>B23</TD><TD>=IF(D23="","",VLOOKUP(D23,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C23</TD><TD>=IF($A23>$B$11,"",VLOOKUP($B$2&$A23,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D23</TD><TD>=IF($A23>$B$11,"",VLOOKUP($B$2&$A23,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E23</TD><TD>=IF($A23>$B$11,0,VLOOKUP($B$2&$A23,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>E24</TD><TD>=SUM(E14:E23)</TD></TR><TR><TD>H26</TD><TD>=B3&" - Actual"</TD></TR><TR><TD>H28</TD><TD>="Market Value as of "&TEXT(B1,"MMMM DD, YYYY")</TD></TR><TR><TD>H34</TD><TD>=Models!C16</TD></TR><TR><TD>L34</TD><TD>=SUM(L36:L36)</TD></TR><TR><TD>H35</TD><TD>=Models!C17</TD></TR><TR><TD>H36</TD><TD>=IF(J36="","",IF(LEN(J36)<4,VLOOKUP(K36,'Sec ID'!$A$4:$Z$124,2,FALSE),VLOOKUP(J36,'Sec ID'!$A$4:$Z$124,2,FALSE)))</TD></TR><TR><TD>I36</TD><TD>=IF(H36="","",VLOOKUP(H36,'Sec ID'!$B$4:$C$123,2,FALSE))</TD></TR><TR><TD>J36</TD><TD>=HLOOKUP($B$2,'HLOOKUP Sheet'!$A$1:$L$44,$G36+1,FALSE)</TD></TR><TR><TD>K36</TD><TD>=$C$14</TD></TR><TR><TD>L36</TD><TD>=IF(K36="","",IF(J36="SEP",SUMIF('All Port Source Upload'!$R$2:$R$6009,Portfolios!K36,'All Port Source Upload'!$Z$2:$Z$6009),SUMIF('All Port Source Upload'!$X$2:$X$6009,K36&J36,'All Port Source Upload'!$Z$2:$Z$6009)))</TD></TR><TR><TD>H37</TD><TD>=IF(J37="","",IF(LEN(J37)<4,VLOOKUP(K37,'Sec ID'!$A$4:$Z$124,2,FALSE),VLOOKUP(J37,'Sec ID'!$A$4:$Z$124,2,FALSE)))</TD></TR><TR><TD>I37</TD><TD>=IF(H37="","",VLOOKUP(H37,'Sec ID'!$B$4:$C$123,2,FALSE))</TD></TR><TR><TD>J37</TD><TD>=IF(K37="","",HLOOKUP($B$2,'HLOOKUP Sheet'!$A$1:$L$44,$G37+1,FALSE))</TD></TR><TR><TD>L37</TD><TD>=IF(K37="","",IF(J37="SEP",SUMIF('All Port Source Upload'!$R$2:$R$6009,Portfolios!K37,'All Port Source Upload'!$Z$2:$Z$6009),SUMIF('All Port Source Upload'!$X$2:$X$6009,K37&J37,'All Port Source Upload'!$Z$2:$Z$6009)))</TD></TR><TR><TD>H39</TD><TD>=Models!Q18</TD></TR><TR><TD>L39</TD><TD>=SUM(L41:L54)</TD></TR><TR><TD>H40</TD><TD>=Models!Q19</TD></TR><TR><TD>H41</TD><TD>=IF(J41="","",IF(LEN(J41)<4,VLOOKUP(K41,'Sec ID'!$A$4:$Z$124,2,FALSE),VLOOKUP(J41,'Sec ID'!$A$4:$Z$124,2,FALSE)))</TD></TR><TR><TD>I41</TD><TD>=IF(H41="","",VLOOKUP(H41,'Sec ID'!$B$4:$C$123,2,FALSE))</TD></TR><TR><TD>L41</TD><TD>=IF(K41="","",IF(J41="SEP",SUMIF('All Port Source Upload'!$R$2:$R$6009,Portfolios!K41,'All Port Source Upload'!$Z$2:$Z$6009),SUMIF('All Port Source Upload'!$X$2:$X$6009,K41&J41,'All Port Source Upload'!$Z$2:$Z$6009)))</TD></TR></TBODY></TABLE></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
So this is my project im working on. The second fund I edited the formula so you could have an example that showed up in the first table on the left.
Basically I want to be able to change between portfolios and the Sub-Port-IDs to change with them to their respective codes. I was hoping to do that by linking the Tickers together, but the ticker relys on the Sub-Port-ID thus creating a circular reference if used.
So my question to all of you is how can I solve my problem another way? I've been puzzled for quite some time and tried a few different ways. I'm running out of ideas.
I also don't want to manually type anything in as I want to click on a portfolio and everything loads for me accordingly.
Essentailly I'd like to create a VLOOKUP where the ticker is looked up in the top left table and if found gives the Sub-Port-ID # which would be A2-A5. If however it cannot find the ticker in the table, it will default to A1, which is the standard (ignore the ticker "SPEN" for now).
Thanks in advance for any help. Sorry such a long post.
Portfolios
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 151px"><COL style="WIDTH: 274px"><COL style="WIDTH: 98px"><COL style="WIDTH: 64px"><COL style="WIDTH: 9px"><COL style="WIDTH: 309px"><COL style="WIDTH: 72px"><COL style="WIDTH: 96px"><COL style="WIDTH: 74px"><COL style="WIDTH: 119px"></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><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Date</TD><TD style="TEXT-ALIGN: left">May 27, 2009</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="COLOR: #008000">Portfolio ID:</TD><TD style="COLOR: #000080; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center">A</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Portfolio:</TD><TD>Retirement Income Pension Plan</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Model:</TD><TD>ERISA Model</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Stance:</TD><TD style="TEXT-ALIGN: left">Portfolio Stance is Aggressive @ 75% of the Equity Range.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Equity Range:</TD><TD style="TEXT-ALIGN: left">Equity Target is 62.5%.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">Low</TD><TD style="TEXT-ALIGN: center">55.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">High</TD><TD style="TEXT-ALIGN: center">65.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Security Min:</TD><TD style="COLOR: #008000; TEXT-ALIGN: center">75%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Security Max:</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">105%</TD><TD></TD><TD></TD><TD></TD><TD></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">11</TD><TD># of Sub-Portfolios:</TD><TD style="TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></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">12</TD><TD style="FONT-WEIGHT: bold">Portfolios</TD><TD style="FONT-WEIGHT: bold"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BACKGROUND-COLOR: #0000ff"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">Ticker</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">1</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">2</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">SPEN</TD><TD style="COLOR: #800000">A1</TD><TD>A - SPEN</TD><TD style="TEXT-ALIGN: right">93,145,478 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">ALTH</TD><TD style="COLOR: #800000">A2</TD><TD>A - SALARIED - ALETHEIA</TD><TD style="TEXT-ALIGN: right">4,181,736 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">ABI</TD><TD style="COLOR: #800000">A3</TD><TD>A - SALARIED - ALLIANCE BERNSTEIN</TD><TD style="TEXT-ALIGN: right">8,671,005 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">NBGR</TD><TD style="COLOR: #800000">A4</TD><TD>A - SALARIED - NEUBERGER BERMAN</TD><TD style="TEXT-ALIGN: right">12,625,843 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">NWQ</TD><TD style="COLOR: #800000">A5</TD><TD>A - SPEN - NWQ</TD><TD style="TEXT-ALIGN: right">10,095,843 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">6</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">7</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">8</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: center">9</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">10</TD><TD></TD><TD style="COLOR: #800000"></TD><TD></TD><TD style="TEXT-ALIGN: right">- </TD><TD></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">24</TD><TD></TD><TD></TD><TD>Total</TD><TD></TD><TD style="TEXT-ALIGN: right">128,719,905 </TD><TD></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">25</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></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">26</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000">Retirement Income Pension Plan - Actual</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000">Market Value as of May 27, 2009</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 44px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD></TD><TD>Bring tickers into table doing a VLOOKUP</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000">Name</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Ticker</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">CUSIP</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Sub-Port ID</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Market Value</TD></TR><TR style="HEIGHT: 8px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 38px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD></TD><TD>Then create if statement for Sub-Port ID saying, If one of tickers, result in either A2:A5, if not then A1 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 8px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD><TD style="FONT-SIZE: 5pt; COLOR: #ffff99; BACKGROUND-COLOR: #000000"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #808000"></TD><TD style="FONT-WEIGHT: bold; COLOR: #808000">Cash & Equivalents</TD><TD style="FONT-WEIGHT: bold; COLOR: #808000"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold; COLOR: #808000"></TD><TD style="TEXT-ALIGN: right">3,532,932 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #800000; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: left">Money Market Funds</TD><TD style="COLOR: #800000; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD><TD style="COLOR: #800000; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #808080; TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: left">Wells Fargo Advantage Prime</TD><TD style="COLOR: #808080; TEXT-ALIGN: center">NWRXX</TD><TD style="TEXT-ALIGN: right">vp4540002</TD><TD style="COLOR: #808080; TEXT-ALIGN: center">A1</TD><TD style="TEXT-ALIGN: right">3,532,932 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #808080; TEXT-ALIGN: center"></TD><TD></TD><TD style="COLOR: #808080"></TD><TD></TD><TD style="COLOR: #808080"></TD><TD></TD></TR><TR style="HEIGHT: 10px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">39</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; COLOR: #993300; TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold; COLOR: #993300">Fixed Income</TD><TD style="FONT-WEIGHT: bold; COLOR: #808080"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold; COLOR: #808080"></TD><TD style="TEXT-ALIGN: right">62,584,263 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">40</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #800000; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center"></TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: left">Investment Grade Bonds</TD><TD style="COLOR: #808080; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD><TD style="COLOR: #808080; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">41</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="COLOR: #808080; TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: left">SPEN - Neuberger Berman</TD><TD style="COLOR: #808080; TEXT-ALIGN: center">NBGR</TD><TD style="TEXT-ALIGN: right">SEP</TD><TD style="COLOR: #808080; TEXT-ALIGN: center">A4</TD><TD style="TEXT-ALIGN: right">12,625,843 </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>B1</TD><TD>='All Port Source Upload'!A4</TD></TR><TR><TD>B3</TD><TD>=VLOOKUP(B2,Accts!$M$108:$P$165,2,FALSE)</TD></TR><TR><TD>B4</TD><TD>=VLOOKUP(B2,Accts!M108:P165,Accts!O106)</TD></TR><TR><TD>B5</TD><TD>=HLOOKUP(B4,Models!C10:AC13,Models!B11,FALSE)</TD></TR><TR><TD>B6</TD><TD>=HLOOKUP(B4,Models!C10:AC13,Models!B13,FALSE)</TD></TR><TR><TD>B7</TD><TD>=HLOOKUP(B4&" - "&A7,Models!$B$14:$AC$32,Models!$B$22,FALSE)</TD></TR><TR><TD>B8</TD><TD>=HLOOKUP(B4&" - "&A8,Models!$B$14:$AC$32,Models!$B$22,FALSE)</TD></TR><TR><TD>B9</TD><TD>=Models!A7</TD></TR><TR><TD>B10</TD><TD>=Models!A8</TD></TR><TR><TD>B11</TD><TD>=COUNTIF(Accts!$C$3:$C$51,Portfolios!B2)</TD></TR><TR><TD>B14</TD><TD>=IF(D14="","",VLOOKUP(D14,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C14</TD><TD>=IF($A14>$B$11,"",VLOOKUP($B$2&$A14,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D14</TD><TD>=IF($A14>$B$11,"",VLOOKUP($B$2&$A14,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E14</TD><TD>=IF($A14>$B$11,0,VLOOKUP($B$2&$A14,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A15</TD><TD>=A14+1</TD></TR><TR><TD>B15</TD><TD>=IF(D15="","",VLOOKUP(D15,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C15</TD><TD>=IF($A15>$B$11,"",VLOOKUP($B$2&$A15,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D15</TD><TD>=IF($A15>$B$11,"",VLOOKUP($B$2&$A15,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E15</TD><TD>=IF($A15>$B$11,0,VLOOKUP($B$2&$A15,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A16</TD><TD>=A15+1</TD></TR><TR><TD>B16</TD><TD>=IF(D16="","",VLOOKUP(D16,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C16</TD><TD>=IF($A16>$B$11,"",VLOOKUP($B$2&$A16,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D16</TD><TD>=IF($A16>$B$11,"",VLOOKUP($B$2&$A16,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E16</TD><TD>=IF($A16>$B$11,0,VLOOKUP($B$2&$A16,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A17</TD><TD>=A16+1</TD></TR><TR><TD>B17</TD><TD>=IF(D17="","",VLOOKUP(D17,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C17</TD><TD>=IF($A17>$B$11,"",VLOOKUP($B$2&$A17,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D17</TD><TD>=IF($A17>$B$11,"",VLOOKUP($B$2&$A17,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E17</TD><TD>=IF($A17>$B$11,0,VLOOKUP($B$2&$A17,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A18</TD><TD>=A17+1</TD></TR><TR><TD>B18</TD><TD>=IF(D18="","",VLOOKUP(D18,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C18</TD><TD>=IF($A18>$B$11,"",VLOOKUP($B$2&$A18,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D18</TD><TD>=IF($A18>$B$11,"",VLOOKUP($B$2&$A18,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E18</TD><TD>=IF($A18>$B$11,0,VLOOKUP($B$2&$A18,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A19</TD><TD>=A18+1</TD></TR><TR><TD>B19</TD><TD>=IF(D19="","",VLOOKUP(D19,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C19</TD><TD>=IF($A19>$B$11,"",VLOOKUP($B$2&$A19,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D19</TD><TD>=IF($A19>$B$11,"",VLOOKUP($B$2&$A19,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E19</TD><TD>=IF($A19>$B$11,0,VLOOKUP($B$2&$A19,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A20</TD><TD>=A19+1</TD></TR><TR><TD>B20</TD><TD>=IF(D20="","",VLOOKUP(D20,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C20</TD><TD>=IF($A20>$B$11,"",VLOOKUP($B$2&$A20,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D20</TD><TD>=IF($A20>$B$11,"",VLOOKUP($B$2&$A20,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E20</TD><TD>=IF($A20>$B$11,0,VLOOKUP($B$2&$A20,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A21</TD><TD>=A20+1</TD></TR><TR><TD>B21</TD><TD>=IF(D21="","",VLOOKUP(D21,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C21</TD><TD>=IF($A21>$B$11,"",VLOOKUP($B$2&$A21,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D21</TD><TD>=IF($A21>$B$11,"",VLOOKUP($B$2&$A21,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E21</TD><TD>=IF($A21>$B$11,0,VLOOKUP($B$2&$A21,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A22</TD><TD>=A21+1</TD></TR><TR><TD>B22</TD><TD>=IF(D22="","",VLOOKUP(D22,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C22</TD><TD>=IF($A22>$B$11,"",VLOOKUP($B$2&$A22,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D22</TD><TD>=IF($A22>$B$11,"",VLOOKUP($B$2&$A22,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E22</TD><TD>=IF($A22>$B$11,0,VLOOKUP($B$2&$A22,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>A23</TD><TD>=A22+1</TD></TR><TR><TD>B23</TD><TD>=IF(D23="","",VLOOKUP(D23,Accts!$F$2:$G$51,2,FALSE))</TD></TR><TR><TD>C23</TD><TD>=IF($A23>$B$11,"",VLOOKUP($B$2&$A23,Accts!$E$3:$I$51,C$13,FALSE))</TD></TR><TR><TD>D23</TD><TD>=IF($A23>$B$11,"",VLOOKUP($B$2&$A23,Accts!$E$3:$I$51,D$13,FALSE))</TD></TR><TR><TD>E23</TD><TD>=IF($A23>$B$11,0,VLOOKUP($B$2&$A23,Accts!$E$3:$I$51,E$13,FALSE))</TD></TR><TR><TD>E24</TD><TD>=SUM(E14:E23)</TD></TR><TR><TD>H26</TD><TD>=B3&" - Actual"</TD></TR><TR><TD>H28</TD><TD>="Market Value as of "&TEXT(B1,"MMMM DD, YYYY")</TD></TR><TR><TD>H34</TD><TD>=Models!C16</TD></TR><TR><TD>L34</TD><TD>=SUM(L36:L36)</TD></TR><TR><TD>H35</TD><TD>=Models!C17</TD></TR><TR><TD>H36</TD><TD>=IF(J36="","",IF(LEN(J36)<4,VLOOKUP(K36,'Sec ID'!$A$4:$Z$124,2,FALSE),VLOOKUP(J36,'Sec ID'!$A$4:$Z$124,2,FALSE)))</TD></TR><TR><TD>I36</TD><TD>=IF(H36="","",VLOOKUP(H36,'Sec ID'!$B$4:$C$123,2,FALSE))</TD></TR><TR><TD>J36</TD><TD>=HLOOKUP($B$2,'HLOOKUP Sheet'!$A$1:$L$44,$G36+1,FALSE)</TD></TR><TR><TD>K36</TD><TD>=$C$14</TD></TR><TR><TD>L36</TD><TD>=IF(K36="","",IF(J36="SEP",SUMIF('All Port Source Upload'!$R$2:$R$6009,Portfolios!K36,'All Port Source Upload'!$Z$2:$Z$6009),SUMIF('All Port Source Upload'!$X$2:$X$6009,K36&J36,'All Port Source Upload'!$Z$2:$Z$6009)))</TD></TR><TR><TD>H37</TD><TD>=IF(J37="","",IF(LEN(J37)<4,VLOOKUP(K37,'Sec ID'!$A$4:$Z$124,2,FALSE),VLOOKUP(J37,'Sec ID'!$A$4:$Z$124,2,FALSE)))</TD></TR><TR><TD>I37</TD><TD>=IF(H37="","",VLOOKUP(H37,'Sec ID'!$B$4:$C$123,2,FALSE))</TD></TR><TR><TD>J37</TD><TD>=IF(K37="","",HLOOKUP($B$2,'HLOOKUP Sheet'!$A$1:$L$44,$G37+1,FALSE))</TD></TR><TR><TD>L37</TD><TD>=IF(K37="","",IF(J37="SEP",SUMIF('All Port Source Upload'!$R$2:$R$6009,Portfolios!K37,'All Port Source Upload'!$Z$2:$Z$6009),SUMIF('All Port Source Upload'!$X$2:$X$6009,K37&J37,'All Port Source Upload'!$Z$2:$Z$6009)))</TD></TR><TR><TD>H39</TD><TD>=Models!Q18</TD></TR><TR><TD>L39</TD><TD>=SUM(L41:L54)</TD></TR><TR><TD>H40</TD><TD>=Models!Q19</TD></TR><TR><TD>H41</TD><TD>=IF(J41="","",IF(LEN(J41)<4,VLOOKUP(K41,'Sec ID'!$A$4:$Z$124,2,FALSE),VLOOKUP(J41,'Sec ID'!$A$4:$Z$124,2,FALSE)))</TD></TR><TR><TD>I41</TD><TD>=IF(H41="","",VLOOKUP(H41,'Sec ID'!$B$4:$C$123,2,FALSE))</TD></TR><TR><TD>L41</TD><TD>=IF(K41="","",IF(J41="SEP",SUMIF('All Port Source Upload'!$R$2:$R$6009,Portfolios!K41,'All Port Source Upload'!$Z$2:$Z$6009),SUMIF('All Port Source Upload'!$X$2:$X$6009,K41&J41,'All Port Source Upload'!$Z$2:$Z$6009)))</TD></TR></TBODY></TABLE></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
