Formulas

  • Thread starter Thread starter xtrucker
  • Start date Start date
X

xtrucker

Two problems have developed in only one worksheet file. I
can not format the date column. no matter what I chose
for date entering always comes up 1/1/00 second problem
has just occored dragging the balance on a check book
file has worked until now all I get is Value ?? small
help icon says error in formula but this formula has been
working and continue dragging to cells for about 350
cells is there a limit to cells that you can drag a
formula?
..
 
Hi xtrucker,

As far as the date problem, I would look at the cell
formats. I bet it's something simple that you're
overlooking.

As far as drag copying a formula, there is no set limit on
this. The #VALUE error means just exactly that. A value
referenced in the formula is of the wrong type. It might
be referring to a TEXT value. Try posting the formula with
a description of your worksheet.

Biff
 
Hi xtrucker,
You might use Conditional Formatting to find the
problem. Say you have your Debits in column E,
your Credits in Column F, and your Balance in Column G.

Select columns E:G then with cell E1 as the active cell
on which your Conditional Formatting formula will be based on,
Format, Conditional Formatting,
Formula is: =ISTEXT(E1)
format button, patterns, choose a color, OK, OK

If you want to do a little better you can *Add* a second
C.F. test to make sure you aren't restarting the balance column.
Select column G1 then with G1 as the active cell
Format, Conditional Formatting,
[Add] button
Formula is: =NOT(ISNUMBER(G1))
format button, patters, choose a different color, OK, OK

You should see very easily now why you have #VALUE! errors
because you have text values. The second color will indicate
where you have an empty row and your balances start over rather
than continue.

More on Conditional Formatting on
http://www.mvps.org/dmcritchie/excel/condfmt.htm
More on Checkbook type balances and use of OFFSET in
http://www.mvps.org/dmcritchie/excel/insrtrow.htm.
More on checking what
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat
Reason not to use SUM in checkbook balances
http://google.com/groups?as_umsgid=ugDxrXayCHA.2592@TK2MSFTNGP10

The information in this posting has been added as a topic
#VALUE! Errors on Checkbook Balance Column (#VALUE)
within Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#value
 
Back
Top