Help with military problem...

[jgn2112]

Pretty good excel user, but frustrated. Here is my situation...and I
apologize in advance for the "silliness" of it all, but I cannot use my
real example...here goes:
I have 8 worksheets each with a different kind of vehicle (e.g. Ford,
Chevy, Dodge, Honda, Toyota, BMW, Mercedes, VW). On each of the
worksheets, I have two adjacent columns: "Errand" for what kind of
errand the car was used for (with drop-down lists in each cell in the
column for the user to select one of the following: groceries,
transportation, cargo, other), and "Support" for who the car was
supporting when it did the aforementioned errand (with a drop-down list
in each cell in the column for the group of people supported: parents,
friends, collegues, teachers).
Now then, I have a "reference" worksheet at the back of the workbook
with two different areas to tabulate 1. how many times each specific
car does a particular errand, and 2. how many times a specific car
supports a particular group. What I cannot figure out how to do
(keeping in mind I have 8 worksheets from which to gather data) is how
to create another area on the "reference" sheet to calculate how many
times each errand is run for each particular group of people. For
example, I need to see how many times I've had to do "grocery" runs for
"collegues" or how many "cargo" runs I've had to do for "parents" (and
so on...) between all 8 cars.
I've been racking my brains to the point where I can no longer think
straight and I've read all the help files Excel has to offer but to no
avail.
Can anyone help?
Thank you very much in advance!
- John

 

[CLR]

Personally, I would combine all 8 sheets into one database............if I
ever wanted the info formerly on just one sheet it would be easy to capture
with Data > AutoFilter, as would most of the other things you wish to
isolate........I would freeze about 8 lines or so at the top and just the
SUBTOTAL formulas to calculate with..........

hth
Vaya con Dios,
Chuck, CABGx3

 

[jgn2112]

The problem with combining sheets is this has to be a user friendl
spreadsheet as it will be passed to people when I complete it to use.
And I've done so much programming on the 8 pages that it would be lik
practically starting over, as I've noticed formulas don't always cop
exactly as they were (the fields sometimes change). I know there ha
to be a way to figure out how many times someone has done a "grocery
run in support of "teachers" and a "transportation" run in support o
"friends" and so on. Here's a more graphical depiction of what I'
working on:
Template 1 (which I have figured out, no problem) -- Cars vs Errands:
.............Groceries | Cargo | Transportation | Other
Ford
Chevy
Dodge
Honda
Toyota
Mercedes
BMW
VW
TOTAL:

Template 2 (which I have figured out) -- Cars vs Support
..............Parents | Friends | Colleagues | Teachers
Ford
Chevy
Dodge
Honda
Toyota
Mercedes
BMW
VW
TOTAL:

Here's where I need help: Errands vs Support
.....................Parents | Friends | Colleagues | Teachers
Groceries
Cargo
Transportation
Other

How can I "combine" the two previous templates to give me info such a
"How many times did we do "Cargo" runs in support of "Teachers" and s
on.
Can anyone help?
Thanks!

 

[paul]

is the data captured on each sheet?Ie each time you select a grocery run for
a parent is a table filled or something?

 

[Toppers]

If you download and install the free add-in Morefunc.xll, you can use
the following formula... entered as an array formula with Ctrl-Shift-Enter


=SUM((THREED(Ford:Toyota!$A$2:$A$200)=$A2)*(THREED(Ford:Toyota!$B$2:$B$200)=B$1))

Copy formula across and down.

In the (example) summary table below "Errands" are in rows A2:A5 and
"Support" in B1: E1 so the formula above finds "Groceries" ($A2) for
"Patients" (B$1)

Patients Friends Colleagues Teachers
Groceries
Transportation
Cargo
Other


The add-in can be found in the following link...


http://xcell05.free.fr/english/index.html


Hope this helps!