Poadrim
Posts: 140
Joined: Sat Oct 04, 2008 11:22 pm

Flight Log With Excel.

Fri Jan 18, 2013 1:48 am

Hi guys,

I have made my own "flight log" that I have been a passenger on. But I have a problem I can't get around.

I have 2 column that are named "ICAO" and "Airline", when I enter (let's say)
SAS in the "ICAO" column I want "Scandinavian Airlines" to appear in the "Airline" column.
Now, this is all good and well, but as I fly more then just SAS I want to add NAX (Norwegian Air Shuttle). This is here I get the problem.

=IF(E5:E500=SAS;”Scandinavian Airlines System”;"NOT BOOKED")
This works well, but I can't add another value for excel to look for.

I tried:
=IF(E5:E500=SAS;”Scandinavian Airlines System”;IF(E5:E500=NAX;"Norwegian Air Shuttle";"NOT BOOKED"))
and get: #NAME?.

Any ideas?

Thanks guys!!
Good judgment comes from experience. Good experience comes from someone else's bad judgment.
 
User avatar
Dreadnought
Posts: 9929
Joined: Tue Feb 19, 2008 6:31 pm

RE: Flight Log With Excel.

Fri Jan 18, 2013 3:53 am

Easy.

Create a sheet2. Here you will have a list of all the world's airlines. Column A has the code, Column B has the full name.

Back to Sheet1, where you keep the log...

In column A, you enter the airline code, like SAS. In column B, you input a lookup formula. Try =VLOOKUP($A1,Sheet2!$A:$B,2,FALSE)

Then it just looks up the value in A2 in the list on Sheet 2, moves to the second column in the range and pulls that value.
You have enemies? Good. That means you've stood up for something, sometime in your life. - W. Churchill
 
TLG
Posts: 364
Joined: Sun Jul 17, 2005 3:41 pm

RE: Flight Log With Excel.

Fri Jan 18, 2013 4:43 am

Your formulae aren't Excel; are you sure you're not using a different spreadsheet? I'm familiar with Excel only, so I can't identify what it is exactly.

I edited the nested formula a bit to fit the Excel syntax, and it works:

=IF(E5="SAS","Scandinavian Airlines System",IF(E5="NAX","Norwegian Air Shuttle","Not Booked"))

The range (E5:500) isn't necessary; Row 5 should be E5, Row 6 should be E6, and so on.

Maybe I'm totally missing what you're trying to do here. If that's the case, then my response will be invalid.  
 
Poadrim
Posts: 140
Joined: Sat Oct 04, 2008 11:22 pm

RE: Flight Log With Excel.

Sat Jan 19, 2013 10:58 pm

Hi, and thanks so far guys   Appreciate it!  

Well TLG, I am using MS Excel 2010. So I don't know why my formulas come back like this but your formula works, so thanks!

Quoting TLG (Reply 2):
Your formulae aren't Excel; are you sure you're not using a different spreadsheet? I'm familiar with Excel only, so I can't identify what it is exactly.

I edited the nested formula a bit to fit the Excel syntax, and it works:

=IF(E5="SAS","Scandinavian Airlines System",IF(E5="NAX","Norwegian Air Shuttle","Not Booked"))

The range (E5:500) isn't necessary; Row 5 should be E5, Row 6 should be E6, and so on.

Maybe I'm totally missing what you're trying to do here. If that's the case, then my response will be invalid.

WOW Dreadnought! That was some formula you wrote for me. Thanks! Just a minor thing, doesn't work thou  
As it seams you know this I'm just gonna PM you. And already now, thank you for your help!

Quoting Dreadnought (Reply 1):
Easy.

Create a sheet2. Here you will have a list of all the world's airlines. Column A has the code, Column B has the full name.

Back to Sheet1, where you keep the log...

In column A, you enter the airline code, like SAS. In column B, you input a lookup formula. Try =VLOOKUP($A1,Sheet2!$A:$B,2,FALSE)

Then it just looks up the value in A2 in the list on Sheet 2, moves to the second column in the range and pulls that value.
Good judgment comes from experience. Good experience comes from someone else's bad judgment.
 
planejamie
Posts: 564
Joined: Thu Sep 15, 2011 8:41 pm

RE: Flight Log With Excel.

Mon Jan 21, 2013 10:32 pm

Start by making a list of ICAO codes matched with airlines. Ideally have the ICAO codes in alphabetical order and put these in a little table type thing somewhere out of the way on the same sheet. You can hide the cells afterwards I believe. This will be used by the VLOOKUP function as a reference for basically, what to lookup when it is given the ICAO code.

If you press the little "fx" formuale/function symbol in Excel 2010 for the cell you put the full airline name into each time you have a new flight, a "Insert Function" popup will appear. Type "lookup" into the search box, hit "Go" and select "VLOOKUP" from the list. Click "OK"

Lookup Value - The cell you enter the ICAO code into each time (click the little button with a cell and red arrow on to select it)

Table Array - Again, click the little button on the right of the input box, choose the top left most cell of your ICAO/Airlines reference area and drag so it covers to the bottom right of it

Col_Index_Num will be the column in this reference area where the values are held. This is not the cell reference or column but a numerical value. So for this I would enter "2"

Ignore Range Lookup, hit "OK". Now you will have "N/A" appear in the cell, type the correct ICAO code into your ICAO cell and it should input the full name of the airline in the cell you put the VLOOKUP function into  
 
User avatar
Dreadnought
Posts: 9929
Joined: Tue Feb 19, 2008 6:31 pm

RE: Flight Log With Excel.

Mon Jan 21, 2013 10:55 pm

Quoting planejamie (Reply 4):
Ignore Range Lookup

Don't ignore it. If you do, it will default to "TRUE", and will give you approximate results. "FALSE" forces VLOOKUP to only return exact matches.
You have enemies? Good. That means you've stood up for something, sometime in your life. - W. Churchill
 
planejamie
Posts: 564
Joined: Thu Sep 15, 2011 8:41 pm

RE: Flight Log With Excel.

Tue Jan 22, 2013 1:17 pm

Quoting Dreadnought (Reply 5):
Don't ignore it. If you do, it will default to "TRUE", and will give you approximate results. "FALSE" forces VLOOKUP to only return exact matches.

Ah right I didn't know that, this has just explained a lot! haha

Who is online

Users browsing this forum: coolian2, salttee and 8 guests

Popular Searches On Airliners.net

Top Photos of Last:   24 Hours  •  48 Hours  •  7 Days  •  30 Days  •  180 Days  •  365 Days  •  All Time

Military Aircraft Every type from fighters to helicopters from air forces around the globe

Classic Airliners Props and jets from the good old days

Flight Decks Views from inside the cockpit

Aircraft Cabins Passenger cabin shots showing seat arrangements as well as cargo aircraft interior

Cargo Aircraft Pictures of great freighter aircraft

Government Aircraft Aircraft flying government officials

Helicopters Our large helicopter section. Both military and civil versions

Blimps / Airships Everything from the Goodyear blimp to the Zeppelin

Night Photos Beautiful shots taken while the sun is below the horizon

Accidents Accident, incident and crash related photos

Air to Air Photos taken by airborne photographers of airborne aircraft

Special Paint Schemes Aircraft painted in beautiful and original liveries

Airport Overviews Airport overviews from the air or ground

Tails and Winglets Tail and Winglet closeups with beautiful airline logos