Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replicating | Excel Discussion (Misc queries) | |||
replicating a folder hiearchy. | New Users to Excel | |||
Replicating Formulas between excel files | Excel Discussion (Misc queries) | |||
Replicating Formulas with Various Worksheet References | Excel Worksheet Functions | |||
Replicating Worksheet References in Formulas | Excel Worksheet Functions |