View Single Post
 
Old 02-24-2020, 06:36 AM
p45cal p45cal is offline Windows 10 Office 2019
Expert
 
Join Date: Apr 2014
Posts: 389
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