Help with military problem...

**jgn2112** · 07-07-2006, 08:17 PM

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 · 07-07-2006, 08:35 PM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> jgn2112
> ------------------------------------------------------------------------
> jgn2112's Profile:
http://www.excelforum.com/member.php...o&userid=36175
> View this thread: http://www.excelforum.com/showthread...hreadid=559542
>

**jgn2112** · 07-07-2006, 11:42 PM

The problem with combining sheets is this has to be a user friendly 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 like practically starting over, as I've noticed formulas don't always copy exactly as they were (the fields sometimes change). I know there has 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 of "friends" and so on. Here's a more graphical depiction of what I'm 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 as "How many times did we do "Cargo" runs in support of "Teachers" and so on.
Can anyone help?
Thanks!!

paul · 07-08-2006, 03:10 AM

is the data captured on each sheet?Ie each time you select a grocery run for
a parent is a table filled or something?
--
paul
[email protected]
remove nospam for email addy!

"jgn2112" wrote:

>
> The problem with combining sheets is this has to be a user friendly
> 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 like
> practically starting over, as I've noticed formulas don't always copy
> exactly as they were (the fields sometimes change). I know there has
> 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 of
> "friends" and so on. Here's a more graphical depiction of what I'm
> 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 as
> "How many times did we do "Cargo" runs in support of "Teachers" and so
> on.
> Can anyone help?
> Thanks!!
>
>
> --
> jgn2112
> ------------------------------------------------------------------------
> jgn2112's Profile: http://www.excelforum.com/member.php...o&userid=36175
> View this thread: http://www.excelforum.com/showthread...hreadid=559542
>
>

Toppers · 07-08-2006, 02:00 PM

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!

"jgn2112" wrote:

>
> The problem with combining sheets is this has to be a user friendly
> 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 like
> practically starting over, as I've noticed formulas don't always copy
> exactly as they were (the fields sometimes change). I know there has
> 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 of
> "friends" and so on. Here's a more graphical depiction of what I'm
> 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 as
> "How many times did we do "Cargo" runs in support of "Teachers" and so
> on.
> Can anyone help?
> Thanks!!
>
>
> --
> jgn2112
> ------------------------------------------------------------------------
> jgn2112's Profile: http://www.excelforum.com/member.php...o&userid=36175
> View this thread: http://www.excelforum.com/showthread...hreadid=559542
>
>