Filtering ?

[Tarjei Lundarvollen]

Hi.

I want to make a pilot logbook in excel, and I have almost completed the
work, but I can't figure out how to do one function:

The first page of the logbook is a "summary" page where some of the most
used flight times is shown. The second page is a electronic version of my
paper logbook (and on this page there is summary for each page in the paper
logbook) and on the next pages I want to have specified aircraft time
logged.

The problem is:

I want to log the time according to my paper logbook on page 2 in the sheet.
And then I want some kind of "filtering / copying" of this time so that the
logged time also transfer to the specified aircraft. Lets say that I logged
a flight with a Boeing 737 on page 2. I have then typed B737 in the "type of
aircraft" field, and I want the filter to look at the "type of aircraft"
column, and it this field says "B737", then I want that row to also be
copied to another page in the sheet (and I have named that sheet B737). The
reason for this is because I want to have a electronic copy of my logbook on
page 2, but I also want the spreadsheet to summary flight time on all the
aircraft I'm flying (and also new aircraft types in the future), and this is
done on by copying the logged flight time to subsequent pages in the same
spreadsheet. The "filter" needs to be able to look for many aircraft types
in the same column (e.g. B737, A320, C172, TB10 ...) and copy each row
(line) to the correct subseqent page in the spreadsheet.

Any hints, help or examples is highly appreciated.

Tarjei Lundarvollen, norway

 

[arno]

Hi Tarjei,

I want to make a pilot logbook in excel, and I have almost completed
the work, but I can't figure out how to do one function:

So, all your data is stored in sheet2, all other sheets are "reports"
on that data? Then you could try to create a pivot table on your data
in sheet2. You could list your planes with the sum of flight time etc.
etc.

Under Extras/Pivot tables you can start an assistant to create such a
table step by step, have a try.

arno

 

[Tarjei Lundarvollen]

So, all your data is stored in sheet2, all other sheets are "reports"

on that data? Then you could try to create a pivot table on your data
in sheet2. You could list your planes with the sum of flight time etc.
etc.

Under Extras/Pivot tables you can start an assistant to create such a
table step by step, have a try.

That's correct. I will try that, thanx for your reply.

Tarjei

 

[arno]

Hi Tarjei,

That's correct. I will try that, thanx for your reply.

let me add, if you put all the available fields of your table (sheet2)
in the "page"-section of the pivot-table, then you have lots of
possibilities to filter your data.

arno

 

[Tarjei Lundarvollen]

I want to make a pilot logbook in excel, and I have almost completed

So, all your data is stored in sheet2, all other sheets are "reports"
on that data? Then you could try to create a pivot table on your data
in sheet2. You could list your planes with the sum of flight time etc.
etc.

Under Extras/Pivot tables you can start an assistant to create such a
table step by step, have a try.

Hmm, It might be me that did something "wrong", but I could not get it work.

A sample spredsheet which has the idea of what I want to do is on
http://home.online.no~tb10/slett/logbooktest.xls

The first page is just a summary page, the second page is the actual logbook
where I'm putting in the flight data, and the third page is the "summary"
page where all the lines that has "B737" as the "aircraft make / model /
variant" should be copied to (for this example just disregard all lines /
rows that don't have B737 as the aircraft type).

If anyone could have a look and tell me what you did to get it work like I
want it to do, that would be nice.

Tarjei Lundarvollen, norway

 

[DS NTE]

Your link does not work !
Linken din virker ikke !
Linken din verke itj!(som vi sei i Trøndelag)

 

[Tarjei Lundarvollen]

Your link does not work !

Linken din virker ikke !
Linken din verke itj!(som vi sei i Trøndelag)

Sorry, forgot a slash. The correct link is:
http://home.online.no/~tb10/slett/logbooktest.xls

I have found a "work around" now, but it's not optimal, the file is 7MB
before I have started to input data (and is not even finish with the setup).

This "workaround" is to have three pages in the spreadsheet. The first is
only a summary page. The second is the actual logbook (just like in the
"demo" I have linked to above), and the third is a "aircraft specified type"
sheet with a LOTS of IF in it. Every line looks for the aircraft type in the
logbook page, and if its a small single engine piston it is copied into the
"single engine" coloumn, if its a multi engine piston it is copied into th
coloumn "MEP" and so on.

It works, but the file size is getting HUGE

Tarjei Lundarvollen, norway

 

[Dominic]

Tarjei,

Had a look at your file. It seems like you want the display the exact same
info in your electronic "paper" logbook and your aircraft type sheets.

I would suggest using a single sheet, your loggbok sheet, and use an
advanced filter.

Using your sample file as an example.
In cell a26 type "variant", in a27 type B737.
Modify formula in H26 to read "=sumif(f7:f24,a27,h7:h24)"

Highlight your data (a6:w24) and choose Data:Advanced Filter.
Filter in place and choose for your criteria the range a26:a27

This will filter the "data" portion, rows 7:24 and will conditional sum your
totals based on the filter criteria. In this case B737.

You would obviously need to change your other formulas to a similar "sumif"
formula.

A little clunky, but should help.