Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yet Another 'INDIRECT' Question
=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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yet Another 'INDIRECT' Question
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem Solved -- Turn Off Drag/Drop
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" wrote: =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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem Solved -- Turn Off Drag/Drop
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 "Suzanne" wrote: 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" wrote: =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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT Question | Excel Discussion (Misc queries) | |||
INDIRECT Question I think | Excel Discussion (Misc queries) | |||
Question on INDIRECT | Excel Discussion (Misc queries) | |||
Indirect question | Excel Discussion (Misc queries) | |||
indirect.ext question | Excel Discussion (Misc queries) |