formulae to look at 52 sheets &count if 2 cells have data entered

Hi All

Been a busy boy today, on excel. Im wondering if any one could assist me in this formuleas I am struggling with?

Ok here it goes...

I would like the formulae to look at 52 sheets, specifically 2 cells within the sheet, if they have dates entered into them, return a 1.

E.g.

Sheet 1

Cells N12&012 have dates in them = 1

sheet 2

Cells N12 has a date in it but o12 has not return = 0

etc etc

Hope this makes sense

Your help would be much appreciated.

Thanks

Andrew

Excel IS formulas don't include an ISDate() function, but since all numbers
can be treated as dates, given that the cell is formatted as a date, then the
ISNUMBER() function can be used. Yes, even negative numbers are treated as a
date when the cell is formatted to show numbers a dates - it just displays as
"########....###"

a formula to just check if both are numeric (could be dates) would be:
=IF(AND(ISNUMBER('Sheet1'!$N$12),ISNUMBER('Sheet1'!$O$12)),1,0)

a slight improvement is made by creating a user defined function using VB's
IsDate() function:

Function IsADate(CellValue) As Boolean
IsADate = IsDate(CellValue)
End Function

then your formula can become:
=IF(AND(IsADate('Sheet1'!$N$12),IsADate('Sheet1'!$O$12)),1,0)

Catch here is that if the cells N12 and O12 are formatted as dates, then any
number entered into them will be evaluated as a date, and perhaps that's what
you want.

Copy either formula through 52 rows, one for each sheet and change name of
each sheet in each formula. Labor Intensive.

You could use code to create the formulas. Assume you want them to begin at
cell A1 on Sheet53

Sub MakeFormulas()
Dim wksAnySheet as Worksheet

Worksheets("Sheet53").Select
Range("A1").Select
For Each wksAnySheet in Worksheets
If wksAnySheet.Name <> ActiveSheet.Name
ActiveCell.Formula = "=IF(AND(IsADate('" & _
wksAnySheet.Name & _
"'!$N$12),IsADate('" & _
wksAnySheet.Name & _
"'!$O$12)),1,0)"

ActiveCell.Offset(1,0).Activate
End If
Next
End Sub



"bsnapool" wrote:

>
> Hi All
>
> Been a busy boy today, on excel. Im wondering if any one could assist
> me in this formuleas I am struggling with?
>
> Ok here it goes...
>
> I would like the formulae to look at 52 sheets, specifically 2 cells
> within the sheet, if they have dates entered into them, return a 1.
>
> E.g.
>
> Sheet 1
>
> Cells N12&012 have dates in them = 1
>
> sheet 2
>
> Cells N12 has a date in it but o12 has not return = 0
>
> etc etc
>
> Hope this makes sense
>
> Your help would be much appreciated.
>
> Thanks
>
> Andrew
>
>
> --
> bsnapool
> ------------------------------------------------------------------------
> bsnapool's Profile: http://www.excelforum.com/member.php...o&userid=36115
> View this thread: http://www.excelforum.com/showthread...hreadid=559923
>
>