Use the Power of Excel & Word with Logbook Pro

rer8

New member
Joined
Jun 6, 2012
Messages
1
My previous computer logbook program was simple to link to my excel spreadsheet where I created resume documents, automated insurance forms, company tracking forms etc. Sadly it became unusable when I upgraded to windows 7 64 bit. I selected Logbook Pro because nine of the sixteen pilots in our department were using it and raved about its features. I was initially stunned that it was impossible to link Logbook Pro files to an Excel spread sheet or Word. I have spent the last week or so finding a way to solve my dilemma, and since there was limited information in the forum with respect to my questions, I have decided to share my solution and some of the excel formulas I have written to make the Exported Excel file produced by Logbook Pro work for me. I am using MS Office 2010. I am assuming that you are familiar with routine Excel processes as well as routine Word processes.

After exporting Logbook Pro I found that the output file was called Book1.xlsx. In order to retain the original name and still be able to manipulate the files (keep track of the files) I saved the working file as UpDated Book1.xlsx. I then named the first sheet “ORIG”, the second CLASS, and for discussion sake I named a third sheet Resume.

ORIG is a copy of the exported file that has been copied and pasted to the first sheet.
CLASS is where the magic of Excel is put to work. (see column names and formulas in the table below)
Resume is where I extract the specific data I want to include in a linked table to my resume in Word.

STEP 1.
I decided to leave the Logbook Pro spreadsheet view in its original state and manipulate the data to suit my personal needs. One of the first things that I wanted was to easily access all of my data in an excel environment The table below illustrates how I either copied the data directly from the “ORIG” sheet or extracted the specific data from the “ORIG” sheet “Aircraft Type” column as I did for the T-PROP, ASEL,AMEL,JET, and HELICOPTER COLUMNS, copying the specific data to the “CLASS” sheet into an appropriately named column.

STEP 2.
Now was where I had a lot of fun in building a table that I could copy and drop directly into a Word document. Here are two illustrations of the cell names/formulas that I used in my resume data compilation.

Pilot in command: =SUM(CLASS!S2:CLASS!S16000) (S2 was the starting cell of the Duration column. S16000 was an arbitrary number I chose to make sure all of the data was considered in the “F” column.

Total: =SUM(CLASS!F2:CLASS!F16000) : The same logic works here but is applied the PIC column.
Once you have your table constructed, it can easily be copied and droped into the word document of your choice.

Prepare your Word document as desired and when you are ready, copy the Excel “cell” or “group of cells” as desired. Paste the data into your word document and select “Link and Keep Source Format” or “Link and Use Source Format.”

STEP 3.
It’s now six months later and you have kept your logbook up to date, and the dream job is now within reach and it is time to update the resume. Use Logbook Pro to generate a fresh copy of Book1.xlsx. Copy the data, and drop in the ORIG sheet of your UpDated Book1.xlsx. Every thing will up date on the Class, and Resume sheets. Leave the spreadsheet (Excel ) open. Now open your Resume document in Word, right click on the table and manually update. Print or email you document as required.


Date
=ORIG!B2
Type
=ORIG!B2
Aircraft Ident
=IF(ISNUMBER(ORIG!C2),FIXED(ORIG!C2,0,TRUE), ORIG!C2)
Route
=ORIG!D2
T – PROP
=IF(OR(B2="BE1900C",B2="1900D",B2="BE200",B2="BE2000",B2="BE200T",B2="BE300",B2="BE350",B2="BE90",B2="BE99",B2="DO228",B2="SW3"),VALUE(E2),"")
Duration
=IF(VALUE(ORIG!F2)>0,VALUE(ORIG!F2),"")
ASEL
=IF(OR(B2="7eac",B2="aa5a",B2="AC114",B2="BE23",B2="BE33",B2="BE35",B2="BE36",B2="CE150",B2="CE152",B2="CE172",B2="CE172E",B2="CE172XP",B2="CE177",B2="CE182",B2="CE206",B2="CE208?",B2="CE210",B2="J3",B2="M20E",B2="PA28140",B2="PA28160",B2="PA28161",B2="PA28180",B2="PA28200",B2="PA28201",B2="PA38112"),VALUE(E2),"")
AMEL
=IF(OR(B2="BE1900C",B2="1900D",B2="BE200",B2="BE2000",B2="BE200T",B2="BE300",B2="BE350",B2="BE400",B2="BE400A",B2="BE55",B2="BE58",B2="BE76",B2="BE90",B2="BE99",B2="CE421",B2="DO228",B2="HS125",B2="LJ35",B2="PA30160",B2="PA31350",B2="PA32300",B2="PA32R",B2="PA34200",B2="RA390",B2="SW3"),VALUE(E2),"")
JET
=IF(OR(B2="BE400",B2="BE400A",B2="HS125",B2="LJ35",B2="RA390"),VALUE(E2),"")
Helicopter
=IF(OR(B2="BH204A",B2="BH206",B2="BH47",B2="CH47",B2="OH58",B2="TH55",B2="UH1H"),VALUE(E2),"")
Landings Day
=IF(ORIG!G2>0,ORIG!G2,"")


As an illustration of how the data can now be used, consider the sample ”resume” data below:
Total
17,973
Pilot in Command
16,966
ASEL
1,977
Turbo - Fan
1,744
AMEL
13,385
Turbo - Prop
10,883
Instrument
2,226
Helicopter
2,265
Instructor
2,249
 
Back
Top