Aggregating data

[pilotdata]

Hello All:

I've already found a great deal of help reading the archives, but I'm now stuck with a structural issue. I am building a spreadsheet to track recurring training requirements for each of the pilots in my corporate flight operation. This is my first attempt at vba and I’m stuck in a few spots. I have one sheet for each pilot with a list of training requirements on each. The last training date is entered for each requirement and the sheet calculates the next due date for each of 15 training requirements based on different variables.

My largest problem is creating a report sheet that aggregates all of the data, with all of the pilots, training requirements, and due dates grouped by month and sorted by due date. Should I be feeding all of the data into one sheet & then using a pivot table? If so, how do I get the data all together when I don't know before hand how many sheets I'll have? Should I be using filters, a macro, or a pivot table to do what I want? Should I be structuring the entire project differently? Currently, there are 8 pilots but the solution needs to be flexible enough to accommodate additional pilots (hence additional sheets)and additional training requirements. Any help is greatly appreciated & I'll be happy to email my latest file for review (it doesnt look like i can attach it to this post).

Thanks!
Chris

[Anne Troy]

Hi, Chris. I've got a few hundred hours, tho not piloting.
If it were me, I'd be looking at getting all the records into one worksheet,
then working with it from there. Consider a macro that dumps the records out
to individual sheets: http://vbaexpress.com/kb/getarticle.php?kb_id=318
(which you could delete after printing). Consider pivot tables, yes, but I
hate them myself. Have you considered using Access which has much friendlier
built-in reporting capability.
************
Anne Troy
www.OfficeArticles.com

"pilotdata" <[email protected]> wrote
in message news:[email protected]...
>
> Hello All:
>
> I've already found a great deal of help reading the archives, but I'm
> now stuck with a structural issue. I am building a spreadsheet to
> track recurring training requirements for each of the pilots in my
> corporate flight operation. This is my first attempt at vba and I'm
> stuck in a few spots. I have one sheet for each pilot with a list of
> training requirements on each. The last training date is entered for
> each requirement and the sheet calculates the next due date for each of
> 15 training requirements based on different variables.
>
> My largest problem is creating a report sheet that aggregates all of
> the data, with all of the pilots, training requirements, and due dates
> grouped by month and sorted by due date. Should I be feeding all of
> the data into one sheet & then using a pivot table? If so, how do I
> get the data all together when I don't know before hand how many sheets
> I'll have? Should I be using filters, a macro, or a pivot table to do
> what I want? Should I be structuring the entire project differently?
> Currently, there are 8 pilots but the solution needs to be flexible
> enough to accommodate additional pilots (hence additional sheets)and
> additional training requirements. Any help is greatly appreciated &
> I'll be happy to email my latest file for review (it doesnt look like i
> can attach it to this post).
>
> Thanks!
> Chris
>
>
> --
> pilotdata
> ------------------------------------------------------------------------
> pilotdata's Profile:
> http://www.excelforum.com/member.php...o&userid=28664
> View this thread: http://www.excelforum.com/showthread...hreadid=483371
>

[pilotdata]

Hi Anne:

Thanks for the fast reply and for the ideas. The example that you supplied seems to go the other way, that is, to take data from one sheet and distribute it to others. I'm not sure how I could modify the code to do the opposite.

You're probably right about Access being the answer, but I've never used it and wouldn't even know where to begin. Perhaps its time for me to take an Access basics class so that I have some new tools to use

Thanks Again,
Chris

[Anne Troy]

Probably VERY easy to create a database for you, Chris. I'm willing to take
a look at your Excel file. Also, I meant that you'd start with all your data
on one worksheet. Use Autofilter and subtotals features:
http://www.officearticles.com/excel/...soft_excel.htm
http://www.officearticles.com/excel/...soft_excel.htm
But when/if you want individual reports for each pilot, you save the file,
run a macro like the one I gave you, print the entire workbook, close the
file without saving so you don't have those extra worksheets anymore.
Make sense? Sure, there's lots of better ways...they're just not as cheap as
this one. I do VBA project mgmt for a living now...no longer a P3
mechanic. LOL
************
Anne Troy
www.OfficeArticles.com

