Cell References
 

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!

Cell References
 

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!

Cell References
 

In H5 enter =Sheet1!Y5
Copy this down the column ( number of rows = 5 times the number of formulas
needed)
Now deleted the unneeded rows
Maybe, first use conditional formatting to highlight needed cells; Formula
IS =MOD(ROW(),5)=0
Select the formulas that remain and drag to the right to column L
But VBA would be faster if you have lots to do
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
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!