two worksheet reference formula help
 

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

two worksheet reference formula help
 

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