Yet Another 'INDIRECT' Question

[Suzanne]

=COUNT('Other'!$B$4)+SUMPRODUCT(--('Other'!$A$16:$A$21="OCT")*ISNUMBER('Other'!$B$16:$B$21))

'Other'!B4 is the October date of an Excellent rating
'Other'!A16:A21 *might* contain "OCT" -- if it does...
'Other'!B16:B21 contains the date(s) of additional October Excellent ratings

The formula above is on the compilation worksheet "OCT"

Problem: When users copy or drag dates on the "Other" worksheet to another
cell, I wind up with errors on the "OCT" worksheet.

I believe the Indirect formula is the way to go, but I can't get it to work
(the formula is accepted but doesn't give me the number of Excellent ratings)

Thanks -- Suzanne

 

[Bob Phillips]

Perhaps

=COUNT('Other'!$B$4)+SUMPRODUCT(--('Other'!$A$16:$A$21=Text('Other'!$B$4","MMM")),--(ISNUMBER('Other'!$B$16:$B$21)))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Suzanne]

Thanks -- the original formula works VERY well to capture inspection ratings
(throughout the year for about 20 different units) -- BTW... thanks to Excel
guru's for helping me with that one as well!

I need the formula to *not* look like the following if users move "1 Oct"
from 'Marginal' (Col D) to 'Excellent' (Col B):

"COUNT('Other'!#REF!)+SUMPRODUCT(--('Other'!A16:A21="OCT")*ISNUMBER('Other'!B16:B21))

and prevent the 'Marginal' cell (which is supposed to capture 'Other'!$D$4)
from changing to 'Other'$B$4)

Suz

 

[Suzanne]

I found my own workaround:

In "This Workbook"

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

Cut/paste does not affect the formula -- only drag/drop.

Suz

 

[Suzanne]

Oh... if anyone else uses this, suggest turning Drag/Drop back on:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.CellDragAndDrop = False Then Application.CellDragAndDrop
= True
End Sub