Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Auto Increment Worksheet While Copying

Forum Register
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

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web

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!!!!


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.



I need to copy one date onto adjacent cells by dragging from the right-down 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 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?




Hi,

Could anyone help me please ? I've tried lots of ways to do this, but none successful so far.

I've got 2 worksheets in the same workbook.

Sheet 1 contains huge amounts of data - thousands of rows and multiple columns

Sheet 2 - I want to extract the data from sheet 1 column A into sheet 2 column A but only the data from every 21st row.

I want to be able to copy the formula automatically down, otherwise it will take hours to do it manually. So far I can only get the cell reference to increment by 1 each time after copying.

So what I'm trying to achieve is :-

Sheet 2 A1 = Sheet 1 A1
Sheet 2 A2 = Sheet 1 A22
Sheet 2 A3 = Sheet 1 A43

If anyone can help me out with this, I'd be most grateful.

Thanks,

M.

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 upper-left 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. Right-click 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.





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?


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




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?


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.





Hi,

I hope someone can help me.

I'm trying to write a formula where one value is incremented automatically when clicking and dragging, while the other values don't change.

For example:

A$1/(A$2-1)
A$1/(A$2-2)
A$1/(A$2-3)
etc.

Cheers,

Bill

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.