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