Start by entering the two values as below in sheet2 as below:

H5: y5
H6: y10

then drag each cell with the fill handle in the right corner to get the
range H5:L6 as below:

y5 y6 y7 y8 y9
y10 y11 y12 y13 y14

Now select this 2x5 block and drag down as far as needed to get all
references.
Then evaluate with Edit Replace Find: 'y' , Relace with '=Sheet1!y'.

wrote:

Hi,

I want to do the following in a formula (not in a macro). Is it
possible?

In Sheet 2 Cell H5, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y5)
In Sheet 2 Cell I5, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y6)
In Sheet 2 Cell J5, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y7)
In Sheet 2 Cell K5, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y8)
In Sheet 2 Cell L5, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y9)

In Sheet 2 Cell H6, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y10)
In Sheet 2 Cell I6, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y11)
In Sheet 2 Cell J6, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y12)
In Sheet 2 Cell K6, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y13)
In Sheet 2 Cell L6, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y14)

In Sheet 2 Cell H7, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y15)
In Sheet 2 Cell I7, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y16)
In Sheet 2 Cell J7, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y17)
In Sheet 2 Cell K7, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y18)
In Sheet 2 Cell L7, I want to link to Sheet 1 Cell Y5 (=Sheet1!Y19)

And so on...

I am going to have this go down for many many rows, and was wondering
if there was a way to say this so that I could drop down a formula. I
tried putting 5, 6, 7, 8, 9 in cells and referencing them (I was going
to use multiples for each row), but was unsuccessful.

Can anyone help?

Thanks!