Sponsor Message:
Non Aviation Forum
My Starred Topics | Profile | New Topic | Forum Index | Help | Search 
Flight Log With Excel.  
User currently offlinepoadrim From Norway, joined Oct 2008, 173 posts, RR: 0
Posted (1 year 8 months 2 weeks 2 days 7 hours ago) and read 1811 times:

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.
6 replies: All unread, jump to last
 
User currently offlineDreadnought From United States of America, joined Feb 2008, 8847 posts, RR: 24
Reply 1, posted (1 year 8 months 2 weeks 2 days 4 hours ago) and read 1774 times:

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.



Veni Vidi Castratavi Illegitimos
User currently onlineTLG From United States of America, joined Jul 2005, 375 posts, RR: 0
Reply 2, posted (1 year 8 months 2 weeks 2 days 4 hours ago) and read 1760 times:

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.  


User currently offlinepoadrim From Norway, joined Oct 2008, 173 posts, RR: 0
Reply 3, posted (1 year 8 months 2 weeks 9 hours ago) and read 1644 times:

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.
User currently offlineplanejamie From United Kingdom, joined Sep 2011, 576 posts, RR: 0
Reply 4, posted (1 year 8 months 1 week 5 days 10 hours ago) and read 1564 times:

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 currently offlineDreadnought From United States of America, joined Feb 2008, 8847 posts, RR: 24
Reply 5, posted (1 year 8 months 1 week 5 days 9 hours ago) and read 1549 times:

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.



Veni Vidi Castratavi Illegitimos
User currently offlineplanejamie From United Kingdom, joined Sep 2011, 576 posts, RR: 0
Reply 6, posted (1 year 8 months 1 week 4 days 19 hours ago) and read 1516 times:

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


Top Of Page
Forum Index

This topic is archived and can not be replied to any more.

Printer friendly format

Similar topics:More similar topics...
Help With Excel posted Fri Sep 7 2001 21:49:37 by SK A340
Airliners.net Popular With FRA Flight Planners? posted Thu Oct 11 2012 20:45:34 by TJCAB
Excel Boffins: Help With Formulae? posted Thu Apr 15 2010 11:49:26 by ajd1992
Post Your Flight Progress Live With MySkyStatus posted Sat Oct 17 2009 07:51:25 by ManuCH
Ever Spent Time With A Crewmember After A Flight? posted Wed Mar 7 2007 17:58:56 by AF773
Is It Easy To Flirt With FAs During A Flight? posted Fri Apr 21 2006 14:55:14 by CY319
Help With Flight Explorer posted Wed Jul 27 2005 22:54:14 by SWA TPA
Ever Boarded A Flight With A Pint In Your Hand? posted Wed Mar 16 2005 23:30:39 by EZYAirbus
Log In With Your Favorite Soda posted Wed Sep 10 2003 04:30:56 by Sleekjet
OK, Who Is With Me (Flight Level)? posted Fri Jul 12 2002 23:00:57 by EGGD