Home

Pilot Logbook

%3CLINGO-SUB%20id%3D%22lingo-sub-679060%22%20slang%3D%22en-US%22%3EPilot%20Logbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679060%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20started%20to%20construct%20a%20Pilot%20Logbook%20with%20around%2035%20years%20(14%2C000%20hours)%20of%20flight%20data%2C%20which%20I%20exported%20from%20a%20database%20into%20a%20basic%20XL%20file%20with%20the%20latest%20entry%20at%20the%20top.%26nbsp%3B%20I%20have%20written%20a%20formula%20to%20calculate%20hours%20from%20chocks%20off%20to%20chocks%20on%20(even%20through%20midnight%2C%20into%20the%20next%20day)%20with%20a%20column%20calculating%20each%20leg%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20column%20with%20leg%20time%20has%20been%20totalled%20at%20the%20top.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20How%20do%20I%20set%20the%20end%20of%20the%20worksheet%20just%20underneath%20the%20first%20entry%2C%20around%203%2C600%20rows%20down%3F%3C%2FP%3E%3CP%3E2)%20How%20do%20I%20keep%20a%20line%20free%20at%20the%20top%20of%20the%20spreadsheet%20after%20an%20row%20entry%20has%20been%20made%3F%3C%2FP%3E%3CP%3E(It%20is%20not%20as%20simple%20as%20'inserting'%20a%20row%20because%20formulas%20are%20hidden%20at%20the%20top%20to%20total%20the%20hours%20column)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-679060%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-680074%22%20slang%3D%22en-US%22%3ERe%3A%20Pilot%20Logbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680074%22%20slang%3D%22en-US%22%3EHi%20BenFawkes%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20would%20really%20help%20if%20you%20could%20post%20a%20sample%20of%20the%20workbook%20to%20explain%20what%20you%20are%20trying%20to%20achieve.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-680198%22%20slang%3D%22en-US%22%3ERe%3A%20Pilot%20Logbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680198%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356974%22%20target%3D%22_blank%22%3E%40BenFawkes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20sample%20of%20your%20workbook%20would%20help%20(not%20your%20entire%20life%20history).%3C%2FP%3E%3CP%3EMeanwhile%2C%20I%20have%20attached%20a%20file%20that%20'mocks%20up'%20a%20non-existent%20table%20type.%3C%2FP%3E%3CP%3EThe%20body%20is%20a%20table%20which%20provides%20structured%20references%20that%20synchronise%20with%20the%20actual%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20record%2C%20comprising%20a%20row%20of%20zeros%2C%20is%20hidden.%26nbsp%3B%20That%20ensures%20number%20formatting%20etc.%20is%20inherited%20by%20any%20inserted%20row.%26nbsp%3B%20The%20table%20header%20row%20is%20reduced%20in%20size%20to%20a%20trim%20line%20under%20the%20totals%20row.%26nbsp%3B%20That%20allows%20the%20user%20to%20select%20a%20column%20by%20hovering%20the%20mouse%20over%20the%20line%20until%20a%20down%20arrow%20appears.%26nbsp%3B%20What%20appears%20to%20be%20a%20header%20row%20is%20no%20more%20than%20a%20copy%20of%20the%20hidden%20row.%26nbsp%3B%20The%20totals%20row%20is%20not%20the%20inbuilt%20table%20feature%20because%20that%20can%20only%20appear%20at%20the%20foot%20of%20the%20table.%26nbsp%3B%20Instead%20it%20contains%20a%20formula%20that%20references%20the%20appropriate%20column%20using%20structured%20referencing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20you%20will%20find%20some%20useful%20ideas%20here.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20411px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117618iC6ADC513FCD77B88%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
BenFawkes
Occasional Visitor

I have started to construct a Pilot Logbook with around 35 years (14,000 hours) of flight data, which I exported from a database into a basic XL file with the latest entry at the top.  I have written a formula to calculate hours from chocks off to chocks on (even through midnight, into the next day) with a column calculating each leg time.

 

The column with leg time has been totalled at the top.

 

1) How do I set the end of the worksheet just underneath the first entry, around 3,600 rows down?

2) How do I keep a line free at the top of the spreadsheet after an row entry has been made?

(It is not as simple as 'inserting' a row because formulas are hidden at the top to total the hours column)

 

Thanks....

2 Replies
Hi BenFawkes,

It would really help if you could post a sample of the workbook to explain what you are trying to achieve.

Thanks

@BenFawkes 

A sample of your workbook would help (not your entire life history).

Meanwhile, I have attached a file that 'mocks up' a non-existent table type.

The body is a table which provides structured references that synchronise with the actual data.

 

The first record, comprising a row of zeros, is hidden.  That ensures number formatting etc. is inherited by any inserted row.  The table header row is reduced in size to a trim line under the totals row.  That allows the user to select a column by hovering the mouse over the line until a down arrow appears.  What appears to be a header row is no more than a copy of the hidden row.  The totals row is not the inbuilt table feature because that can only appear at the foot of the table.  Instead it contains a formula that references the appropriate column using structured referencing.

 

Perhaps you will find some useful ideas here.

Capture.JPG

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies