How to display named cells in Excel

When using a named range in Excel (2013), how to display the entire range but offset a cell?

Option 1: You can use INDEX to return any value from a named range by row and column. So in your example, in B11 (or anywhere) you could use:

= INDEX (distances, 1,1)

To get the value from the first row and the first column. But that won't be ideal to go in. So use the current COLUMN number and set the starting position:

= INDEX (distances, 1, COLUMN () - 10)

That will copy quite happily. The -10 is the adjustment because it starts in column 11 (in your example).

Option 2: You tried to use the implicit crossing method by just taking "= distances" assuming you want to determine where that area intersects with the row or column of the current cell (it only intersects a row or a column or no) . Instead, try one explicit Intersection where you specify two areas separated by a space (which is why you cannot use spaces in area names). You can use both names, or in your case just use them in B11:

= Distances A: A

This intersects your distance range in A2: G2 with column A (instead of B where the formula is). And of course that will be very happy.