What Formula to use when looking for text over multiple worksheets

[SNA400]

I have a Workbook with worksheets labeled for each working day which has
entries for different locations and quantities deliveries and I need to
calculate how many failed deliveries there were and how many lates - layout
of the sheet would help

drop1 drop3 drop4 drop5 Failed Late
FAIL/LATE f l l 1 2
type 1 25 6 10 20 25 30
type 2 92 81 60 40 92 100
type 3 163 465 15 25 163 40

This sheet is completed every day, then at the end of the week I need a
tally of all failed and all late drops and the quantities. I have tried
using LOOKUP, IF, FREQUENCY, COUNTA, COUNTIF and still cannot get the thing
to work.

Does anyone -
A - Understand what I am going on about, because I think i've confused myself!
B - Have some idea on how to get the result I need

Any help would be apreciated!

 

[Ashish Mathur]

Hi,

I would recommend consolidating all the data in one worksheet. Once that it
accomplished by using formulas/pivot tables. You may refer to the following
link for dynamically consolidating data from different
worksheets/workbooks -
http://datapigtechnologies.com/blog/index.php/using-access-to-combine-multiple-excel-files-method-2/.

Also, it would be a good practise to keep all the data in one worksheet
instead of splitting it across worksheet/worksbooks. If your data is fairly
large, then Access is the program to use

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com