...
...
I tried using your formula to get a count & sum for 31 sheets on the final
sheet, but it is not giving me a count and only a value of zeroes, but no
errors.
If you don't get errors but only zeros, then the odds are that there are no
actual matches. That could be due to stray trailing ASCII space or nonbreaking
space characters in your cell entries. Pick a worksheet that appears to contain
a match, and enter some diagnostic COUNTIF formulas, e.g.,
=COUNTIF(X9:X69,"SomeTextHere")
where X is just a placeholder for one of your actual columns and "SomeTextHere"
is another placeholder for text which seems to appear in the given range. If you
get zero results, you likely have stray trailing spaces. If so, clean your data
using Edit > Replace.
1) Now, is it because i have given data validation on every cell in AGENTS
column and ERROR column to choose agents and the corresponding error they
had for the day, on all 31 sheets. and is it because i started entering data
from the 20th sheet onwards till date i.e. 26th, so it is not calculating
and giving the results (that is, count of Errors and sum of Time lost)
because of the starting blank sheets(and ending ones also)?
If you mean you're using data validation lists for AGENTS and ERROR columns, and
of the source lists contain stray trailing spaces, then that would be a possible
cause of your problems. ALWAYS ensure you eliminate trailing spaces. They seem
to be the single most frequent cause of text comparison errors in spreadsheets.
[They're a PITA in other software, but spreadsheet users and developers
generally have little experience handling arbitrary text, so this bites them in
the butts more often.]
However, if your source lists are clean or you're using a different form of
validation, then validation has no effect on other formulas. What's in the cells
is in the cells, and that's what other formulas use. Also, blank cells in
worksheets 01 to 19 and 27 to 31 shouldn't matter. Indeed, if you're getting
zeros rather than errors as results, at least the blank worksheets are being
counted correctly.
2) Also, since i have a cell e.g "A5" on every sheet (its actually a merged
cell that is, merged from A5 to C5) on top which gives me the most occuring
error for the day,
Merged cells could cause problems, but if your formulas access ranges with
topmost rows below row 5, this shouldn't cause any problems.
i use an Array formula for the error range C9:C69:
{=INDEX(C9:C69,MATCH(MAX(COUNTIF(C9:C69,C9:C69)),COUNTIF(C9:C69,C9:C69),0))}
to get the most occuring error for that day.
You could use a shorter, more efficient, non-array formula to do this.
=INDEX(C9:C69,MODE(MATCH(C9:C69,C9:C69,0)))
I believe Leo Heuser came up with this a few months ago (possibly earleir, but
that was the first time I'd seen it).
Please note, since 1st 19 sheets are empty, so the cell shows 0. after 20th,
the cell shows the error. now i made a named range, using your idea that is,
named,
WSE = "'"&WSLST&"'!B2"
Up above you mention A5, which is merged with B5 and C5, which you seem to say
contains the most frequent error on the particular worksheet, but the defined
name above uses B2. Which is it, A5 or B2? If it's A5, then your defined name
needs to refer to A5 rather than B2.
and used that in the final sheet to calculate the final error which occured
over the month that is, which occured maximum in the 31 sheets. BUT IT SHOWS
0. but when i entered some data in the 01st sheet, (that is, selected some
agents and some errors from the drop-down lists), i got the 1st error for
the day reflecting in the final sheet.
i used the array formula:
{=INDEX(INDIRECT(WSE),MATCH(MAX(COUNTIF(INDIRECT(WSE),INDIRECT(WSE))),
COUNTIF(INDIRECT(WSE),INDIRECT(WSE)),0))}
...
Won't work. The result from INDIRECT(WSE) can't be passed as first argument to
INDEX. It's good you're experimenting, but you need to recognize when your
experiments fail, *AND* you need to pay attention to the results from previous
experiments that worked.
You need to wrap anticipated numeric results from this sort of indirect
reference inside N() and anticipated text results inside T(). THESE ENCLOSING
FUNCTION CALLS ARE **NOT** OPTIONAL! I mentioned these in my original response
in this thread, but I may not have emphasized their necessity sufficiently. Now
I have.
Then use the simpler formula I suggested above, which now must be entered as an
array formula. [I suspect the reason array entry is now needed is due to the
fact that the first argument to INDEX is now the result of an expression rather
than a simple range reference.]
=INDEX(T(INDIRECT(WSE)),MODE(MATCH(T(INDIRECT(WSE)),T(INDIRECT(WSE)),0)))
Lastly, i am not able to understand, why you have taken the named range:
Seq = ROW(INDIRECT("1:20"), can i take it as ROW(INDIRECT("1:69") or
ROW(INDIRECT("9:69"), as my column range starts from the 9th row.
Note that if you're using Seq as second or third argument to OFFSET, the results
must be *OFFSETS* rather than *INDICES*. So to give pseudoreferences to C9, C10
and C11, you muse use
OFFSET($A$1,{8;9;10},2)
rather than
OFFSET($A$1,{9;10;11},3)
which would return pseudoreferences to D10, D11 and D12. So if you want rows 9
to 69, define Seq as =ROW(INDIRECT("8:68")) or use Seq-1 if you define Seq as
=ROW(INDIRECT("9:69")).
for WSBase = "'"&WSLST&"'!A1" , do i have to refer to the initial cell A1?
...
No, but I find it reduces errors in generalized dynamic ranges always to anchor
them at cell A1. You could define WSBase as ="'"&WSLST&"'!C9" , but you'd then
need to define Seq as =ROW(INDIRECT("1:61")) and use Seq-1 rather than Seq in
OFFSET. If that works for you, fine. [You'll learn.]
69 to create 2D arrays (with the ranges from each worksheet stacked