I have a range $A$2:$G$2 named Distances; later in the sheet, I want to populate the range B11:H11 with the data in the original range. I can start in cell A11 and type =Distances and it will populate the value from A2. Using the fill handle, I can fill the range of A11:G11, but as soon as I try to put the range into B11:H11, the values transition directly. (E.g. only the values from column 2 transition to column 2, even though I want the value from column 1 (start of the range) to fill the referenced cell in column 2 (start of the reference).) I also cannot start the range reference in column 2 (e.g. =Distances in B2) and get the referenced value to differ from the value of the associated column.

How can I reference the range from somewhere else in sheet, and have the first referencing cell show the value from the first cell in the named range? Is this even possible?

Edit: For clarity, I'm adding the following visualization, although @excellll's answer took care of the issue quite handily...

If my named range is the abdf section below, and I want to display the same abdf values, but starting at the X, how do I do that?

|a|b|d|f| | | |
| | | | | | | |
| | |X| | | | |
| | | | | | | |

@excellll's answer indicates the solution involves using an array formula.


One solution is to enter the reference to the named range as an array formula. To do this, select the entire desired output range, B11:H11 in your example. Without changing the selection, type the formula


and press Ctrl+Shift+Enter. This will enter the formula into the entire selected range and should give you what you want.


Option 1: You could 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:


to get the value from the first row and first column. But this won't be ideal to paste across. So use the current COLUMN number and adjust for starting position:


This will copy across quite happily. The -10 is the adjustment because of starting in column 11 (in your example).

Option 2: You have tried using the implicit intersection method just using "=Distances", which assumes you want to find where that range intersects with the current cell's row or column (it will only intersect with one row or one column, or none). Instead, try using an explicit intersection where you specify two ranges, separated by a space (which is why you can't use spaces in range names). They can both be named ranges, or in your case, simply use, in B11:

=Distances A:A

This will intersect your Distances range in A2:G2 with column A (rather than B, where the formula is). And this will of course copy across, or down, quite happily.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.