COUNTBLANK on multiple sheets

[rickblunt]

Greetings,I have a workbook that contains 62 worksheets that are used for daily inspections. Each worksheet is slightly different in the amount of cells used per day, but the same row is used on each sheet. Because there are a total of 620 cells used each day I would like to us a simple COUTNBLANK formula to confirm that every cell has it's respective value entered. This is pretty simple of course, but as I was writing the formula and typing in =COUNTBLANK(Sheet2!K12:K17+Sheet1!K13:K18+Sheet3!K11:K21+.....) I got to thinking, "Is there a better way of doing this instead of typing in all 62 sheets?". Thanks,RB

 

[RoryA]

You appear to be checking different ranges on each sheet, so I suspect the answer is no, unless there's a pattern to it. Personally, I would use a countblank formula in the same cell on each sheet and then just sum them.

 

[edvwvw]

Is it not possible to calculate he number of blanks in each sheet and put the result in a single cell say IV1 then you could use something like

=sum(Sheet1:Sheet62!IV1)

edvwvw

 

[ravishankar]

Hi
Insert a sheet called Master and paste the following codes in the macro window ( Alt F11)

Sub rickblunt()
Dim a As Long, b As Long
b = Sheets.Count
    For a = 1 To b
        If Worksheets(a).Name <> "master" Then
        Sheets("master").Cells(a + 1, 1) = Worksheets(a).Name
          Sheets("master").Cells(1, 1) = "=countblank('" & Worksheets(a).Name & "'!K11:K25)"
         Sheets("master").Cells(a + 1, 2) = Sheets("master").Cells(1, 1)
         End If
     Next a
     Cells(b + 2, 1) = "total"
     Cells(b + 2, 2) = "=sum(B2:B" & b + 1 & ")"
 MsgBox "complete"
End Sub

run the macro. It lists all sheet names in co A and no of blank cells between K11 and k25 in col B and total at the bottom
Ravi

 

[rickblunt]

Thanks for the responses all - I appreciate the input. I should add that I have this formula in a single cell which is conditionally formatted to show to turn green when the cells are all filled.

edvwvw - I believe that this formual would add all of the values in the cells, and these values are different from day to day so I would not be able to do a COUTNBLANK/COUNT formula

rorya - you are correct, that is basically the approach I took on another workbook that I was using a while back and it worked pretty well.

I will probably do the same thing again - I was just wondering if there was a way to write it all in one fell swoop. When I try something like COUNTBLANK(Sheet1:Sheet61!A34:A44) it doesn't work. RB

 

[RoryA]

Nope - countblank doesn't work with 3d references unfortunately. You could almost certainly come up with an array formula using INDIRECT but I'd keep it simple!

 

[rickblunt]

OK, thanks for the input - I appreciate the help. RB

 

[procod]

I just wrote an Add-In for this task. I have same problem sometime . Maybe it will be useful in your case too.

What you need to do is switch between sheets and select range you want to include to COUNTBLANK. Then use this addin to build a formula and copy/paste it where you want.
Here is download link with instructions:
http://vbacodesamples.blogspot.com/2010/01/excel-macro-multiple-range-formula.html

 

[JakeIsbill]

I am having a similar dilemma. My boss wants me to make a Sheet in an existing Attendance workbook that will add up all of the absences for a particular day. January 1st is in Cell B1 on all 65 sheets. If someone was absent that day we insert an appropriate letter (S-sick, V-vacation,etc.) is there an easy-ish way to add up each days absences on a Master sheet in that workbook, for each day? Please help me.