I have a range
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.
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.