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

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" wrote
in message ...

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

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

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" wrote
in message ...

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

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 Im
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

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

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" wrote
in message ...

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