#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 48
Default 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!

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 340
Default 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!


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default 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!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using an offset formula for the reference in a relative reference Cuda Excel Worksheet Functions 6 November 15th 06 06:12 PM
Furmula Dependent Cell References ned Excel Discussion (Misc queries) 2 May 4th 06 05:03 PM
Automatically Changing Cell Reference's when Pasting in Excel No I'm Spartacus Excel Discussion (Misc queries) 0 February 13th 06 02:01 PM
Cell references change when entering new data [email protected] New Users to Excel 2 May 6th 05 07:48 PM
Absolute cell references and subsequent problems. Pank Mehta Excel Discussion (Misc queries) 2 April 19th 05 11:38 AM


All times are GMT +1. The time now is 09:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"