Excel problem

  Shas 18:56 16 Apr 2007

I have a simple cashbook workbook with 12 sheets, April - March.
On the debit side,
Column A date,
Column B paid to,
Column D (minus) amount;

Credit side
Column F date,
Column G received from,
Column I (plus) amount;

Column K running total/balance, starting with a brought forward figure and adding the ins and outs as it goes down, for example, K2+D3+I3.

Can anyone please explain why, immediately after I saved the workbook, I suddenly got the following #VALUE error message in one particular cell, (it starts in K24 and continues down the sheet): ‘A value used in the formula is of the wrong data type’, but the formula follows exactly the same format throughout Column K. If I delete the formula in K24, everything that follows reverts to as it should, i.e. the running balance plus what’s been paid out and paid in.

I traced the error and it points to D23, but there’s nothing I can see wrong with what has been put in that cell, and I even get the error message if I delete the amount in D23.

I’ve checked the format of all the cells and they are all the same, in number format.

I’ve inserted a fresh worksheet and copied my data across and the new one is fine, but I’m just baffled as to why my original worksheet seemed to get corrupted.

  VoG II 19:18 16 Apr 2007

D23 or another cell that the formula depends on may have been formatted as text instead of a number.

Try selecting all of your data, Data > Text to Columns and click Finish.

  Shas 22:18 16 Apr 2007

Thanks VoG™, I'll check that out tomorrow when I get back into work.

What I don't understand is why it suddenly changed, just by saving the workbook?, but I'll see what your suggestion brings. Thanks.

  Shas 09:20 17 Apr 2007

"Try selecting all of your data, Data > Text to Columns and click Finish."

Did that, but it hasn't changed a thing. I evaluated the formula in the affected cell and five steps in, instead of showing the expected 12345+0, it shows 12345+"", but I can't find out why.

I've double checked all the cells' formatting again, and everything is showing as it should be.

  I am Spartacus 10:11 17 Apr 2007

Try using Autofill to copy the formulas in each column again. I've found that if you cut a value out of a cell then this error occurs.

  Shas 10:27 17 Apr 2007

Thanks, I am Spartacus, just tried it and still no joy I'm afraid. In fact, I've tried every way I know how of inserting the formula and nothing works.

If I delete the formula in this particular cell, the following cells behave as they should, but as soon as I re-enter the formula (either by Autofill or manually) it throws up the error.

  I am Spartacus 10:32 17 Apr 2007

Maybe the Tools, Auditing, Trace Error option might help track it down?

  Shas 10:43 17 Apr 2007

Tried all that as well, and the Evaluate Formula option. As I said in my original post, even if I delete the cell that the trace points to, the error is still there. Strange.

  I am Spartacus 10:54 17 Apr 2007

Well, it looks as though the formula is trying to evaluate text. Are you sure there's not a spurious text character in one of the cells? Maybe a comma instead of a decimal point?

Grasping at straws here.

VoG will now though I bet.

  I am Spartacus 10:55 17 Apr 2007

Correction 'VoG will know....'

  Simsy 11:25 17 Apr 2007

a number, not a formula... is that right?

You say you've traced the error to D23; where does this apear in a formula? I expect it to be K23, but you've said K24... I've probably just visualised your sheet incorrectly...

What other cells are referenced in the formula in K23/K24? Have you carefully checked the formatting of those cells?

Also, you indicate that the formula, (in K3?) is =K2+D3+I3 Should one of those be a minus rather than a plus? If so, are you perhaps going into some negative territory which may be having an adverse affect on some other, (unknown to us), formula that's involved?

I'm clutching at straws rather with the above, but perhaps it might help.

You could try deleting row 23, (the wholw row, not just the contents), and then inserting a row again and reentering the data. Perhaps starting with the row above the problem might be better.

Failing that, you could try coppying all the data and doing a "Paste Special" into a new spreadsheet, choosing the "Values" option. You'd have to recreate the formula and copyit down after, but if it is a corrupted worksheet this may solve things.

Good luck,



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

11 best portfolio websites for designers and artists

Office for Mac buying guide: Price, Office 2017 rumours & new features

Comment désactiver les programmes qui s'exécutent au démarrage de Windows 10 ?