
Auto Increment Worksheet While Copying


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
How do make an auto increment of worksheet while copying?
example:
if i copy one cell(A1) by dragging the mouse, this will be the result:
(B1 is the worksheet)
Cell > Formula
A1 > =SUM('B1'!$E$3:$E$14)
A2 > =SUM('B1'!$E$3:$E$14)
A3 > =SUM('B1'!$E$3:$E$14)
and so on...
the result i want is like this while copying by dragging:
Cell > Formula (B1 is the sheet)
A1 > =SUM('B1'!$E$3:$E$14)
A2 > =SUM('B2'!$E$3:$E$14)
A3 > =SUM('B3'!$E$3:$E$14)
and so on...
Need some guidance.. thanks
Similar Excel Video Tutorials
VLOOKUP & Data Validation for Recipes
 See how to take recipes listed in a column, set them up in rows, then create a drop down arrow to select recipes and display ingredients and direction ...
Incrementing Numbers In Formulas
 Learn about the ROWS, COLUMNS, MOD, ROUND, and WEEKDAY functions :to: 1)Increment from small to big 2)Increment from big to small 3)Inc ...
Turn Off Auto Format
 See how to Turn Off Auto Format so that column and row formatting or formulas are not automatically created. See how to use Excel Options to set unive ...
Increment Word & Number 2 Methods.
 See how to do create a column of words and numbers quickly with two different formulas and a mouse trick for Paste Special Values. See how to incremen ...
