I am working with Excel 2007.

I have a workbook where one worksheet called My Lottery contains a 6 cell
width by 7 cell height, B6 to G12 table with each cell containing numbers.

On another worksheet I have a table B8 Space, D8 Space up to T8 width and B8
Space, B10 Space up to B16 height.

In Cell B8 I enter =IF('My Lottery'!$B$6:$G$6=1,1,0)
In Cell D8 I enter =IF('My Lottery'!$B$6:$G$6=2,2,0)
In Cell F8 I enter =IF('My Lottery'!$B$6:$G$6=3,3,0)

These all work fine returning the correct number or a zero as expected.

However when I move on to cell H8 and enter =IF('My
Lottery'!$B$6:$G$6=4,4,0) I get the "A value used in the formula is of the
wrong data type".

This carries on acrooss the line of cells to T8

On the next line of cells, B10, D10, F10, etc, the same thing happens with
11, 12 & 13 working fine, 14 to 20 returning the error value.

And so on down the cells, the first 3 working, the next 7 not.

What am I doing, what is going, wrong, help please!
--
Mac Macdonald

Mac,

Try using

=IF(COUNTIF('My Lottery'!$B$6:$G$6,1)<0,1,0)

But we could probably come up with a better solution if you described what you want, rather than
what you tried that didn't work.

HTH,
Bernie
MS Excel MVP


"Mac0001UK" wrote in message
...
I am working with Excel 2007.

I have a workbook where one worksheet called My Lottery contains a 6 cell
width by 7 cell height, B6 to G12 table with each cell containing numbers.

On another worksheet I have a table B8 Space, D8 Space up to T8 width and B8
Space, B10 Space up to B16 height.

In Cell B8 I enter =IF('My Lottery'!$B$6:$G$6=1,1,0)
In Cell D8 I enter =IF('My Lottery'!$B$6:$G$6=2,2,0)
In Cell F8 I enter =IF('My Lottery'!$B$6:$G$6=3,3,0)

These all work fine returning the correct number or a zero as expected.

However when I move on to cell H8 and enter =IF('My
Lottery'!$B$6:$G$6=4,4,0) I get the "A value used in the formula is of the
wrong data type".

This carries on acrooss the line of cells to T8

On the next line of cells, B10, D10, F10, etc, the same thing happens with
11, 12 & 13 working fine, 14 to 20 returning the error value.

And so on down the cells, the first 3 working, the next 7 not.

What am I doing, what is going, wrong, help please!
--
Mac Macdonald

Cheers for that Bernie, using it and some conditional formatting I get
exactly what I am looking for.
--
Mac Macdonald


"Bernie Deitrick" wrote:

Mac,

Try using

=IF(COUNTIF('My Lottery'!$B$6:$G$6,1)<0,1,0)

But we could probably come up with a better solution if you described what you want, rather than
what you tried that didn't work.

HTH,
Bernie
MS Excel MVP


"Mac0001UK" wrote in message
...
I am working with Excel 2007.

I have a workbook where one worksheet called My Lottery contains a 6 cell
width by 7 cell height, B6 to G12 table with each cell containing numbers.

On another worksheet I have a table B8 Space, D8 Space up to T8 width and B8
Space, B10 Space up to B16 height.

In Cell B8 I enter =IF('My Lottery'!$B$6:$G$6=1,1,0)
In Cell D8 I enter =IF('My Lottery'!$B$6:$G$6=2,2,0)
In Cell F8 I enter =IF('My Lottery'!$B$6:$G$6=3,3,0)

These all work fine returning the correct number or a zero as expected.

However when I move on to cell H8 and enter =IF('My
Lottery'!$B$6:$G$6=4,4,0) I get the "A value used in the formula is of the
wrong data type".

This carries on acrooss the line of cells to T8

On the next line of cells, B10, D10, F10, etc, the same thing happens with
11, 12 & 13 working fine, 14 to 20 returning the error value.

And so on down the cells, the first 3 working, the next 7 not.

What am I doing, what is going, wrong, help please!
--
Mac Macdonald