Huawei P10 review
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;
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.
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.
"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.
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.
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.
Maybe the Tools, Auditing, Trace Error option might help track it down?
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.
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.
Correction 'VoG will know....'
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.
This thread is now locked and can not be replied to.