Hi,
I have automatically populated a worksheet from another worksheet in the same workbook using array formulas e.g.
My duplicated worksheet is called Payroll Data and I have automatically populated it from some columns in another worksheet called Raw Data using an array formula {=(RawData!B:B)} across a number of columns

My problem is that the array formula was returning unwanted data in blank referenced cells e.g. where a referenced column had a date format, it was returning 01/01/1900 in blank cells and 0 in other columns that were text or numerical format. I have rectified this by switching off the "show zero" in the advanced options feature. However, there is one particular column (overtime) which may legitimately have 0 in the column and I need this column to return data for the referenced cells, even if it is zero.

Is there formula I can use for this column that will let me show the "wanted" zeros but not the "unwanted" ones(without switching the zero option back on again and having to custom format all the other columns as well)?



Thanks