Reference Not Valid

synergy46

Board Regular
Joined
Jan 8, 2010
Messages
58
I have a Detail sheet with a list with a popup that shows accounts; cash, supplies etc... with a blank row at the bottom indicated with a * ?

****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Excel.Sheet"> ****** name="Generator" content="Microsoft Excel 2008"> <link id="Main-File" rel="Main-File" href="file://localhost/Users/Ron/Library/Caches/TemporaryItems/msoclip/0/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding:0px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {mso-number-format:0; text-align:center; background:#FFFF99; mso-pattern:#FFF58C none; mso-protection:unlocked visible;} .xl25 {mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; text-align:right; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl26 {background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl27 {text-align:center; mso-protection:unlocked visible;} .xl28 {mso-protection:unlocked visible;} .xl29 {mso-number-format:"m\/d\/yy"; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} ruby {ruby-align:left;} rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;} --> </style> <table style="border-collapse: collapse;" width="349" border="0" cellpadding="0" cellspacing="0"> <!--StartFragment--> <col style="" width="119"> <col style="" width="77"> <col style="" width="79"> <col style="" width="74"> <tbody><tr style="" height="14"> <td class="xl26" width="119" height="14">Scholarships</td> <td class="xl29" x:num="39054.0" align="right" width="77">12/4/10</td> <td class="xl24" x:num="154.0" width="79">154</td> <td class="xl25" x:num="2500.0" width="74"> $2,500.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Postage</td> <td class="xl29" x:num="39055.0" align="right">12/5/10</td> <td class="xl24" x:num="155.0">155</td> <td class="xl25" x:num="33.0"> $33.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Printing and Copy</td> <td class="xl29" x:num="38749.0" align="right">2/2/10</td> <td class="xl24">debit card</td> <td class="xl25" x:num="60.0"> $60.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Degree expense</td> <td class="xl29" x:num="38749.0" align="right">2/2/10</td> <td class="xl24" x:num="156.0">156</td> <td class="xl25" x:num="100.0"> $100.00 </td> </tr> <tr style="page-break-before: always;" height="13"> <td class="xl26" height="13">Degree expense</td> <td class="xl29" x:num="38750.0" align="right">2/3/10</td> <td class="xl24" x:num="157.0">157</td> <td class="xl25" x:num="55.0"> $55.00 </td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl28">
</td> <td class="xl28">
</td> <td class="xl28">
</td> </tr> <!--EndFragment--> </tbody></table>

I create a pivot table and it looks ok. But, ...

