Solved

Excel ISERROR question

Posted on 2011-02-28
5
334 Views
Last Modified: 2012-05-11
Hi Experts:
I have this formula on cells in my spreadsheet:
=IF($A$1="ALL",Data!A4,IF(ISERROR(FIND($A$1,Data!D4)),"",Data!A4))

The spreadsheet contains a value in A1 which can vary.  The data as you can see comes from another spreadsheet.  If the A1 value does not match the Data spreadsheet D4 cell, I want a blank or null.  Now I am getting zeros when there is no match.

I hope this makes sense.

Thank you.
0
Comment
Question by:snix123
  • 2
  • 2
5 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 35000750
Try:

=IF($A$1<>Data!A4,"",IF(ISERROR(FIND($A$1,Data!D4)),"",Data!A4))
0
 

Author Comment

by:snix123
ID: 35006712
I still get a 'zero'.  Researching this further...thanks for your help, though.
0
 
LVL 1

Expert Comment

by:TonyWong
ID: 35241460
Hi there, not sure if this is resolved yet but you may want to check out your settings.

Depending on what version of Excel you have, go to File->Options->Advanced.
In the dialog box that pops up, go to 'Show zero in cells that have a zero value' and uncheck it.

Cheers,
Tony
0
 

Author Comment

by:snix123
ID: 35243747
Thank you for your input.  The problem was that the cells were never blank since they had a formula in them!  Have rescoped how things were happening and it seems o.k. now...can we close this one?
0
 
LVL 1

Accepted Solution

by:
TonyWong earned 500 total points
ID: 35260047
Still, if the formula in the cell is returning a '0' value (and it's supposed to be that as a result) then what I suggested should stop displaying the zeroes.

snix123, it's up to you if you want to close it as you're the OP :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now