98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » #N/A error
#N/A error
Resolved · High Priority · Version 2000
Jamie has attended:
No courses
#N/A error
#N/A error
I have a set of data relating to a list of district authorities that is regularly updated to pivot tables (monthly) with new data from related workbooks. The updated data is supplied from an internal database that exports to Excel. However, sometimes there is no new data associated to some of the authorities listed and therefore, the database does not export the names of those local authorities where no new data exists for that month.
The problem is that when I update the pivot table (which has the full list of local authorities), where there is no data that month I keep getting the #N/A error, because Excel cannot find the name of the local authority from the exported data. This is causing me to have to manually deleted the #N/A error in each cell and replace it with a 0 (zero). This has a knock-on effect of deleting the function from that cell, which means that next month, I need to re-enter that function into each cell.
Does anyone know how to add / alter a VLookup function to automatically replace an #N/A error with a zero (0) value? The current function I'm using for cells is: =VLookup (cell refererence, "workbook filename", column ref, false).
RE: #N/A error
Hi Jamie
You could try and combine the VLOOKUP function with an IF function.
THe formula below looks at a cell. If the cell is blank it puts a zero(0). If it has data it runs the VLOOKUP.
=IF(A4="",0,VLOOKUP(A4,Percentages!$A$3:$B$30,2,FALSE))
Hope this helps
Carlos
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Large Icons on toolbarYou can make the buttons on your toolbars bigger by going to Tools / Customize / Options / select Large icons. |