Replace N/A error with zero (0) without losing VLookup function

  • Thread starter Thread starter J.F.Conway
  • Start date Start date
J

J.F.Conway

I have a set of data relating to a list of data tags that is regularly
updated to pivot tables with new data from related workbooks. The updated
data is supplied from an internal database (Oracle DB) that exports to Excel.
However, sometimes there is no new data associated to some of the data tags
listed and therefore, the database does not export the names of those data
tags where no new data exists on that update.

The problem is that when I update the pivot table (which has the full list
of data tags), where there is no data that month I keep getting the #N/A
error, because Excel cannot find the name of the data tag from the exported
data. This is causing me to have to manually delete 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 the next time I update the
data, I need to re-enter that function into each cell with a 0 value.

Does anyone know how to add / alter a VLookup function to automatically
replace an #N/A error with a zero (0) value, without losing the VLookup
function? The function I use is:

=VLookup(cell refererence,'workbook filename.xls''worksheet',column,false).

Thanks.
 
Many thanks for your help, Jan Karel Pieterse. Problem fixed!!

Regards,

J.F.Conway
 
Back
Top