Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 -- bsnapool ------------------------------------------------------------------------ bsnapool's Profile: http://www.excelforum.com/member.php...o&userid=36115 View this thread: http://www.excelforum.com/showthread...hreadid=559923 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulae to look at 52 sheets &count if 2 cells have data entered
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulae to look at 52 sheets &count if 2 cells have data entered | Excel Discussion (Misc queries) | |||
formulae to look at 52 sheets &count if 2 cells have data entered | Excel Discussion (Misc queries) | |||
How to only print cells where there is data entered, as in a sched | Excel Discussion (Misc queries) | |||
Comparing data in cells on 2 sheets | Excel Discussion (Misc queries) | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |