Solved

How to find which sheet and cell has #N/A in a workbook

Posted on 2013-01-20
11
295 Views
Last Modified: 2013-01-21
Guys, how can I find which sheet has #N/A in my workbook?
compare-rows.xlsx
0
Comment
Question by:rws1
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 38799691
Hello,

you sheets are called Sheet2 and Sheet5. Assuming that you mean Sheet2 when you say Sheet1 and you mean Sheet5 when you say Sheet2, then put this formula in Sheet2, cell E6 and copy down

=INDEX(Sheet5!$D:$D,MATCH(C6,Sheet5!$D:$D,0))

cheers, teylyn
0
 

Author Comment

by:rws1
ID: 38799796
Thank you teylyn, for your response, however what I need is a pop-up or something to alert me where I have the #N/A in my workbook and direct me to the correct cell. I get a notice that I have a #N/A in my sheet totals, but then I have to research the whole workbook to try to find which cell is producing the problems so I can fix it.

Any thoughts in this direction would be greatly appreciated.

Once again thank you for your input.
0
 

Author Comment

by:rws1
ID: 38799802
My note on my attachment is wrong it shoud say sheet 2 and sheet 5, I don't know how to remove and replace the file.
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38799807
Delete row 5 from the worksheet you posted and then run try this code:
Sub CompareCols()
Dim v As String
    
    v = DoCompare(Range("Sheet2!C5").CurrentRegion, Range("Sheet5!D6").CurrentRegion)
    If v <> "" Then MsgBox "Missing value(s): " & v
End Sub

Function DoCompare(rng1 As Range, rng2 As Range) As String
Dim i1 As Long
Dim i2 As Long
    
    DoCompare = ""
    i2 = 1
    For i1 = 1 To rng1.Rows.Count
        If rng2.Cells(i1, 1).Text = "#N/A" Then
            DoCompare = DoCompare & IIf(DoCompare = "", "", ",") & rng1.Cells(i1, 1).Value
        End If
    Next i1
End Function

Open in new window

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38799817
This ARRAY formula will give you the row number of the first #NA cell in the list

=MIN(IF(ISNA(D:D),ROW(D:D)))
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:rws1
ID: 38799916
Thank you to both you guys, however I am having no success getting either of your suggestions to work, would you mind to put it in the spreadsheet and send it back?
Thank you,
Rob
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38801064
The formula I gave is in column E which gives only the first occurence of the the #NA

In column F I have given another formula which when copied down will show further occurences,
0
 

Author Comment

by:rws1
ID: 38801543
ssaqibh, thank you for your response, I only see the formula for column D, above
=MIN(IF(ISNA(D:D),ROW(D:D)))

I am confused as to your direction

Thank you for your help,
Robert
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 250 total points
ID: 38801611
Oops...file not uploaded
Copy-of-compare-rows.xlsx
0
 
LVL 10

Accepted Solution

by:
tdlewis earned 250 total points
ID: 38801796
Here is the sheet with the macro I wrote to find all instances of #N/A.
compare-rows.xlsm
0
 

Author Comment

by:rws1
ID: 38803519
Thank you, your illustrations are great.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

11 Experts available now in Live!

Get 1:1 Help Now