Inspecting .xlsx for source of #N/A in cell how can I inspect the underlying dat
ID: 3565370 • Letter: I
Question
Inspecting .xlsx for source of #N/A in cell
how can I inspect the underlying data of the .xlsx cells' contents to identify what Microsoft is interpreting and converting to '#N/A'?
we receive excel spreadsheets from clients which we convert to CSV files and import into MS Access 2010 tables via VBA procedures
periodically we get what appears to us as '#N/A' in cells of the spreadsheet which halts and aborts the automated process with an error type: 13 - Type mismatch
trying to clarify with the button pushing Einstein's that sent us the file "What is in the cell?" is an exercise in teaching calculus to amoebas (we're not connecting!)
how can I inspect/identify the underlying data of the .xlsx cells' contents to identify WHAT Microsoft is interpreting/converting and displaying as '#N/A'?
I can likely code a trap to capture and 'fix' the bad data, but need some way of inspecting the underlying data causing what is being displayed - or perhaps be able to instruct the button-monkeys on how to prep the data properly for successful automated transmittal and processing.
Explanation / Answer
Hi,
A cell can contain #N/A for a couple of reasons such as a failed LOOKUP or even someone entering =NA() in a cell so let's take a particular example. This formula would return #N/A if it couldn't find the value in A1 in column I
=VLOOKUP(A1,I1:J22,2,FALSE)
There are 3 ways to fix this.
1. Insert the value from A1 into Col I but this of course means the data are no longer valid. i.e. we made it up.
2. Change the formula which is what should have been done in the first place to something like this which makes the error return a null string
=iferror(VLOOKUP(A1,I1:J22,2,FALSE),"")
3. Run code on the worksheet to delete all the errors.