a) for some strange reason there is a (blank) entry at the bottom of my date column... There is no blank at the bottom of my LIST, except for the next input row. Is that what is causing the blank? If so, how can I get rid of it?
****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Excel.Sheet"> ****** name="Generator" content="Microsoft Excel 2008"> <link id="Main-File" rel="Main-File" href="file://localhost/Users/Ron/Library/Caches/TemporaryItems/msoclip/0/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding:0px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {border:.5pt solid black;} .xl25 {border-top:.5pt solid black; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl26 {border-top:.5pt solid black; border-right:none; border-bottom:.5pt solid black; border-left:.5pt solid black;} .xl27 {border-top:.5pt solid black; border-right:.5pt solid black; border-bottom:none; border-left:.5pt solid black;} .xl28 {border-top:none; border-right:.5pt solid black; border-bottom:none; border-left:.5pt solid black;} .xl29 {text-align:center; mso-protection:unlocked visible;} .xl30 {border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl31 {mso-number-format:"Short Date"; border-top:.5pt solid black; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl32 {mso-number-format:"Short Date"; border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid black;} ruby {ruby-align:left;} rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;} --> </style> <table style="border-collapse: collapse;" width="110" border="0" cellpadding="0" cellspacing="0"> <!--StartFragment--> <col style="" width="68"> <col style="" width="42"> <tbody><tr style="" height="15"> <td class="xl29" width="68" height="15">
</td> <td class="xl29" width="42">
</td> </tr> <tr height="13"> <td class="xl25" height="13">Sum of Amt</td> <td class="xl27">
</td> </tr> <tr height="13"> <td class="xl24" height="13">Date</td> <td class="xl27">Total</td> </tr> <tr height="13"> <td class="xl31" x:num="38718.0" align="right" height="13">1/2/10</td> <td class="xl27" x:num="1200.0" align="right">1200</td> </tr> <tr height="13"> <td class="xl32" x:num="38721.0" align="right" height="13">1/5/10</td> <td class="xl28" x:num="78.0" align="right">78</td> </tr> <tr height="13"> <td class="xl32" x:num="38732.0" align="right" height="13">1/16/10</td> <td class="xl28" x:num="1122.0" align="right">1122</td> </tr> <tr height="13"> <td class="xl32" x:num="38733.0" align="right" height="13">1/17/10</td> <td class="xl28" x:num="355.0" align="right">355</td> </tr> <tr height="13"> <td class="xl32" x:num="38749.0" align="right" height="13">2/2/10</td> <td class="xl28" x:num="160.0" align="right">160</td> </tr> <tr height="13"> <td class="xl32" x:num="38750.0" align="right" height="13">2/3/10</td> <td class="xl28" x:num="4055.0" align="right">4055</td> </tr> <tr height="13"> <td class="xl32" x:num="38776.0" align="right" height="13">3/1/10</td> <td class="xl28" x:num="50.0" align="right">50</td> </tr> <tr height="13"> <td class="xl32" x:num="38778.0" align="right" height="13">3/3/10</td> <td class="xl28" x:num="477.0" align="right">477</td> </tr> <tr height="13"> <td class="xl32" x:num="38779.0" align="right" height="13">3/4/10</td> <td class="xl28" x:num="77.0" align="right">77</td> </tr> <tr height="13"> <td class="xl32" x:num="38780.0" align="right" height="13">3/5/10</td> <td class="xl28" x:num="222.0" align="right">222</td> </tr> <tr height="13"> <td class="xl32" x:num="38810.0" align="right" height="13">4/4/10</td> <td class="xl28" x:num="2788.0" align="right">2788</td> </tr> <tr height="13"> <td class="xl32" x:num="38811.0" align="right" height="13">4/5/10</td> <td class="xl28" x:num="1000.0" align="right">1000</td> </tr> <tr height="13"> <td class="xl32" x:num="38812.0" align="right" height="13">4/6/10</td> <td class="xl28" x:num="888.0" align="right">888</td> </tr> <tr height="13"> <td class="xl32" x:num="38828.0" align="right" height="13">4/22/10</td> <td class="xl28" x:num="999.0" align="right">999</td> </tr> <tr height="13"> <td class="xl32" x:num="38830.0" align="right" height="13">4/24/10</td> <td class="xl28" x:num="8888.0" align="right">8888</td> </tr> <tr height="13"> <td class="xl32" x:num="38837.0" align="right" height="13">5/1/10</td> <td class="xl28" x:num="500.0" align="right">500</td> </tr> <tr height="13"> <td class="xl32" x:num="38839.0" align="right" height="13">5/3/10</td> <td class="xl28" x:num="1000.0" align="right">1000</td> </tr> <tr style="" height="13"> <td class="xl32" x:num="38841.0" align="right" height="13">5/5/10</td> <td class="xl28" x:num="800.0" align="right">800</td> </tr> <tr style="" height="13"> <td class="xl32" x:num="38904.0" align="right" height="13">7/7/10</td> <td class="xl28" x:num="55.0" align="right">55</td> </tr> <tr style="" height="13"> <td class="xl32" x:num="38930.0" align="right" height="13">8/2/10</td> <td class="xl28" x:num="100.0" align="right">100</td> </tr> <tr height="13"> <td class="xl32" x:num="39024.0" align="right" height="13">11/4/10</td> <td class="xl28" x:num="100.0" align="right">100</td> </tr> <tr height="13"> <td class="xl32" x:num="39054.0" align="right" height="13">12/4/10</td> <td class="xl28" x:num="2500.0" align="right">2500</td> </tr> <tr height="13"> <td class="xl32" x:num="39055.0" align="right" height="13">12/5/10</td> <td class="xl28" x:num="33.0" align="right">33</td> </tr> <tr height="13"> <td class="xl30" height="13">(blank)</td> <td class="xl28">
</td> </tr> <tr height="13"> <td class="xl26" height="13">Grand Total</td> <td class="xl24" x:num="27447.0" align="right">27447</td> </tr> <!--EndFragment--> </tbody></table>
b) My PT (above) has a column of dates above (blank). I had previously been able to GROUP these dates by Year and Month but now when I try and Group, XL automatically adds a "Group1" label and column to the left of date. What is that about?

