Zero problem with blank cell after automated data transfer

[Struggling in Sheffield]

Hi,
(Excel 2003)
I have a workbook where sheet 1 is called 'Raw data'.
Sheets 2 to 13 are then called 'January' through to 'December'.
I use the following formulas to auto-transfer rows of data from sheet 1 'Raw
data' into sheets 2-13 'January' - 'December' depending on a date cell within
‘Raw data’:

In sheet 1 ‘Raw data’:
=IF($B3="","",IF(TEXT($B3,"mmmm")=EK$2,ROW(),""))

In sheets 2-13 ‘January’ to ‘December’:
=IF(ROWS($2:2)>COUNT(OFFSET('Raw data'!$EJ:$EJ,,MATCH(WSN,'Raw
data'!$EK$2:$EV$2,0))),"",INDEX('Raw data'!E:E,SMALL(OFFSET('Raw
data'!$EJ:$EJ,,MATCH(WSN,'Raw data'!$EK$2:$EV$2,0)),ROWS($2:2))))

This all works fine except for one very perplexing problem.

Most of the data rows auto-transferred contain number cells, with some text
and a date cell. The text, date, number cells (formatted to 2 decimal places)
containing numbers, and some empty (blank) number cells all transfer OK (the
numbers are transferred as numbers (to 2 decimal places), and the blanks
transfer as just that - blank cells). Perfect.

All except for 4 cells on each row that contain whole numbers (with no
decimal places). Numbers within these cells transfer fine - but if these
cells are blank, Excel substitutes a ‘0’ (zero) value for the blank space
after the data transfer. This is terrible. I must have a blank space
transferred if the original cell value in ‘Raw data’ was a blank space. Some
of my calcs result in and display a zero, which is important. Excel chucking
in a zero on auto-transfer between sheets to replace a blank is the end of
the world and I must stop it.

For the life of me I can’t work out why some blank number cells transfer as
blanks, whilst Excel feels it must substitute a zero where other blank number
cells exist. Like I said before the only difference I can see is that the
number cells formatted to 2 decimal places all transfer fine, whilst those
formatted to 0 (zero) decimal places are giving me the problem.

I can’t hide the zeros as these are important for many of my calcs, nor can
I alter the number of decimal places for the whole number cells.

I’m close to spitting the dummy out with this one, Excel is just having a
laugh.
Please help.
Steve.

 

[Struggling in Sheffield]

Hi,
I follow your logic but despite trying several ways to work ISBLANK into my
original equation I still can't get rid of the zeros:

=IF(ROWS($2:2)>COUNT(OFFSET('Raw data'!$EJ:$EJ,,MATCH(WSN,'Raw
data'!$EK$2:$EV$2,0))),"",INDEX('Raw data'!E:E,SMALL(OFFSET('Raw
data'!$EJ:$EJ,,MATCH(WSN,'Raw data'!$EK$2:$EV$2,0)),ROWS($2:2))))

How can I incorporate ISBLANK into the equation above to make the zeros
become blanks again?

 

[Bernie Deitrick]

Steve,

Explicitly check for empty cells ("")

=IF(ROWS($2:2)>COUNT(OFFSET('Raw Data'!$J:$J,,MATCH(WSN,'Raw
Data'!$K$2:$V$2,0))),"",IF(INDEX('Raw Data'!E:E,SMALL(OFFSET('Raw
Data'!$J:$J,,MATCH(WSN,'Raw
Data'!$K$2:$V$2,0)),ROWS($2:2)))="","",INDEX('Raw
Data'!E:E,SMALL(OFFSET('Raw Data'!$J:$J,,MATCH(WSN,'Raw
Data'!$K$2:$V$2,0)),ROWS($2:2)))))

HTH,
Bernie
MS Excel MVP

 

[Struggling in Sheffield]

Thanks for that Bernie,
Couldn't initially get the 'answer' to work but your solution is spot on.
Cheers