![]() I deleted the “Column”, “Row” and “Include” columns to be left with the following: SW Grid I copied the whole sheet and use “Paste values only” on a new Worksheet (“Squares2”) to remove the formulas. Then I sorted the spreadsheet using this column, deleted all the rows saying “No”, and re-sorted my spreadsheet alphabetically by SW Grid reference. I added a column called “Include” and entered a formula that would return “No” if any of the three grid references did not begin with the letter S (as all my valid grid references begin with S). You will notice that some of the grid reference columns are now populated with the headings from around your grid reference table. This is the bit where we lose the squares at the north and east edge of our area. Then use the ADDRESS and INDIRECT formulas to retrieve the grid references for the NW, NE and SE corners. Column: =SUMPRODUCT(-(grid=A2)*COLUMN(grid)) To make these formulas easier to read, I added a named range called “grid” that contained the grid references on the coloured cells on my “Grid” worksheet.Īdd “Column” and “Row” columns after the “SW Grid” column and use these formulas to populate them with the column and row numbers of grid reference for the SW corner. Add columns “NW Grid”, “NE Grid” and “SE Grid” which will be populated with grid references for the other corners. ![]() This column is the grid reference for the south-west corner of the square. Add a column heading saying “SW Grid” and paste your original list of grid references underneath. Methodology for obtaining coordinates for other corners.Ĭreate a new tab on your Excel workbook called “Squares”. The south-west corner is our “origin”, then we can add 1 to the row number to get the north-west corner, add 1 to the column number to get the south-east corner, and add 1 to the column and row numbers to get the north-east corner. We will use this concept to get the grid references for each corner of each square. The north-east corner of each square is the south-west corner of the square that is 1km to the north, and 1km to the east of the starting square. ![]() Now we have the south-west corner of each grid square as a latitude and longitude, but in order to draw an area for each grid square, we need the other corners as well. I also added a header row to keep things tidy. The converter will produce a tab-separated output which is ready to be copied and pasted into a new tab (I called mine “LatLong”) on your Excel workbook. Paste in your list of grid references, set the options to convert from grid references to lat and long, then click convert. SE0001 Convert grid coordinates to lat and longĪt first I thought I would need to do this manually, with some help from the Ordnance Survey coordinate tools spreadsheets, but thankfully some helpful person has created a batch conversion tool that makes this super easy. My real list has 952 squares in total! SD9800 Here’s the top 10 rows of mine, which I will continue to use as examples throughout this article. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |