[Solved] Eliminate formula error message

Discuss the spreadsheet application

[Solved] Eliminate formula error message

Postby djd » Tue Jun 17, 2008 7:56 pm

I have a workbook that contains over a hundred sheets that are used to track individual expenses for construction projects. I pull the totals from these individual sheets to a master sheet that summarizes all the individual expenses and totals them. On some projects, some of the expense sheets are not used and I would like to be able to delete the expense sheets but when I do that an error occurrs (#NAME?) on the master sheet because the formula can't locate the expense sheet anymore. Is there a formula format that I can use that will not show this error message? Right now I have to go to these cells and delete the formula to clear the error, but was hoping I could eliminate this step.


Using Oov2.4.1, Vista
Last edited by Hagar Delest on Wed Jun 18, 2008 10:31 pm, edited 1 time in total.
Reason: tagged the thread as Solved.
Posts: 18
Joined: Tue May 20, 2008 1:25 am

Re: Eliminate formula error message

Postby squenson » Tue Jun 17, 2008 9:13 pm

I am not sure that you can trap #REF! or #NAME! errors in formulas. I tried with ISREF() function, but it did not provide the expected results when I delete a sheet containing a referenced cell.
LibreOffice on Ubuntu 14.04
User avatar
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Eliminate formula error message

Postby keme » Tue Jun 17, 2008 10:04 pm

The "catchall" functions for errors are ISERR() (which returns a true condition for all errors except #N/A, and false otherwise) and ISERROR() (which returns true for all errors and false if no error is present).

Looks like you want to ignore all errors instead of catching them. For that, use the N() function, which will return the correct number if possible, and zero if there's an error.

Regardless of which option you choose, be sure to catch the error as early in the calculation as possible. Using N() around the last calculation will probably only yield a big zero.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice on Mepis Antix MX 14
User avatar
Posts: 3394
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Eliminate formula error message

Postby djd » Wed Jun 18, 2008 1:46 am

Thanks keme

I have tried the N syntax but it still returns an error. Here is a sample of the formula I am using currently: =IF(ISBLANK(MCE.C11);"";N('261'.E42))

If I delete sheet 261, I get a reference error in the formula, but nothing in the cell, which is what I want. But when I save the file and reopen it, I get #NAME? in the cell.

Is the formula incorrect or is Openoffice not handling the formula correctly?

Using Oo v2.4.1, Vista
Posts: 18
Joined: Tue May 20, 2008 1:25 am

Re: Eliminate formula error message

Postby Duhhh » Wed Jun 18, 2008 8:06 pm

Why would you need N()? You didn't say you always wanted numeric results, and that's what N() does. It doesn't strip out errors, it converts things to numbers.

I think you want: =IF(ISERROR(INDIRECT("'261.E42"));"";INDIRECT("261.E42"))

What this does: if there's an error in cell "261.E42" (or the cell doesn't exist) you'll get a blank. Otherwise, you get whatever is in cell "261.E42" - text, number, whatever.

The reason I use INDIRECT is so the formula won't crater when you delete the sheet. Without the INDIRECT, the formula will still work, but when you delete sheet "261" the formula would change "261" to #REF. If you ever wanted to add "261" back, it wouldn't fix itself. By using INDIRECT, you can delete and re-create sheet 261, and this formula will always find the target cell.
OOo 3.3.X on Ms Windows XP
Posts: 49
Joined: Tue Jun 03, 2008 6:10 pm
Location: USA

Re: Eliminate formula error message

Postby djd » Wed Jun 18, 2008 8:21 pm

Thanks Duhhh

That gives me exactly the results I was looking for.

Thanks again.
Posts: 18
Joined: Tue May 20, 2008 1:25 am

Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 34 guests