Quick way to fill formula in large grids?

[JamesCBRE]

Hello All - This is my first post. I'm in way over my head on a project and came across this forum in my search for a clue.

I have a couple of lists of locations (MSAs and airports) with lat/lon and a getdist VB script. I'm able to calculate the distances (miles) one column at a time but I'd love to know a way to fill in the entire grid quickly. Below is a link to the file, areas in blue on first and second sheets are what I'm trying to solve:

http://marketing.cbre.com/SanDiegoArea/jmccarthy/Distance Grids - MSA and Airports.xlsm

Any tips or help would be greatly appreciated!

 

[skywriter]

You should be able to copy the formula in B2 into all the other cells on this sheet and it will work. Just put it into cell B2 first and enter it, then copy it select all the other cells and paste it.

Grid - MSA Distances

*	A	B	C

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ffff00, align: right"]CBSA Code[/TD]
[TD="bgcolor: #ffff00, align: right"]10180[/TD]
[TD="bgcolor: #ffff00, align: right"]10420[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ffff00, align: right"]10180[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,168[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =getDist(INDEX('CBSA Lat-Lon'!$D$2:$D$382,MATCH($A2,'CBSA Lat-Lon'!$A$2:$A$382,0)),INDEX('CBSA Lat-Lon'!$E$2:$E$382,MATCH($A2,'CBSA Lat-Lon'!$A$2:$A$382,0)),INDEX('CBSA Lat-Lon'!$D$2:$D$382,MATCH(B$1,'CBSA Lat-Lon'!$A$2:$A$382,0)),INDEX('CBSA Lat-Lon'!$E$2:$E$382,MATCH(B$1,'CBSA Lat-Lon'!$A$2:$A$382,0))) C2 =getDist(INDEX('CBSA Lat-Lon'!$D$2:$D$382,MATCH($A2,'CBSA Lat-Lon'!$A$2:$A$382,0)),INDEX('CBSA Lat-Lon'!$E$2:$E$382,MATCH($A2,'CBSA Lat-Lon'!$A$2:$A$382,0)),INDEX('CBSA Lat-Lon'!$D$2:$D$382,MATCH(C$1,'CBSA Lat-Lon'!$A$2:$A$382,0)),INDEX('CBSA Lat-Lon'!$E$2:$E$382,MATCH(C$1,'CBSA Lat-Lon'!$A$2:$A$382,0))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[skywriter]

Here's the other sheet.

Excel Workbook
	A	B	C	D	E
1	CBSA Name	Closest Airport Code	Miles Away	CBSA Code	ABQ
2	Abilene, TX	DFW	160	10180	433
Grid - Airport-MSA Distances

 

[JamesCBRE]

Wow Skywriter, you just freed up my entire day. Thanks so much!

 

[skywriter]

Really, it was my pleasure. I like your user defined function. I was once involved in surveying as a career and this calculating distance by lat lon was the kind of problems I was faced with in my Trigonometry class in college, fun stuff.

 

[JamesCBRE]

Well, thanks again. You really saved me a ton of time. I can't take credit for the getdist function, but it too was a lifesaver. I do site selection for data centers and distances to transportation, fiber lines, population centers, etc. are extremely important. The ability to calculate distances in bulk will help us do a much better job.