|
Help with military problem...
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 |
Help with military problem...
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" wrote in message ... 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 |
Help with military problem...
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 |
Help with military problem...
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 |
| All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com