#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).

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

Thanks Carlos,

I have successfully rectified this error.

Thanks for your help.

Jamie