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

Guys, how can I find which sheet has #N/A in my workbook?
compare-rows.xlsx
rws1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
rws1Author Commented:
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
rws1Author Commented:
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

tdlewisCommented:
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
Saqib Husain, SyedEngineerCommented:
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
rws1Author Commented:
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
Saqib Husain, SyedEngineerCommented:
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
rws1Author Commented:
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
Saqib Husain, SyedEngineerCommented:
Oops...file not uploaded
Copy-of-compare-rows.xlsx
0
tdlewisCommented:
Here is the sheet with the macro I wrote to find all instances of #N/A.
compare-rows.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rws1Author Commented:
Thank you, your illustrations are great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.