Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-09-2020, 12:20 PM
mandersen04 mandersen04 is offline GetDistance Formula Windows 10 GetDistance Formula Office 2016
Novice
GetDistance Formula
 
Join Date: Sep 2018
Posts: 12
mandersen04 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 02-10-2020, 05:01 AM
p45cal p45cal is offline GetDistance Formula Windows 10 GetDistance Formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 392
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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?
Reply With Quote
  #3  
Old 02-10-2020, 05:50 PM
mandersen04 mandersen04 is offline GetDistance Formula Windows 10 GetDistance Formula Office 2016
Novice
GetDistance Formula
 
Join Date: Sep 2018
Posts: 12
mandersen04 is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 02-10-2020, 06:37 PM
p45cal p45cal is offline GetDistance Formula Windows 10 GetDistance Formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 392
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

First, which of the two isn't recognised?
Reply With Quote
  #5  
Old 02-11-2020, 07:22 AM
mandersen04 mandersen04 is offline GetDistance Formula Windows 10 GetDistance Formula Office 2016
Novice
GetDistance Formula
 
Join Date: Sep 2018
Posts: 12
mandersen04 is on a distinguished road
Default

The getdistance function.
Reply With Quote
  #6  
Old 02-11-2020, 07:48 AM
p45cal p45cal is offline GetDistance Formula Windows 10 GetDistance Formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 392
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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.
Reply With Quote
  #7  
Old 02-11-2020, 09:37 AM
mandersen04 mandersen04 is offline GetDistance Formula Windows 10 GetDistance Formula Office 2016
Novice
GetDistance Formula
 
Join Date: Sep 2018
Posts: 12
mandersen04 is on a distinguished road
Default

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!
Attached Files
File Type: xlsm Copy of CRW Addresses for Matt.xlsm (19.6 KB, 6 views)
Reply With Quote
  #8  
Old 02-11-2020, 10:26 AM
p45cal p45cal is offline GetDistance Formula Windows 10 GetDistance Formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 392
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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…
Reply With Quote
  #9  
Old 02-12-2020, 03:08 AM
mandersen04 mandersen04 is offline GetDistance Formula Windows 10 GetDistance Formula Office 2016
Novice
GetDistance Formula
 
Join Date: Sep 2018
Posts: 12
mandersen04 is on a distinguished road
Default

Wow, I didnít even think of that. Thanks for the suggestion! It works fine now.



Quote:
Originally Posted by p45cal View Post
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Ö
Reply With Quote
  #10  
Old 02-22-2020, 06:07 AM
mandersen04 mandersen04 is offline GetDistance Formula Windows 10 GetDistance Formula Office 2016
Novice
GetDistance Formula
 
Join Date: Sep 2018
Posts: 12
mandersen04 is on a distinguished road
Default

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!
Attached Files
File Type: xlsm Copy of CRW Addresses for Matt.xlsm (22.8 KB, 1 views)
Reply With Quote
  #11  
Old 02-24-2020, 06:36 AM
p45cal p45cal is offline GetDistance Formula Windows 10 GetDistance Formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 392
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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).
Attached Files
File Type: xlsm msofficeforums44365CRW Addresses for Matt.xlsm (38.8 KB, 0 views)
Reply With Quote
Reply

Tags
#name?, getdistance

Thread Tools
Display Modes


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
GetDistance Formula 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
GetDistance Formula Formula Excel Excel 1 08-14-2014 05:11 PM
GetDistance Formula help with formula? doczilla Excel 2 09-25-2011 04:14 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:43 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft