Office & Productivity Software forum

General discussion

Help with Excel / =IF(ISERROR) function

Hi, I need some help. I have a workbook with 3 sheets, 1. a report that draws data from 2. a page to manually key in data and 3. a sheet where data is imported.

I need to make a cell that, when the imported data is refreshed, simply displays a field from page 3. However, on occasion, this field may be empty. When this is the case, I get a #REF! error for this field. Insted of getting the #REF! error, I'm trying to get it to pull from a corresponding cell on page 2 that the user can key in (in the case that it is missing from page 3).

After looking through the help files, here's what I found that seems like it will work, but I keep getting formula errors:

=IF(ISERROR('Auto Entry'!C332<>0),'Auto Entry'!C332,'Manual Entry'!B8)

As I read it, if AutoEntryC332 is not empty, then this formula should display the contents of AutoEntryC332. If it is empty, then it should display ManualEntryB8 instead of the error.

What am I doing wrong?

Thanks!
Ryan

Discussion is locked
You are posting a reply to: Help with Excel / =IF(ISERROR) function
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Help with Excel / =IF(ISERROR) function
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
IF(ISERROR)

In reply to: Help with Excel / =IF(ISERROR) function

I think the formula does have an error in it.

If you use the IF(ISERROR) function, to check for an #REF error, the function needs to be of the type;

=IF(ISERROR(B6),"error",B6)

In that example, B6 refers to cell A1 in another sheet which I made to be non-existant, hence B6 shows #REF.

So, I then put this forumla into D6. What it does is this.

If B6 shows an error, then ISERROR is True. If B6 does not show an error, then ISERROR is False.

If True, then "error" is displayed in D6, (without the quotes).

If False, then the value of B6 is displayed.

You can use this. The data is imported into a range of cells, then copied using the ISERROR function into another range.

Then use the "2nd" range to do your checking and calculating, and referencing for the other sheets.

I hope this helps

Mark

Collapse -
ISERROR not required

In reply to: Help with Excel / =IF(ISERROR) function

Your formula doesn't need the ISERROR to work.

=IF('Auto Entry'!C332<>0,'Auto Entry'!C332,'Manual Entry'!B8)

Good luck

Collapse -
Thanks guys! Lifesaver...

In reply to: Help with Excel / =IF(ISERROR) function

You guys are awesome! I've got everything working perfectly with your help.

Ryan

Collapse -
Wait, still having problems...

In reply to: Help with Excel / =IF(ISERROR) function

Alright, I am still having problems. I'm still having a problem with #REF!. The formula is in A16. I tried it both ways you guys suggested, and it worked OK both ways. That is, if C332 had data A16 displays the contents of C332. If I manually deleted the data in C332, A16 displays the data in B16. BUT, if I import a txt file that did not have data for C332, and C332 was blank because of this, Excel displays a #REF! and replaces the C332 refernce in my formula with #REF!. So I thought it worked until I did it with an actual import... *bashes head against desk* What can I do?

The two formulas:

=IF(ISERROR('Auto Entry'!C332),'ManualEntry'!B16,'Auto Entry'!C332!)

=IF('Auto Entry'!C332<>0,'Auto Entry'!C332,'Manual Entry'!B16)

Thanks!
Ryan

Collapse -
Update

In reply to: Wait, still having problems...

I played around a little more. Now if C332 is blank after refresh, the data does revert to B16. However, Excel messes up my formula and replaces the C332 with #REF! in the formula itself. In other words, if I import the data, and C332 is blank, cell A16 displays B16 but the formula in A16 gets changed to:

=IF(ISERROR('Auto Entry'!#REF!),'ManualEntry'!B16,'Auto Entry'!#REF!)

Can I do anything about this? Any ideas? Everthing else seems ok other than this. If not, I guess I can live with it. I'll just be sure to save it under a new name each time it's populated so I'll keep my formulas intact...

ps - ignore the final ! in first formula in my prev post. Bad cut and paste job. Happy

Thanks.
Ryan

Collapse -
Explanation

In reply to: Update

You will get the #REF! if you deleted the cells that your formula were pointing to. You can clear the content of those cells but not delete the rows or columns.

To answer the other issue with cells having text instead of number, you will have to improve your formula with an extra condition.

Try to use ISTEXT or ISNUMBER in your formula.

Good luck

Collapse -
Thanks again...

In reply to: Explanation

I guess I'll just have to live with it, since that's the nature of my import files. Unless I can figure out a way to make my macro import instead of refresh, but still prompt for a file to import... Hmm...

Thanks!
Ryan

Collapse -
More information to enhance you Macro

In reply to: Thanks again...

Ryan,

Here a portion of code that will help you modify your macro. You will need to add validation, etc.

' Allow the user to select Data file.
nFileName = Application.GetOpenFilename("CSV File (*.csv),*.csv ")
If nFileName = "False" Then
Exit Sub
End If

' Keep reference to actual worksheet
Let nWBook = ActiveWorkbook.Name

' Open Data file.
Workbooks.Open Filename:=nFileName
nNewBook = ActiveWorkbook.Name

' Copy Data file to main file
Range("A1").CurrentRegion.Copy
Windows(nWBook).Activate
Worksheets(nSheet).Range("A1").PasteSpecial Paste:=xlValues

' Close Data file.
Windows(nNewBook).Close False

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

SMART HOME

This one tip will help you sleep better tonight

A few seconds are all you need to get a better night's rest.