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

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

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"
wrote in message ...
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 cant 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 cant 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.

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

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

"Bernie Deitrick" wrote:

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"
wrote in message ...
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 cant 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 cant 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.

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

.