One of the tasks that I have to do in my job is to enter data into an Excel spreadsheet. All the data comes from a defined list that rarely changes. The list consists of approximately 230 waypoints (geographic locations). Each waypoint has a name, a latitude, a longitude, and an abbreviation in seperate columns.
What I want to be able to do is to enter the abbreviation into a cell and then have the adjacent cells automatically fill in the additional information for me. This will reduce my work load and eliminate transcription errors in the latitude/longitude columns. It would also mean that any changes to waypoints can be made to the master list only without having to go through and find each instance of the waypoint and individually change them.
The master list is available in both Excel and Access.
I have limited knowledge of Excel (and less of Access) and have not yet been able to figure out how to do this.
The master list of waypoints is just waypoints. The spreadsheets that they get entered into are flightlog forms that work out time, distance, fuel used etc. These flightlogs only have some of the waypoints.
I have, in the past, just entered it all in manually but it would be nice to automate it.
One option I have thought of is to do as you suggest and set the abbreviation column as primary, then cut and paste the resulting information across to the spreadsheet. However, I have had access for about 3 days and don’t even know enough to be able to make sense of the help files, so I have been unsuccessfull getting data transferred from the main table to another one. Also, the flights often have a waypoint occuring twice (normally the departure and arrival airport) so it won’t allow me to assign primary to any field that has the duplicated data.
Hmm… in Excel, you use the “=LOOKUP” function to retrieve a value, using a search term, from the same row.
Take a look at this very simple spreadsheet to see what I mean.
Unforutnately, my knowledge of Excel is very limited and you’ll need to tinker with it to make it accept the previous waypoint’s longitude and latitude instead of the current waypoint’s (in order to calculate distance).
Or you could first use lookup to copy all the data from the master list into a series of hidden columns in the flightlog and then use that local copy to perform the distance and fuel calculations… might be easier.
Wish I knew more about Excel, sorry. Maybe somebody else has a better solution…
In the reference worksheet with the waypoints
Set up columns like this:
in column A list the abbreviations
in column B list the name
in column C list thge latitude
in column D list the londitude.
On the entry worksheet enter the abbreviation in column A
in column B enter =VLOOKUP($A1,Sheet1!$A:$D,2,false)
copy this into column C and column D all that will change is ,2, to point to column 3 and 4 of the lookup.
The formula just says look up the value in cell A1 of this sheet, in column A of sheet1 and bring back the value in the next column. The false is so that it returns an error if the abbreviation is not found.
Copy to the bottom of your input worksheet. Also set data/validation for the input column to pick from the list of abbreviations so that if you are entering the same thing all the time you can just pick from a drop down by typing at most one letter.
Reply, I only needed it to pick out the current waypoints details, the distance calculation is done on another part of the sheet, so you were on the right track.
The sheet had been done by someone else long before I joined the company but it had had a lot of errors in it (mainly due to users accidently deleting formulae). I’m just in the process of fixing things and adding bits and pieces for my own satisfaction.
Don’t Ask, your VLOOKUP formula worked perfectly. I initially thought it wasn’t going to because the input column is not, and can’t be, the left most of the range. After working my way through LOOKUP I eventually realised that only the reference sheet needs to have the abbreviation column on the left, the entry sheet can have them anywhere.
For my next trick I’m going to need to extract the lat and longs which are in this format:
| LAT | LONG |
|28 18.0 | 113 36.0|
|28 43.0 | 113 47.0|
and put them into a txt file in this format:
SEG=1
SIGPT=2818S11336E
SEG=2
SIGPT=2843S11347E
I will save that for another day and another thread though.