List formulas mysteriously going to #VALUE...

[synergy46]

... but if I put the cursor in the top of a column and then put the cursor at the beginning of the formula and press ENTER I am asked if I want the formula to be copied down the column. After this, everything is as expected.

What causes this? How can I prevent it? The formulas are right so I don't know how to get around it....

Thanks

****** name="Title" content="Actual vs Budget"> ****** name="Keywords" content=""> ****** 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"> <title>Actual vs Budget</title> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} .font5 {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;} .font6 {color:black; font-size:9.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0;} .font7 {color:black; font-size:9.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0;} td {padding:0px; mso-ignoreadding; 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-protection:unlocked visible;} .xl25 {text-align:right;} .xl26 {mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; border-top:.5pt solid windowtext; border-right:none; border-bottom:2.0pt double windowtext; border-left:none;} .xl27 {mso-number-format:"\#\,\#\#0\.00_\)\;\[Red\]\\\(\#\,\#\#0\.00\\\)"; border-top:.5pt solid windowtext; border-right:none; border-bottom:2.0pt double windowtext; border-left:none;} .xl28 {mso-number-format:"\#\,\#\#0\.00_\)\;\[Red\]\\\(\#\,\#\#0\.00\\\)";} .xl29 {font-size:9.0pt; font-style:italic; mso-number-format:"\#\,\#\#0\.00_\)\;\[Red\]\\\(\#\,\#\#0\.00\\\)"; text-align:center; border-top:none; border-right:none; border-bottom:1.0pt solid #000090; border-left:none;} .xl30 {mso-number-format:"\@"; text-align:center; background:#FFFF99; mso-pattern:#FFF58C none; mso-protection:unlocked visible;} .xl31 {font-size:9.0pt; font-style:italic; mso-number-format:"\@"; text-align:center; border-top:none; border-right:none; border-bottom:1.0pt solid #000090; border-left:none; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl32 {mso-number-format:"\@"; text-align:center; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl33 {text-align:center; background:#FFFF99; mso-pattern:auto none;} .xl34 {mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; text-align:center; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl35 {mso-number-format:"_\(* \#\,\#\#0\.00_\)\;_\(* \\\(\#\,\#\#0\.00\\\)\;_\(* \0022-\0022??_\)\;_\(\@_\)"; text-align:center; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl36 {color:#DD0806; text-align:center; mso-protection:unlocked visible;} .xl37 {font-size:9.0pt; font-style:italic; mso-number-format:"mmm\\ dd\\ yyyy"; text-align:center; border-top:none; border-right:none; border-bottom:1.0pt solid #000090; border-left:none; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl38 {mso-number-format:"mmm\\ dd\\ yyyy"; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl39 {font-size:9.0pt; font-style:italic; mso-number-format:"\#\,\#\#0\.00_\)\;\[Red\]\\\(\#\,\#\#0\.00\\\)"; text-align:center; border-top:none; border-right:none; border-bottom:1.0pt solid #000090; border-left:none; mso-protection:locked hidden;} .xl40 {mso-number-format:"\#\,\#\#0\.00_\)\;\[Red\]\\\(\#\,\#\#0\.00\\\)"; mso-protection:locked hidden;} .xl41 {font-size:9.0pt; font-style:italic; mso-number-format:"_\(* \#\,\#\#0\.00_\)\;_\(* \\\(\#\,\#\#0\.00\\\)\;_\(* \0022-\0022??_\)\;_\(\@_\)"; text-align:center; border-top:none; border-right:none; border-bottom:1.0pt solid #000090; border-left:none; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl42 {mso-number-format:"_\(* \#\,\#\#0\.00_\)\;_\(* \\\(\#\,\#\#0\.00\\\)\;_\(* \0022-\0022??_\)\;_\(\@_\)"; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl43 {mso-number-format:"_\(* \#\,\#\#0\.00_\)\;_\(* \\\(\#\,\#\#0\.00\\\)\;_\(* \0022-\0022??_\)\;_\(\@_\)"; text-align:right; background:#FFFF99; mso-pattern:auto none; mso-protection:unlocked visible;} .xl44 {text-align:center; vertical-align:top; mso-protection:unlocked visible;} .xl45 {text-align:center; vertical-align:top;} .xl46 {color:#DD0806; text-align:center;} 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;" border="0" cellpadding="0" cellspacing="0" width="498"> <!--StartFragment--> <col width="75"> <col style="" width="71"> <col style="" width="59"> <col style="" width="88"> <col style="" width="86"> <col style="" width="63"> <col style="" width="56"> <tbody><tr style="" height="21"> <td class="xl36" height="21" width="75">B</td> <td class="xl36" width="71">C</td> <td class="xl24" width="59"></td> <td colspan="3" class="xl44" width="237">INPUT LIST TABLE</td> <td class="xl46" width="56">H</td> </tr> <tr height="14"> <td class="xl37" height="14">Date</td> <td class="xl31">Type</td> <td class="xl31">Check No.</td> <td class="xl41"> Amt </td> <td class="xl39">Jan Actual</td> <td class="xl29">Feb Actual</td> <td class="xl29">Mar Actual</td> </tr> <tr height="13"> <td class="xl38" x:num="38719.0" align="right" height="13">Jan 03 2010</td> <td class="xl34"> Check </td> <td class="xl32" x:num="101.0">101</td> <td class="xl42" x:num="-35.0" align="right"> (35.00)</td> <td class="xl40" x:num="-35.0" align="right">(35.00)</td> <td class="xl40"></td> <td class="xl40" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38719.0" align="right" height="13">Jan 03 2010</td> <td class="xl35"> Deposit </td> <td class="xl32" x:num="102.0">102</td> <td class="xl42" x:num="-50.0" align="right"> (50.00)</td> <td class="xl40" x:num="-50.0" align="right">(50.00)</td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38807.0" align="right" height="13">Apr 01 2010</td> <td class="xl35"> Check </td> <td class="xl32" x:num="103.0">103</td> <td class="xl42" x:num="-75.0" align="right"> (75.00)</td> <td class="xl40"></td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38837.0" align="right" height="13">May 01 2010</td> <td class="xl35"> Deposit </td> <td class="xl32">-</td> <td class="xl42" x:num="100.0" align="right"> 100.00 </td> <td class="xl40"></td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38902.0" align="right" height="13">Jul 05 2010</td> <td class="xl35"> Debit Card </td> <td class="xl32">-</td> <td class="xl42" x:num="-133.0" align="right"> (133.00)</td> <td class="xl40"></td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38776.0" align="right" height="13">Mar 01 2010</td> <td class="xl33">Check</td> <td class="xl30">104</td> <td class="xl43" x:num="-75.0"> (75.00)</td> <td class="xl40"></td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38779.0" align="right" height="13">Mar 04 2010</td> <td class="xl33">Deposit</td> <td class="xl30"> </td> <td class="xl43" x:num="200.0"> 200.00 </td> <td class="xl40"></td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr style="" height="13"> <td class="xl38" x:num="38846.0" align="right" height="13">May 10 2010</td> <td class="xl33">Check</td> <td class="xl30">105</td> <td class="xl43" x:num="-74.0"> (74.00)</td> <td class="xl40"></td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38841.0" align="right" height="13">May 05 2010</td> <td class="xl33">Check</td> <td class="xl30">106</td> <td class="xl43" x:num="-36.0"> (36.00)</td> <td class="xl40"></td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38757.0" align="right" height="13">Feb 10 2010</td> <td class="xl33">Check</td> <td class="xl30">107</td> <td class="xl43" x:num="-50.0"> (50.00)</td> <td class="xl40"></td> <td class="xl28" x:num="-50.0" align="right">(50.00)</td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38719.0" align="right" height="13">Jan 03 2010</td> <td class="xl33">Check</td> <td class="xl30">108</td> <td class="xl43" x:num="-56.0"> (56.00)</td> <td class="xl40" x:num="-56.0" align="right">(56.00)</td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38721.0" align="right" height="13">Jan 05 2010</td> <td class="xl33">Deposit</td> <td class="xl30"> </td> <td class="xl43" x:num="2200.0"> 2,200.00 </td> <td class="xl40" x:num="2200.0" align="right">2,200.00 </td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="38778.0" align="right" height="13">Mar 03 2010</td> <td class="xl33">Deposit</td> <td class="xl30"> </td> <td class="xl43" x:num="100.0"> 100.00 </td> <td class="xl40"></td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="13"> <td class="xl38" x:num="39072.0" align="right" height="13">Dec 22 2010</td> <td class="xl33">Deposit</td> <td class="xl30"> </td> <td class="xl43" x:num="210.0"> 210.00 </td> <td class="xl40"></td> <td class="xl28"></td> <td class="xl28" align="center">#VALUE!</td> </tr> <tr height="14"> <td class="xl26" height="14"> </td> <td class="xl26"> </td> <td class="xl26"> </td> <td class="xl26"> </td> <td class="xl27" x:num="2059.0" align="right">2,059.00 </td> <td class="xl27" x:num="-50.0" align="right">(50.00)</td> <td class="xl27" align="center">#VALUE!</td> </tr> <tr height="14"> <td class="xl24" height="14"></td> <td class="xl24"></td> <td class="xl24"></td> <td class="xl24"></td> <td class="xl25"></td> <td class="xl25"></td> <td class="xl25"></td> </tr> <!--EndFragment--> </tbody></table>

 

[jproffer]

What is the formula?

 

[synergy46]

The formula is:
=IF(MONTH($B4)=COLUMN()-5,$E4,"")

The strange thing about this is that after I 're activate' the formula (and don't change anything), I can copy it across the list. And, everything works as expected. I copied across all columns of the list, save the spreadsheet and then reopened it. Everything was normal. huh?

 

[jproffer]

When I pasted your table into a WS, the dates weren't formatted as dates, which messed up the formulas...but I assume your dates are actually dates, since going to edit mode and back out fixes your problem.

Other than that, I couldn't get it to error out for me. I tried closing and reopening the WB, dragging it over where column()-5 would be negative...no errors.

I'm sure this is less than helpful to you, but without being able to reproduce the error you're getting, I'm afraid I can't help.

Hope you get it sorted out.

 

[synergy46]

Thanks for trying. When I opened the WB this morning, everything was normal. Maybe it is some weird MS thing?

 

[synergy46]

Update on #VALUE problem....

It happened again. When I chose an account from the column A drop down, everything went to #VALUE in the Months column F through Q.

What I don't get is the the formula in the LIST columns F - Q is
=IF(MONTH($B4)=COLUMN()-5,$E4,"")

which does not reference column A at all!!!?????