Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
change formula- keep cell increment sheet numbers
Hi there, i have a workbook consisting of approx 70 sheets, named 1 - 70.
One sheet has formuals that read cells from all the others sheets. Example C1 = '1'!$A$4 C2 = '2'!$A$4 C3 = '3'!$A$4 I need to drag the formula down from C1:C70, but can't find a way that it increments sheet numbers. It is taking for-ever to cut and copy, and then change the sheet numbers individually. This sheet also has formulas reading C1:C70 all the way through to AK1:AK70 so too many cells to copy. Any ideas on how to increment sheet numbers each row down. many thanks Ali |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
change formula- keep cell increment sheet numbers
If your sheet names are simply numbers, you could do:
=INDIRECT("'"&ROW(A1)&"'!A4") Copying this down will cause the reference to change to A2, A3, etc, thus changing your sheet. Note that since the A4 is inside quotes (is text in the formula), it never changes so there is no need for absolute reference on that part. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Ali" wrote: Hi there, i have a workbook consisting of approx 70 sheets, named 1 - 70. One sheet has formuals that read cells from all the others sheets. Example C1 = '1'!$A$4 C2 = '2'!$A$4 C3 = '3'!$A$4 I need to drag the formula down from C1:C70, but can't find a way that it increments sheet numbers. It is taking for-ever to cut and copy, and then change the sheet numbers individually. This sheet also has formulas reading C1:C70 all the way through to AK1:AK70 so too many cells to copy. Any ideas on how to increment sheet numbers each row down. many thanks Ali |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
change formula- keep cell increment sheet numbers
Hi Luke, this all works fine if I start a new sheet with this formula.
problem is my info in-correct. my actual sheet starts on C8 = '1'!$A$4 C9 = '2'!$A$4 C10 = '3'!$A$4 Not sure how to do this as it is not starting on Line 1/ SECONDLY, the sheets are presently set up and named as 1,2,3,4, etc. So your formula works fine. however, once the sheets have been re-used they will be re-named as guests name 1 might become Keith 2 might become James 3 might become Jones once names are changed the formula then gives a Ref error. Any ideas, thanks "Luke M" wrote: If your sheet names are simply numbers, you could do: =INDIRECT("'"&ROW(A1)&"'!A4") Copying this down will cause the reference to change to A2, A3, etc, thus changing your sheet. Note that since the A4 is inside quotes (is text in the formula), it never changes so there is no need for absolute reference on that part. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Ali" wrote: Hi there, i have a workbook consisting of approx 70 sheets, named 1 - 70. One sheet has formuals that read cells from all the others sheets. Example C1 = '1'!$A$4 C2 = '2'!$A$4 C3 = '3'!$A$4 I need to drag the formula down from C1:C70, but can't find a way that it increments sheet numbers. It is taking for-ever to cut and copy, and then change the sheet numbers individually. This sheet also has formulas reading C1:C70 all the way through to AK1:AK70 so too many cells to copy. Any ideas on how to increment sheet numbers each row down. many thanks Ali |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
change formula- keep cell increment sheet numbers
Hi Luke, this all works fine if I start a new sheet with this formula.
problem is my info in-correct. my actual sheet starts on C8 = '1'!$A$4 C9 = '2'!$A$4 C10 = '3'!$A$4 Not sure how to do this as it is not starting on Line 1 and it needs to for formula to work SECONDLY, the sheets are presently set up and named as 1,2,3,4, etc. So your formula works fine. however, once the sheets have been used they will be re-named as guests name 1 might become Keith 2 might become James 3 might become Jones once names are changed the formula then gives a Ref error. Any ideas, thanks "Luke M" wrote: If your sheet names are simply numbers, you could do: =INDIRECT("'"&ROW(A1)&"'!A4") Copying this down will cause the reference to change to A2, A3, etc, thus changing your sheet. Note that since the A4 is inside quotes (is text in the formula), it never changes so there is no need for absolute reference on that part. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Ali" wrote: Hi there, i have a workbook consisting of approx 70 sheets, named 1 - 70. One sheet has formuals that read cells from all the others sheets. Example C1 = '1'!$A$4 C2 = '2'!$A$4 C3 = '3'!$A$4 I need to drag the formula down from C1:C70, but can't find a way that it increments sheet numbers. It is taking for-ever to cut and copy, and then change the sheet numbers individually. This sheet also has formulas reading C1:C70 all the way through to AK1:AK70 so too many cells to copy. Any ideas on how to increment sheet numbers each row down. many thanks Ali |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy formula - keep cell same but increment sheet numbers | Excel Discussion (Misc queries) | |||
Formula copy paste down in a sheet but change row letter increment | Excel Discussion (Misc queries) | |||
Automatically increment numbers in a cell when you open workbook | Excel Discussion (Misc queries) | |||
Change the work sheet name in a formula by using cell reference | Excel Worksheet Functions | |||
Automatic cell increment with data from sheet 1 to sheet 2 | Excel Worksheet Functions |