two worksheet reference formula help

[chadhart]

Hi,
I'm creating a worksheet for work with 1000 addresses on Sheet1, they start at row 11 and continue on every row until 1011.
On Sheet2 I have a bunch of information on those houses starting at row 10 and the information is 79 rows. So I want a reference on line 10 on Sheet2 to the address on Sheet1 at line 11 (normally I would just put "=Sheet1!A11") and then the next address reference would need to be at row 80 on Sheet2 and it would normally be "=Sheet1!A12" and so on, however as you all know when I copy that 80 rows of information to duplicate it for the next house it changes that formula to "=Sheet1!A91" and so on, and I have so much information I reached the bottom of the sheet at row 65536. and so if I had to go through and change all those references it would take me hours and hours. How do a copy that formula 80 rows down on Sheet2 but only have it reference one row down on Sheet1? Thanks for the help. I have started to get an idea I just don't know the formulas enough. I was trying to do it with =INDIRECT(Sheet1!A ... but I haven't figured it out yet. THANKS!

[SiC]

Some part of the information that's provided didn't make sense to me. Maybe
I'm reading it wong, but if you start sheet2 at row 10 with 79 rows of
information, doesn't that end at row 88?

Anyway, assuming you want row 80 from sheet2 to refer to A2 from sheet1, row
160 from sheet2 to refer to A3 from sheet1, you can try:
=INDIRECT("Sheet1!A"&INT(row()/80)+1)
I'm sure I read your description wrong and the row number need to be
adjusted, but that's the general idea and you can adjust my formula
accordingly.

-Simon

"chadhart" wrote:

>
> Hi,
> I'm creating a worksheet for work with 1000 addresses on Sheet1, they
> start at row 11 and continue on every row until 1011.
> On Sheet2 I have a bunch of information on those houses starting at row
> 10 and the information is 79 rows. So I want a reference on line 10 on
> Sheet2 to the address on Sheet1 at line 11 (normally I would just put
> "=Sheet1!A11") and then the next address reference would need to be at
> row 80 on Sheet2 and it would normally be "=Sheet1!A12" and so on,
> however as you all know when I copy that 80 rows of information to
> duplicate it for the next house it changes that formula to
> "=Sheet1!A91" and so on, and I have so much information I reached the
> bottom of the sheet at row 65536. and so if I had to go through and
> change all those references it would take me hours and hours. How do a
> copy that formula 80 rows down on Sheet2 but only have it reference one
> row down on Sheet1? Thanks for the help. I have started to get an idea I
> just don't know the formulas enough. I was trying to do it with
> =INDIRECT(Sheet1!A ... but I haven't figured it out yet. THANKS!
>
>
> --
> chadhart
> ------------------------------------------------------------------------
> chadhart's Profile: http://www.excelforum.com/member.php...o&userid=35059
> View this thread: http://www.excelforum.com/showthread...hreadid=548013
>
>