Auto Increment Worksheet While Copying
(Alternate Topic Results)

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.

=IF(AND($B30"",$A30>=M$25),$F30-$F$30,"--")

I have 600 columns and don't really want to have to manually adjust the formula.

Dan

Hi guys.

I need to create a list of numbers like this by copying and dragging the formula from the first cell:

1
1
2
2
3
3

Anyone know how to do it using worksheet function. I've tried using mod() function and index function but couldn't get it done.

Thank you.

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.

Thankyou

Hi,

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!

Thanks All

A

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,
WHATEVER?!?!

Thanks in advance,

Tom

Hi

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?

Thanks,

Nick

Hi

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?

Guys,

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 ?

Hi folks,

Is there a way of copying a formula down a column or across a row without using autofill and dragging down the sheet?

Many thanks,

Danny

Hello,

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.

Thank you,
Harry

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)
Cell AK4=(I4*(1-Sheet1!$BM8)*Sheet2!$V6)+(I4*Sheet1!$BM8*Sheet2!$AG6)
Cell AL4=(J4*(1-Sheet1!$BM9)*Sheet2!$V7)+(J4*Sheet1!$BM9*Sheet2!$AG7)
...
Cell BH4=(AF4*(1-Sheet1!$BM31)*Sheet2!$V29)+(AF4*Sheet1!$BM31*Sheet2!$AG29)

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)
Cell AK5=(I5*(1-Sheet1!$BM8)*Sheet2!$V6)+(I5*Sheet1!$BM8*Sheet2!$AG6)
Cell AL5=(J5*(1-Sheet1!$BM9)*Sheet2!$V7)+(J5*Sheet1!$BM9*Sheet2!$AG7)
...
Cell BH5=(AF5*(1-Sheet1!$BM31)*Sheet2!$V29)+(AF5*Sheet1!$BM31*Sheet2!$AG29)

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.

Thanks again
septhemis / DP

Hello,

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

=HLOOKUP($A$3,Apr!$G$4:$AQ$300,37,0)

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)?

=$A$1
=$A$2
=$A$3
=$A$4
=$A$5
=$A$6

Dragging the handle and cell copying doesn't work but creates

=$A$1
=$A$1
=$A$1
=$A$1
=$A$1

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.