Similar Topics
Hi!
I would really apreciate some help...
I have a worksheet with some data. I have another worksheet where I make a connection to the 1st worksheet the following way...
Field A1=Sheet1!A1
Field A2=Sheet1!A6
Field A3=Sheet1!A10
...
Now when I simply try to copy those formulas (expecting the references to the other worksheet to increase by 5) i get nothing, that is, I get an increment of 1 for the rest of the fields...
How do I force excel to increment by 5 when copying cells?
Thanks!!!!
I need to copy one date onto adjacent cells by dragging from the rightdown corner, as when copying an ordinary number.
In the case of dates, however, that fills each cell of the series with the date following the previous one.
How can I cancel that serial filling, so that such dragging will just copy the same original date into all those cells?
Thank you for any help you can provide.
ACA
How do I copy individual cells into one worksheet from other (more than one)
worksheets into a formula and then copy the remainder of the column to match
each corresponding cell like the first one copied so that I am copying same
cell areas from each worksheet when I drag and copy that one formula? Not
sure if I explained this correctly. For example there are four lodging
properties (or worksheets with the same template) and one resort worksheet.
So I am copying a1 from each of the four worksheets in a simple addition
formula that I need to have duplicate for b1, c1, and so forth across the
resort worksheet column without having to go to each worksheet for each
formula with my mouse. Hope that explains it.
How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example:
I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2.
Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.
Thanks, Simon.
Hello.
I need to copy a chart to several different pages of an Excel presentation
spreadsheet, and would like for the data source cell references to increment
by 50 each time. I'm copying the chart 50 rows down each time, but the data
source references do not increment. When I change the data source references
from "$A$25" to "A25", they get changed back to the "$A$25" absolute
reference when I click Ok.
I expect that the chart objects aren't tied to specific cells on the
presentation spreadsheet  so Excel doesn't increment the references when
they get copied.
But ........... is there any easy to increment the data source cell
references when I copy so I don't have to manually change them on each new
copy of the chart?
Thanks in advance  Dave
Hello.
I need to copy a chart to several different pages of an Excel presentation
spreadsheet, and would like for the data source cell references to increment
by 50 each time. I'm copying the chart 50 rows down each time, but the data
source references do not increment. When I change the data source references
from "$A$25" to "A25", they get changed back to the "$A$25" absolute
reference when I click Ok.
I expect that the chart objects aren't tied to specific cells on the
presentation spreadsheet  so Excel doesn't increment the references when
they get copied.
But ........... is there any easy to increment the data source cell
references when I copy so I don't have to manually change them on each new
copy of the chart?
Thanks in advance  Dave
Greetings,
I'm having problems copying a formula into a certain 'area' of a spreadsheet. The total cell area that I'm copying this formula into is 430 Rows x 179 Cols. Also, this formula pulls information in from other large worksheets. I can copy the formula into the first portion, but there seems to be certain parts that misbehave when I try to continue copying the cells. I'll try to explain 'misbehave' to the best of my ability. If I try dragging and copying, the cell formula gets left blank, but the conditional formating gets copied. If I copy and paste, the cell produces seemingly random results. For example, the first copy and paste produces no formula and a blank cell, the second paste might give me '0.00' or something like that, but still no formula. I can get around the problem in a strange way though. I can't copy things out of the corrupt area into the the corrupt area, but copying cells that start inside of the corrupt area works. So, by manualing copying the formula into a 'corrupted cell', I can copy that cell to the rest of the broken cells.
I'm not getting an error message, so I don't have anything to offer you there. However, I know that something is obviously corrupted or broken. I've tried repairing the file with a few different methods, but it hasn't changed the situation at all. I tried copying everything into a new worksheet, but the issue is still present. I need to eliminate this issue since I may end up passing this spreadsheet off to someone else. If they are unaware that this issue exists, bad information/data may get proliferated. Any help or suggestions with this would be greatly appreciated. Let me know if you need more info about the file.
Regards,
Gav
P.S. Sorry for the long post, but I figured excessive information was better than a lack of.
I use Excel 2010. I just tried copying formulas by using the handle to drag down. It's always worked before when I've used previous versions of Excel. In 2010, dragging down copies the formulas correctly BUT copies the result of the initial formula rather than the result of the correct formula in the current cell. What?? Can anyone help?
Formula dragging question:
I have a sheet where data is in a column format.
I need to have the data copied to another worksheet in a row format. An example is:
='[TEST.xls]Critical QIP'!$B62
I need it to increment B63, B64, B65, etc.... and I'm having a heck of a time getting it to do that with the drag function.
Can someone help?
I have a cell in a worksheet which refers to a localised named range in another worksheet within the same workbook, thus:
Cell '2008 (0)'!C9 contains the formula
=MATCH(C8,'Summary (0)'!Years,0)
I wish to create a duplicate of worksheet '2008 (0)' but without duplicating the worksheet 'Summary (0)', and with the result that cell '2008 (1)'!C9 should contain the formula:
=MATCH(C8,'Summary (0)'!Years,0)
Currently, if I just create a copy of worksheet '2008 (0)' then the formula displayed in cell '2008 (1)'!C9 is shown as
=#N/A
I can get around it by copying the formula across separately after I have created the duplicate worksheet, but I would prefer it if the formula was written such a way as to avoid that necessity. Is this possible, please?
I Need To Have 2  $'s In The Formula So I Can Paste The Col To Another Sheet & Not Have It Get Screwed Up.
"$a" Is No Problem Because Im Staying In The Same Col, But "$1" Needs To Increment But Still Have The $
Not Like This:
=$a1
=$a2
Like This:
=$a$1
=$a$2
I Need The Row To Increment, But Still Have A $ So I Can Cut & Paste It
I Dont Want To Do It With Just An $ Before The Col, Drag It So The Rows Increment, & Then Manually Put In The $ Before The Row
How do I copy down a formula where the cell references will increment by
the same amount? E.g. Sum(B100:B104) will increment to Sum(B105:B109)
as opposed to Sum(B101:B105).
Thanks for any help you can provide...
Rita
I'm having an unusual problem with Vlookup. I enter the formula in a cell and it produces the correct result. However when I copy down the displayed result is exactly the same as in the original cell. When looking at the formulas I note that the lookup cell has been incremented correctly. for example:the original formula is:
Code:
=VLOOKUP(A2,[Book1]Sheet1!$A$3:$Y$318,25,FALSE)
dragging this down the next row formula is:
Code:
=VLOOKUP(A3,[Book1]Sheet1!$A$3:$Y$318,25,FALSE)
and so on. However the result for each cell is the same as in the top one. I have noticed however that if in the formula I manually delete 'A3' and type in 'A3' then it produces the correct result so obviously something is going wrong when draging down the formula even though it appears to be correct
any idea's?
There is a difference between copying all the cells in a sheet and copying the sheet itself.
1. Select all cells in the sheet by pressing Ctrl+A>, or if the selected cell is in a Current Region/List range, press Ctrl+A+A.
Or
Click Select All at the upperleft intersection of rows and columns.
2. Press Ctrl+C.
3. Press Ctrl+Page Down to select another sheet, then select cell A1.
4. Press Enter>.
Copying the entire sheet means copying the cells, the page setup settings, and the defined Names range.
1. Click on a sheet tab.
2. Hold down the Ctrl key, and drag the sheet to a different location.
3. Release the mouse button and then the Ctrl key.
1. Rightclick the appropriate sheet tab.
2. From the shortcut menu, select Move or Copy. The Move or Copy dialog box enables to copy the sheet either to a different location in the current workbook, or to a different workbook. Make sure to mark the Create a copy checkbox.
1. Select View > Arrange (in Window Group).
2. Select Tiled.
3. Use Option 1 above (dragging the sheet while pressing the Ctrl key) to copy or move a sheet.
Hi everyone,
I'm going nuts trying to figure out how to autofill text from one cell
to another. For example, everytime I type text into cell A20, I want
the exact same thing to cell BL20. The same for B20, BL20, etc.
How do I go about that? I already know how to copy formulas from one
cell to another by dragging the skinny black line of the cell. I guess
I just need the formula for a simple IDENTICAL copying of text.
Thanks in advance,
Mike
Hi quick question:
When i copy a cell into a row or colum by clicking and dragging the black
plus sign, a little window pops up that gives me Auto fill options. is there
a way to stop it without copying another cell? i have tried a bunch of
things but no success.
I have a file that links in several different datasources. What I am doing is updating all my sources, running all my calculations then producing "results" sheets. I have several seperate reports being generated from the same data. I am copying the worksheet into a new workbook then saving, emailing, then going onto the next. Problem is when the users open that file their calculation is set to manual because I am manually calculating everything to speed up processing time. If i enable auto calculation after copying the worksheet it calculates my entire workbook, then saves, and goes onto the next. Each time it auto calculates it takes 15 minutes. With manual calc its 2 minutes. How can i make it so that my main file always has auto calc off but make it so that when users open the seperate file auto calc is enabled?
I have a worksheet named App1. I have a macro that creates a new worksheet and copies information. I would then like to rename the worsheet as App2. Each new worksheet added would be incremented  App3, etc.  My question is how can I rename and increment the new worksheet
I would also like to increment a cell in each new worksheet which indicates the application number  1,2,3... etc.
Can you help?
I have a cell that contains a formula.I want to copy this formula to another cell but when I copy and paste it my formula doesnt hold(its a simple = to formula but it doesnt work when I paste it).I tried copying it and dragging it to the new cell.the formula holds when I do this but then it is only in the new cell and I want it to be in both cells.
Does anyone know how to do this?
Thanks.
HI TO all
Pls help me with This one,
im tired of dragging and copying, how can i make this one like this without pressing "ctrl+c" and dragging.
what formula or function must be use to it?
pls help me any help is deeply appreciated
LIB shoud be 22
SHW must be 25
ALB must be 28
now
site,,, no.
LIB,,, 22
LIB,,, 22
LIB,,, 22
SHW,,, 22
SHW,,, 22
SHW,,, 25
SHW,,, 25
LIB,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
LIB,,, 28
ALB,,, 28
LIB,,, 28
ALB,,, 28
SHW,,, 28
ALB,,, 28
ALB,,, 28
AFTER "CTRL+c" +dragging (so very time consuming)
site no.
LIB,,, 22
LIB,,, 22
LIB,,, 22
LIB,,, 22
LIB,,, 22
SHW,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
how can i make it this way.
hi,
How can i set the options of auto fill option so that it will not automatically increment the numbers,so that i can fill all the cell with the same formula for all?Without copying and pasting it manually.
Thanks
Hi,
I know how to drag a hyperlink using the =HYPERLINK formula, but can only get it to work with an increment of 1.
Is there a way to have the increment be larger than one? In this case, by 13 cells?
I attached a sample file.
Hi,
I know how to drag a hyperlink using the =HYPERLINK formula, but can only get it to work with an increment of 1.
Is there a way to have the increment be larger than one? In this case, by 13 cells?
I attached a sample file.
I'm having a weird issue with excel that I haven't seen before. I'm trying to copy a formual to another cell and fill it down and the formula is being copied but the result isn't being properly updated. For example, in Column A I have the following values: 1, 2, 3. In column B, I have 3, 4, 5. I enter a formula in Column C to sum A + B (1+3), and the result appears as 4. When I try to copy or autofill that down through column C (summing 2+4, 3+5), the formula copies and updates but the result is showing the same as in cell C1 (i.e., 4). Any idea of how to fix this?
thanks
i need to change all price values in an inventory. i inserted a new column (C9) next to price column (C10) and in the first cell wrote formula that gave me the new value i wanted (=C10+0.05)
now if i autofill by dragging down from the corner of that cell the formula copies and i get the correct new values in the cells selected.
but...the column has 4500 rows so want to have alternative to dragging down manually with mouse all the way to bottom of doc.
how can i do this? i tried to use 'fill' from the home tab on the ribbon by copying the cell with the formula and clicking on top of column to select it all but whichever way i then tried to 'fill' i simply got the value from the selected cell pasted to the whole column rather than the results of the formula.
thanks in advance

