#1
|
|||
|
|||
Formula to hide '0' from blank referenced cells
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 |
#2
|
|||
|
|||
{=if(isblank(RawData!B:B),"",RawData!B:B)}
|
#3
|
|||
|
|||
Thanks.
The formula worked for the first cell and pulled in the column header, but when I changed it to an array formula (using CTRL+SHIFT+ENTER) to reference the whole column, it crashed my whole system. This didn't happen for the other columns. Is there another option? |
#4
|
||||
|
||||
Perhaps post a sample sheet showing BEFORE and AFTER.
At first glance I don't understand the use of array formulas which are quite heavy on CPU usage and not always necessary
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
multiple conditions, and blank cells with the countifs function | jaden0605 | Excel | 1 | 03-30-2014 01:50 AM |
Join cells together separated by ; when some are blank | leemoreau | Excel | 3 | 11-03-2013 11:22 AM |
Selecting blank cells in criteria | apolloman | Excel | 6 | 08-24-2011 05:38 AM |
How to organize and insert blank cells between a huge number of data? | tareq | Excel | 12 | 09-29-2010 02:12 PM |
Using both drop down lists and blank cells for text | voltarei | Word | 0 | 03-18-2010 04:16 AM |