A completely different way to go is to bypass the autofill and make your own.
In A2 change the code to
Then just drag it out on the row like you wanted to.
What we do here is add and offset to the reference cell.
Syntax for offset is:
OFFSET(reference, rows, cols, [height], [width])
We refer to the original target
B273 but we don't want autofill to mess with it anymore so we make it an absolute reference by adding
$ before the column and row. Offset then changes the row we want to look at. (B273,x,0)
height and width are optional
But we need the offset in row to change with the columns, as we drag the code out, so instead of a number, we add another function
Column() simply returns the current column. In A that would be 1, B=2, C=3 and so on.
Since we start in the A column, it's already adding one row. Therefore we need to remove one row to compensate, hence the
This can of course also be done by changing the initial
$B$272 and remove the
I just wanted to include the -1, so that it's clearer what to do if you plan on starting the sequence on another column, and so that we can see what the original target is.