"pilotdata" <[email protected]> wrote
in message news:[email protected]...
>
> Hi Anne:
>
> Thanks for the fast reply and for the ideas. The example that you
> supplied seems to go the other way, that is, to take data from one
> sheet and distribute it to others. I'm not sure how I could modify the
> code to do the opposite.
>
> You're probably right about Access being the answer, but I've never
> used it and wouldn't even know where to begin. Perhaps its time for me
> to take an Access basics class so that I have some new tools to use
>
>
> Thanks Again,
> Chris
>
>
> --
> pilotdata
> ------------------------------------------------------------------------
> pilotdata's Profile:
> http://www.excelforum.com/member.php...o&userid=28664
> View this thread: http://www.excelforum.com/showthread...hreadid=483371
>

[Trinetra]

Hi pilotdata!
The easiest solution is that to create entries in single sheet. If you can
email me the structure of the data that you use and the variables, I can even
help you create a sheet myself. The reporting requirements can be easily met
by the Pivot table. Additionally when you want a different sheet for each of
the pilots, you can have two options:
1. individual sheets linked to the base sheet (in which you enter data).
(the file size will be bigger)
2. single sheet with a drop down list of all the pilots, it will give you
the data based on the selection. (the file size will be smaller)

--
Trinetra
The Good One


"pilotdata" wrote:

>
> Hello All:
>
> I've already found a great deal of help reading the archives, but I'm
> now stuck with a structural issue. I am building a spreadsheet to
> track recurring training requirements for each of the pilots in my
> corporate flight operation. This is my first attempt at vba and I’m
> stuck in a few spots. I have one sheet for each pilot with a list of
> training requirements on each. The last training date is entered for
> each requirement and the sheet calculates the next due date for each of
> 15 training requirements based on different variables.
>
> My largest problem is creating a report sheet that aggregates all of
> the data, with all of the pilots, training requirements, and due dates
> grouped by month and sorted by due date. Should I be feeding all of
> the data into one sheet & then using a pivot table? If so, how do I
> get the data all together when I don't know before hand how many sheets
> I'll have? Should I be using filters, a macro, or a pivot table to do
> what I want? Should I be structuring the entire project differently?
> Currently, there are 8 pilots but the solution needs to be flexible
> enough to accommodate additional pilots (hence additional sheets)and
> additional training requirements. Any help is greatly appreciated &
> I'll be happy to email my latest file for review (it doesnt look like i
> can attach it to this post).
>
> Thanks!
> Chris
>
>
> --
> pilotdata
> ------------------------------------------------------------------------
> pilotdata's Profile: http://www.excelforum.com/member.php...o&userid=28664
> View this thread: http://www.excelforum.com/showthread...hreadid=483371
>
>

[pilotdata]

Anne & Trinetra:

It sounds like both of you suggest getting all of the data onto one sheet. I'll have to think more about how to set this up......

In the mean time, would either of you mind taking a look at my existing file? What is the best way to get my file to you? This forum doesn't seem to have a provision for emailing either of you directly....

Anne: P3 mech, eh? Very cool. Where were you based? I'm a Gulfstream IV pilot (a C-20G to military types :-)

[Anne Troy]

Hi, Pilot. Rota, Spain. Also did a tour at VT-3 in FL. You can email the
workbook to me if you like. ng@ the website in my signature.
************
Anne Troy
www.OfficeArticles.com

"pilotdata" <[email protected]> wrote
in message news:[email protected]...
>
> Anne & Trinetra:
>
> It sounds like both of you suggest getting all of the data onto one
> sheet. I'll have to think more about how to set this up......
>
> In the mean time, would either of you mind taking a look at my existing
> file? What is the best way to get my file to you? This forum doesn't
> seem to have a provision for emailing either of you directly....
>
> Anne: P3 mech, eh? Very cool. Where were you based? I'm a
> Gulfstream IV pilot (a C-20G to military types :-)
>
>
> --
> pilotdata
> ------------------------------------------------------------------------
> pilotdata's Profile:
> http://www.excelforum.com/member.php...o&userid=28664
> View this thread: http://www.excelforum.com/showthread...hreadid=483371
>