Auto Increment Worksheet While Copying
(Alternate Topic Results)
How do make an auto increment of worksheet while copying?
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
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
Hi guys, working my way through a large worksheet of figures which I need to sum according to date, and then calculate the amount of each date's figures with tax on top.
Autosum automatically places the sum of a column underneath the bottommost cell. Is there a way to put it at the side of the bottommost cell? Up to now I've been inserting a blank row beneath, then autosumming then dragging that cell to the right. Or else I can select the cells and type the sum in manually but it would be nice to tell Excel where I want the autosum result to be, without having to manually type in =sum(h29:h31) over and over.
Also, for my tax calculation I've been Ctr-dragging the formula downwards to apply it to the subsequent amounts. It retains the value of the earlier amount until I double-click on it to force a re-calculation. I'm sure I've had other formulas automatically update when I ctr-drag them so why not this one? =K150*(1+12.5%)
Thanks in advance for advice,
Hi, I'm new to this forum. Relatively good at Excel, but am always game to learn new formulas/VBA code.
I have a workbook where I have a button to automatically create a new worksheet. (ex. Report 1, Report 2, Report 3, etc...). I would like to have a Summary worksheet that automatically grabs the data from the existing and newly created worksheets (Report 1, Report 3, Report 3...etc) On the Summary worksheet, I would like to reference the exact same cell range from Report 1, Report 2, Report 3 etc...
However, I am unable to increment the values of the referenced worksheet on my Summary Worksheet. I'm not sure how to change the numerical value inside the single-quotes which refer to the worksheet Report 1, Report 2, etc. (ie 'Report 1'!A$89 ...) I would like to increment the 1 to a 2 and then to a 3 as each new worksheet is created and the Summary puts the data in the next available line, resulting in a Summary Report of Report 1 to Report N.
Hopefully that makes sense. If anyone has an idea, I would appreciate some guidance.
Thanks In Advance
This sounds simple to do and I have done it before. Copying a cell from one worksheet to another with the same workbook.
I highlight the source cell to copy and when I click to go to the other worksheet, it disappears. In other words, there is nothing to paste. Why would that happen? How to fix it?
Hi, I am using lookup function to find the last entry in a range on another worksheet. But the array table consists of 9 rows.
I want to find out a way to increment the array values by 9 instead of 1 as I am copying down on the current worksheet. I am looking for a solution that uses existing excel functions as a VBA would slow down the sheet and is irritating when the buttons pop up.
The following is the code I have entered:
So I am looking to change 36 to 45 and 43 to 52 when I copy. And incrementing by 9 throughout.
By the way I'm using 2007 version.
Thanks for any thoughts,
Is there a way of drag copying the formula below to the right so that the last cell reference will increment by one each time. The last cell reference is $F$30. When I drag the formula to the right to copy it, I want $F$30 to have changed to $F$31.
I have 600 columns and don't really want to have to manually adjust the formula.
I need to create a list of numbers like this by copying and dragging the formula from the first cell:
Anyone know how to do it using worksheet function. I've tried using mod() function and index function but couldn't get it done.
I need help copying a row from one worksheet to another using a formula. I manage a sales team of 22 salespeople and would like to have a separate worksheet for each salesperson. Is there a way for me to use a formula to locate all sales for each salesperson and copy the entire row of the main worksheet to their individual worksheet? There are 8 columns in this worksheet. The column I would like to use to identify the row(s) I would to like copy just contains the salesman name?
I have uploaded a .txt example of the format of the worksheet.
I have a formula that returns a string using the concatenate function. Now I want to copy the string to another cell without copying the formula. The string will become a record in a CSV file. I cannot figure out how to make what shows on the screen a label cell instead of a formula cell.
I use excel 2003. I want to be able to take a number in a cell in one worksheet that is derived from a formula and copy the value (not the formula) into a new worksheet.
For instance, I input the number 100 into a1 and 200 into b1. I want the sum of a1 and b1 in the c1 cell - so in c1 I input the formula =a1+b1 - and the number 300 shows up as the value in cell c1.
I now want to copy the number in c1 into the f1 cell in a new worksheet. If I copy and paste - it is the formula that I am copying and pasting - so the number 300 does not go into f1 in the new worksheet.
I assume that there is a way to "lock" the number of 300 (which appears in c1 of the first worksheet) so that I can copy that number (as opposed to the formula) from one worksheet to another.
How do I do this?
I put this question using a simple example. When I get the answer, I assume that I can apply it to a section of a worksheet that has many cells whose value is derived from formulas relating to numbers outside the section.
This is my first post here. Thanks for listening.
I normally have no problems with V-lookups but this time I am not getting the result I want.
I have a workbook with 2 sheets in it. Both sheets have over 20 rows. I have used a v-look up to grab some data from one sheet and put it in the other.
Here's the problem. I finished the v-lookup formula and the first one worked fine.
I then tried to use the auto-fill to drag the formula copying it all the way down to the bottom of my sheet. It copied the formula but with this result:
=VLOOKUP(A4,'[Combined List of Stock with landed costs.xls]Combined Land Cost'!#REF!,8,FALSE)
Since it's not referencing my table array, I get "#REF!" in my cells.
I don't understand why its not copying my table array references.
This is the formula from the first cell which works fine. It's when I try to copy it to all my cells I get the above result:
=VLOOKUP(A3,'[Combined List of Stock with landed costs.xls]Combined Land Cost'!1:65536,8,FALSE)
Please help, it's driving me mad!
This is something that has perplexed me for [literally] years. It used to
happen with e2k, and is now happening with e2k3.
When I have a value in a cell, be it a month or some number, SOMETIMES when I
try to drag it to "copy" it, instead it'll increment itself. "Jan" will become
"Feb", then "Mar", etc. Likewise the number in the dragged cell will increment.
BUT...this only happens SOMETIMES. Other times, the value, whatever it is,
REMAINS THE SAME.
Of course, THIS time, I'm wanting it to increment, and it won't. (As opposed to
my usual not wanting it to and it does! )
Can someone enlighten me as to what it is I'm doing, not doing, should be doing,
Thanks in advance,
I am a total newbie with Excel, and I just need it for basic data entry.
I don't even really know the terminology for what I am trying to do, so it is making it hard to find the answer...here it is
When I enter a number into a cell, and then drag down in the column to auto fill the cells below, the number increases. I want to be able to drag without the number increasing in increment.
Where and how do I do this?
Thank-you in advance for your help!!
My Excel auto-fill is stuck on copying cells instead of applying a formula. I have clicked the Auto-Fill Options Box and chose Fill Without Formatting but it reverts back to copying the cell values instead of using the formulas I want for the rest of cells... What can I do to fix the problem?
Whilst grappling with percentages, I have tried to copy a formula from say cell A1 down to A20. In cell A5 I have the figure of 30%, however when I copy cell A1 down to A20 the formula starts off ok with A5, but then it decides to increment A5, A6, A7 A8 and of course those cells are blank and my formula does not work. THen I am having to go back into it and amend each cell from A6, A7, A8 etc to all say A5, why is that?
Let's say I got a result of 216 in cell A1. I want to use that same result in
A1 to another calculation without copying the formula. How do I do it?
Big problem for me ........hopefully small problem for you....
I have numbers displayed in a column on one worksheet and I want to display them in a row on another worksheet.
The only way I have been able to do it is to individually set up each cell which is very time consuming. I've entered a few cells and then tried dragging the sequence but it doesn't work properly. As i drag across the row of the 1st sheet ( where I want to display the info ) the cell references increment across the row from the 2nd sheet ( which contains the info I want in columns ) rather than down the column.
Does this make sense ?
Can anyone help ?
Is there a way of copying a formula down a column or across a row without using autofill and dragging down the sheet?
I would like to auto increment the cell A1 after every print.
Also if possible to have the luxury of entering a value in B1 for print quantity.
A1 should increment before printing the next print.
I am using the A1 as a serial number generator inside the form.
I am new to this. Only copied one macro before this.
My very first post here on MrExcel. Thanks in advance for helping.
Need help with the following multi-sheet formula reference. When copying across columns, I would like to increment formula reference by rows. So when I type my first formula in cell AJ4 and drag it across to cell BH4, I need to have the following formula in those cells:
For Row 4, Columns AJ through BH (AJ4 - BH4), I need the following:
Cell AJ4 =(H4*(1-Sheet1!$BM7)*Sheet2!$V5)+(H4*Sheet1!$BM7*Sheet2!$AG5)
For Row 5, Columns AJ through BH (AJ5 - BH5), I need the following:
Cell AJ5 =(H5*(1-Sheet1!$BM7)*Sheet2!$V5)+(H5*Sheet1!$BM7*Sheet2!$AG5)
and so on and so forth...till row 36.
Or alternatively, when I drag cells AJ4 to BH4, down till 36th row (AJ36 to BH36), I need those formula to automatically update/increment.
septhemis / DP
I am performing a vlookup in a specific cell (i.e. B2) and I need to copy that formula down into the rest of the sheet. Normally I would copy it and do a paste special: formula and this would work. For some reason, it is not copying the formula to reference the new cell for the formula. It is literally copying that exact formula and pasting it down; giving me the same value in each cell.
I have never had a problem with this before, and I was wondering if it might have something to do with formatting in the workbook or something of that nature. Any help would be appreciated.
I have worksheets named 1 to 100. In a different worksheet "Summary" I have a summary of data of those worksheets. I want to change the worksheet number automatically in a row by dragging.
For example, in worksheet "Summary"
Cell A1 = Refers to Cell B2 of worksheet "1"
Cell A2 = Refers to Cell B2 of Worksheet "2" and so on......
I want to do this by dragging Cell A1 to A100.
Is there any way to do it?
I have the following lookup formula in cell A5 :-
When I drag the formula down, the row index number 37 does not change, so I have to manually change it
Is there a way of dragging the formula down so the row index number changes automatically
This would save a lot of time as I have 300 rows of data
EDIT: attention, my thread is misleading, see post #15
How can I auto-fill absolute references (as exemplified below)?
Dragging the handle and cell copying doesn't work but creates
PS: I hope there is an easier solution than diving into INDIRECT( ), ROW( ), OFFSET( ) etc., because I read http://www.excelforum.com/excel-gene...-possible.html and that's way beyond my IQ.
When I try to drag a control from the Forms bar onto a worksheet the control
does follow the mouse, although it does respond to the mouse click. Design
mode does not make any difference. The result is I can't add controls to my
worksheets, except by copying them from other projects.