****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Excel.Sheet"> ****** name="Generator" content="Microsoft Excel 2008"> <link id="Main-File" rel="Main-File" href="file://localhost/Users/Ron/Library/Caches/TemporaryItems/msoclip/0/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding:0px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {border:.5pt solid black;} .xl25 {border-top:.5pt solid black; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl26 {border-top:.5pt solid black; border-right:none; border-bottom:none; border-left:none;} .xl27 {border-top:none; mso-border-top-source:background; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl28 {border-top:.5pt solid black; border-right:none; border-bottom:.5pt solid black; border-left:none; mso-border-left-source:background;} .xl29 {border-top:.5pt solid black; border-right:none; border-bottom:.5pt solid black; border-left:.5pt solid black;} .xl30 {border-top:.5pt solid black; border-right:.5pt solid black; border-bottom:none; border-left:.5pt solid black;} .xl31 {border-top:none; border-right:.5pt solid black; border-bottom:none; border-left:.5pt solid black;} .xl32 {text-align:center; mso-protection:unlocked visible;} .xl33 {mso-protection:unlocked visible;} .xl34 {border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl35 {mso-number-format:"m\/d\/yyyy"; mso-protection:unlocked visible;} .xl36 {mso-number-format:"Short Date"; border-top:.5pt solid black; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl37 {mso-number-format:"Short Date"; border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid black;} ruby {ruby-align:left;} rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;} --> </style> <table style="border-collapse: collapse;" width="159" border="0" cellpadding="0" cellspacing="0"> <!--StartFragment--> <col style="" width="68"> <col style="" width="49"> <col style="" width="42"> <tbody><tr style="" height="15"> <td class="xl32" width="68" height="15">
</td> <td class="xl32" width="49">
</td> <td class="xl32" width="42">
</td> </tr> <tr height="13"> <td class="xl25" colspan="2" height="13">Sum of Amt</td> <td class="xl30">
</td> </tr> <tr height="13"> <td class="xl24" height="13">Date2</td> <td class="xl24">Date</td> <td class="xl30">Total</td> </tr> <tr height="13"> <td class="xl25" height="13">Group1</td> <td class="xl36" x:num="38718.0" align="right">1/2/10</td> <td class="xl30" x:num="1200.0" align="right">1200</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38721.0" align="right">1/5/10</td> <td class="xl31" x:num="78.0" align="right">78</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38732.0" align="right">1/16/10</td> <td class="xl31" x:num="1122.0" align="right">1122</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38733.0" align="right">1/17/10</td> <td class="xl31" x:num="355.0" align="right">355</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38749.0" align="right">2/2/10</td> <td class="xl31" x:num="160.0" align="right">160</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38750.0" align="right">2/3/10</td> <td class="xl31" x:num="4055.0" align="right">4055</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38776.0" align="right">3/1/10</td> <td class="xl31" x:num="50.0" align="right">50</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38778.0" align="right">3/3/10</td> <td class="xl31" x:num="477.0" align="right">477</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38779.0" align="right">3/4/10</td> <td class="xl31" x:num="77.0" align="right">77</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38780.0" align="right">3/5/10</td> <td class="xl31" x:num="222.0" align="right">222</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38810.0" align="right">4/4/10</td> <td class="xl31" x:num="2788.0" align="right">2788</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38811.0" align="right">4/5/10</td> <td class="xl31" x:num="1000.0" align="right">1000</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38812.0" align="right">4/6/10</td> <td class="xl31" x:num="888.0" align="right">888</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38828.0" align="right">4/22/10</td> <td class="xl31" x:num="999.0" align="right">999</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38830.0" align="right">4/24/10</td> <td class="xl31" x:num="8888.0" align="right">8888</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38837.0" align="right">5/1/10</td> <td class="xl31" x:num="500.0" align="right">500</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38839.0" align="right">5/3/10</td> <td class="xl31" x:num="1000.0" align="right">1000</td> </tr> <tr style="" height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38841.0" align="right">5/5/10</td> <td class="xl31" x:num="800.0" align="right">800</td> </tr> <tr style="" height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38904.0" align="right">7/7/10</td> <td class="xl31" x:num="55.0" align="right">55</td> </tr> <tr style="" height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="38930.0" align="right">8/2/10</td> <td class="xl31" x:num="100.0" align="right">100</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="39024.0" align="right">11/4/10</td> <td class="xl31" x:num="100.0" align="right">100</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="39054.0" align="right">12/4/10</td> <td class="xl31" x:num="2500.0" align="right">2500</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl37" x:num="39055.0" align="right">12/5/10</td> <td class="xl31" x:num="33.0" align="right">33</td> </tr> <tr height="13"> <td class="xl27" height="13">
</td> <td class="xl34">(blank)</td> <td class="xl31">
</td> </tr> <tr height="13"> <td class="xl29" colspan="2" height="13">Grand Total</td> <td class="xl24" x:num="27447.0" align="right">27447</td> </tr> <tr height="13"> <td class="xl35" height="13">
</td> <td class="xl35">
</td> <td class="xl33">
</td> </tr> <!--EndFragment--> </tbody></table> Your thoughts and insights are appreciated.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Forum statistics

Threads
1,141,402
Messages
5,706,245
Members
421,433
Latest member
yash0468

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
Top