Distance Column Trickery Help Required

Hi all,

I have a directory list of places with addresses. When a user looks at the details of one of these places I want to add an in-line list below that shows other places that are nearby.
I am using the distance column elsewhere but I can’t work out a way to calculate the distance between the currently selected place and all other places in the directory that I can then filter based upon distance away.

I have tried adding in a column that copies a ‘1’ to all rows in the directory and then creating a multiple relation to this which returns all of the places, but if you then try and create a distance column using this relation it returns one value (I.e. it doesn’t recognise multiple entries).

Does anyone know of a way around this?

I’m thinking this would be kind of hard to achieve but here’s my two cents.

Let’s say you have 10 addresses, you can assign an ID for each of them (1 to 10).

Then you would have two buttons, one to increase a user-specific column, let’s say “View choice”, with the value that can be mapped to the IDs, and one other to decrease that same column. Basically a “next place” and “previous place” navigation.

Next, you create a relation linking that user-specific column’s value back to the ID, and return the place’s address via a lookup. Let’s say we can do all of this in a sheet that is not your “Directory” sheet.

After finishing the above steps, go back to the “Directory” sheet, create a single column to take the lookup address back. So now you’re having the address of the place that the user is viewing, and then you can create a dynamic distance to other places based on each user’s view.

Hope it can help, just wrote out what I can think of.

Thanks for that. With what you have described I think I would need to have the directory sorted by location to enable the next nearest place to be shown? I’m not sure that will be possible as the directory is already 200+ with new places being added which would need to be sorted to the right place in the directory - I think?!

I’m also trying to minimise the number of clicks the user has to do so I’m not sure I’m too keen on having next and previous buttons.

I do really appreciate your ideas - This is how we all learn and create better apps.

Yeah that would be another headache, because places with ID 199 or 200 would never be able to reach the customer’s screen. Hopefully someone else can chime in with an idea.


I think the only way to achieve this is to have a selection of some kind. Not the UI you’re looking for. User would select the place and then nearby locations would appear. There needs to be a single value column in your editor to which to check all the other addresses. That single value column can’t populate itself dynamically/ automatically based on simply viewing a screen…it would need to be chosen. Hm. Interesting problem.

Thanks Robert. I understand what you are saying but I just can’t think of a solution that avoids the user needing to do multiple clicks to get to the end result. Will keep thinking…