Calc: select and use calculated values in calculation

[bbramwell]

I am trying to set up a spreadsheet to allow someone to calculate the length of a race course based on a list of waymarks. At present I have a fixed table of the marks like this:

Name | Lat. Decimal | Long. Decimal | Lat Dº M’ S” | Long Dº M’ S”
MA57 | 44.50844 | -64.21353 | N 44 30 30.4 | W 64 12 48.7
MC59 | 44.50233 | -64.26436 | N 44 30 08.4 | W 64 15 51.7

… where Lat. Decimal and Long. Decimal are calculated (formulated?) from the Lat. and Long. DMS columns.

Further down the sheet I have a mostly blank section which is intended to be filled in by the “user”:

Name | Lat Decimal | Long Decimal | Distance (NM) | Total (NM)|
MC59 | 44.502333 | -64.2643611 | |
MA57 | 44.508444 | -64.2135278 | 2.207387 | 2.207387
M55 | 44.410111 | -64.2110277 | 5.904950 | 8.112338
| | | 0 | 0

… where the distance and cumulative distance is calculated from the given Lat/Long co-ordinates.

I’d like an easy (for the user) way to copy or reference the first 3 columns from the fixed table to the distance calculation section, OR I’d like suggestions as to how else I could make this work.

At present I select the 3 columns of the row I’m interested in, and then “paste special” the values into the calculation section. This is really very clumsy.

Anybody? Your help would be greatly appreciated!

Bob B.

[Villeroy]

ask137251.ods (19.8 KB)
Select a waymark by entering its name. I added a numeric sort field, assuming that the order of waymarks matters.

[bbramwell]

Thank you very much for your reply! VLOOKUP, once I understood what it does, is amazingly useful. I think I’ve got a workable solution now.

• Bob B.

[karolus]

Hallo

use ⇒ Rightclick ⇒ Data_Validation ⇒ Allow ⇒ Cellrange … the obvious to create »drop-down-fields« for input the Names.

and VLOOKUP to grep Latidude and Longitude from the reference

[bbramwell]

Interesting… I’ll give that a try. Thanks!
… and that seems to do the trick - thanks again