User input to get coordinate distance

**pitakoc** · 11-30-2012, 07:50 PM

I have data in the following format in a worksheet:
X Y name category

where x and y are integers for coordinates and name and category are are strings.

I'd like to have a separate sheet where a user will input an x and y coordinate, and select a category, and return the closest 10 points for the selected category. I have the distance formula but don't know the ins and outs of excel to know how to deal with user input. If someone could point me in the direction of features and functions or elaborate on how user input and return results work I think I can figure it out. Thank you for your time.

**djapigo** · 11-30-2012, 08:25 PM

re-post: ignore this... had a brain cramp...

**shg** · 11-30-2012, 08:25 PM

Insert a few rows above your table. Enter the coordinates of the point of interest, and in a column to the right of the table, calculate the distance of each point to the reference point. Sort the table by the distance column.

**Richard Buttrey** · 11-30-2012, 08:34 PM

Hi,

I think the general approach I'd take is as follows.

1. Name the two cells for the x & y co-ordinates, say xLoc, yLoc. (It's always good practice to name cells and then use the names in formulae).
2. Add a fifth helper column to your data. This will contain the distance between each data point and the reference point. This distance can be computed with the normal Pythagoras theorem. e.g for the 1st data point on say row 2
=SQRT((A2-xLoc)^2+(B2-yLoc)^2)
3. Now use Data Advanced Filter Copy to another location, to extract from your data the five columns using a criteria range for the category you want.
4. Once you've extracted all the co-ordinates, sort the output list in ascending order using the distance column as the key for the sort.
5. The first 10 rows are the closest points.

**pitakoc** · 12-01-2012, 01:29 AM

Some of that helped but I still havent been able to get what I'm looking for. I added a link to the spreadsheet to help clarify:

http://dl.dropbox.com/u/38938412/coorddist.xlsx

The "closest" sheet has the input and where I want the 10 closest from the other sheets. The sheet to the right has columns where I'd like the ten closest coordinates for the input. The other tabs have the coordinates as well as the distance. What I'm looking to do is have those 10 columns update with the X Y coordinates for the 5 different sheets. I only need to figure out one and then I can get the rest from there but I'm not sure how to go about that. Let me know if I can clarify anything.

**Richard Buttrey** · 12-01-2012, 06:17 AM

Please upload the workbook to this forum. Some of us are reluctant to use external sites.

**pitakoc** · 12-01-2012, 11:20 AM

I tried but apparently the file is to big at 19 MB? Is there a way to have the coordinate sheet with the distances automatically sort itself when the input changes on the first sheet? I added images of the two sheets I'm working with. So in D5-D14 of the "Closest" sheet I want the X coordinate of the 10 closest, and E5-14 I want the Y coordinate of the 10 closest. I didn't sort it because the input will change making the closest all different.