Excel problem

  Shas 18:56 16 Apr 07
Locked

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 07

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 07

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 07

"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 07

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 07

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 07

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

  Shas 10:43 17 Apr 07

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 07

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 07

Correction 'VoG will know....'

  Simsy 11:25 17 Apr 07

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,

Regards,

Simsy

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

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now