Yet Another 'INDIRECT' Question
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
"Bob Phillips" wrote:
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" wrote in message
...
=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
|