#1
|
|||
|
|||
GetDistance Formula
Hello, Excel World! I've been using the GetDistance formula to map distances between a list of addresses. I was able to get the formula to work about a week ago. But then I tried adding a couple of rows to both my source data sheet and the main sheet. Since then, I've been getting a #NAME? error. I've double-checked and triple-checked the syntax in the formula, and it seems to be correct. And the other formulas I use work just fine as well. Have there been any Excel updates in the last week that are incompatible with the GetDistance formula? I'm at a loss for what the problem could be. Here's the formula I'm using: =GetDistance(VLOOKUP(B$1,CRW,8,FALSE),VLOOKUP(B$1, CRW,9,FALSE),VLOOKUP($A2,CRW,8,FALSE),VLOOKUP($A2, CRW,9,FALSE)) CRW is the name of the table I'm pulling the data from. The table has the people's names listed in the top row, as well as in column A. So B1 and A2 in the formula represent the same person, and the formula continues from there. I should also say that the column lookups, 8 and 9, are the latitude and longitude coordinates for the addresses. Thank you in advance! |
#2
|
||||
|
||||
Either the user-defined function GetDistance isn't found or CRW isn't found.
You can tell which by selecting one of the cells with #NAME? in, then clicking the Evaluate Formula button in the Formula Auditing section of the Formulas tab of the ribbon. Step through resulting dialogue box and it should be obvious which is missing. We'd really neeed to see a workbook. Can you attach one here? |
#3
|
|||
|
|||
Well, double-checked, and the named range CRW is there, as is the VBA module behind the GetDistance function. Any idea why either of those wouldn't be recognized?
|
#4
|
||||
|
||||
First, which of the two isn't recognised?
|
#5
|
|||
|
|||
The getdistance function.
|
#6
|
||||
|
||||
Open the VBE at the said function.
What kind of module is it in? (Sheet code-module, Standard code-module, ThisWorkbook code-module), and which workbook is it in; the same one as the sheet which has the function in one of its cells?, perhaps PERSONAL.XLSB? Attach the workbook, it's a lot easier. |
#7
|
|||
|
|||
Sorry, I should have done that in the first place. I whipped up a smaller version of the spreadsheet with fake names to protect privacy. I left the addresses blank, but kept the coordinates, which is how the formula calculates the distance. And in this particular workbook, the table it's grabbing data from is called Directory, instead of CRW. The name of the VBA module is GetDistance.
Thanks for helping me out! |
#8
|
||||
|
||||
Ha ha ha!
Change the name of the module from GetDistance to anything that's not the same as the name of a function! You might have to do something similar with the other module. You could instead change the formulae to the likes of: =GetDistance.GetDistance(VLOOKUP(B$1,Dir… |
#9
|
|||
|
|||
Wow, I didn’t even think of that. Thanks for the suggestion! It works fine now.
Quote:
|
#10
|
|||
|
|||
So I really appreciate your help last week. I have since added a worksheet to my workbook, wherein I rank the distances for each person in relationship to each other. I have attached the same file with the third sheet added, so you'll see what I mean.
The sheet 1 tab contains the rankings from smallest to largest (i.e. closest to farthest away), and right now I'm using an Index Match formula and a Small formula. However, right now, the Small formula references a specific row in the Distance tab, meaning that if I ever sort the rows in the Distance tab in a different order, the rankings will be incorrect. I know I need to reference the person's name in the formula and leave out the specific row reference, but unfortunately, Index Match formulas are not my strong suit. Any suggestions? Do I need an Array formula? What would it look like? Thanks! |
#11
|
||||
|
||||
In the attached on a copy of your sheet calle Sheet 1(2) is an awful but working formula which you need to check gives the right results. You can put this formula in B2 and copy down and across, but you will need to change some references within the formula to fit your data ranges. Try not to use entire columns in the references as it will take longer for the formulae to calculate.
There is an alternative solution on that sheet in terms of a pivot table which is based on your Table3 on the Distance sheet. You can add more data to Table3, change its size in both directions, then all you have to do is refresh the pivot table. It uses Power Query behind the scenes to create an invisible table which in turn feeds the pivot table. The info in the pivot is arranged differently (vertically) with the To field being sorted smallest to largest Distance, but you can quickly filter the results (I've added a slicer as it's easier to use than the dropdown arrows). |
Tags |
#name?, getdistance |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula Copy Row 2 Row But Next Column In Formula From Another Tab | TimG | Excel | 3 | 04-16-2018 09:20 PM |
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula | innkeeper9 | Excel | 2 | 09-13-2016 08:59 PM |
Need help with dragging a formula and changing a reference column as I drag the formula. | LupeB | Excel | 1 | 10-22-2015 03:02 PM |
Formula | Excel | Excel | 1 | 08-14-2014 05:11 PM |
help with formula? | doczilla | Excel | 2 | 09-25-2011 04:14 PM |