replicating formuleas?
 

I have a large spreadsheet over several pages and i am crossreferencing the
pages.

I want to point to a cell on a previous sheets which i can do but!!! i have
about 70 rows to do this on and wonder if there is anyway of copying the
formulea down. I know you can click on the bottom right and drag but the
problem is the cells in the previous page have gaps in them

e.g.
timesheets!L67
timesheets!l117
timesheets!167
they are 50 rows apart and i want the info from that sheet pulling through
onto the one i am working on

I can do it long hand but i have hundreds to do and i am hoping there is an
esy way??

replicating formuleas?
 

Assuming this formula is entered into cell A1:
=INDEX(Timesheets!L$67:L$500,(ROWS(A$1:A1)-1)*50+1)

Change (Rows(A$1:A1)) to whatever the cell address you actually enter the
formula into. Also change Timesheets!L$67:L$500 to whatever the actual range
is.



"Steve" wrote:

I have a large spreadsheet over several pages and i am crossreferencing the
pages.

I want to point to a cell on a previous sheets which i can do but!!! i have
about 70 rows to do this on and wonder if there is anyway of copying the
formulea down. I know you can click on the bottom right and drag but the
problem is the cells in the previous page have gaps in them

e.g.
timesheets!L67
timesheets!l117
timesheets!167
they are 50 rows apart and i want the info from that sheet pulling through
onto the one i am working on

I can do it long hand but i have hundreds to do and i am hoping there is an
esy way??

replicating formuleas?
 

In A1 of target sheet enter this.

=OFFSET(timesheets!$L$1,50*ROW()+16,0)

Copy down.


Gord Dibben MS Excel MVP

On Sun, 2 Sep 2007 09:20:05 -0700, Steve
wrote:

I have a large spreadsheet over several pages and i am crossreferencing the
pages.

I want to point to a cell on a previous sheets which i can do but!!! i have
about 70 rows to do this on and wonder if there is anyway of copying the
formulea down. I know you can click on the bottom right and drag but the
problem is the cells in the previous page have gaps in them

e.g.
timesheets!L67
timesheets!l117
timesheets!167
they are 50 rows apart and i want the info from that sheet pulling through
onto the one i am working on

I can do it long hand but i have hundreds to do and i am hoping there is an
esy way??

replicating formuleas?
 

Steve,
If your timesheets! are customized firmly having a jump every 50rows, the
offset will work yet the way you like it shows that you like the cell address
to be as the *formula*..
e.g.
timesheets!L67
timesheets!l117 timesheets!L117
timesheets!167 timesheets!L167

---so otherwise select the range you need then an auto-filter on the
"timesheets" tab will allow you to get all the data u need to be linked to
the worksheet.
---select the range of cells
---copypaste specialpaste link to the worksheet...

Regards


"Steve" wrote:

I have a large spreadsheet over several pages and i am crossreferencing the
pages.

I want to point to a cell on a previous sheets which i can do but!!! i have
about 70 rows to do this on and wonder if there is anyway of copying the
formulea down. I know you can click on the bottom right and drag but the
problem is the cells in the previous page have gaps in them

e.g.
timesheets!L67
timesheets!l117
timesheets!167
they are 50 rows apart and i want the info from that sheet pulling through
onto the one i am working on

I can do it long hand but i have hundreds to do and i am hoping there is an
esy way??