Zero problem with blank cell after automated data transfer

S

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.
Steve.

S

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?

B

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

S

Struggling in Sheffield

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