YTLE#68: 3 Ways To Use Fill Handle for Copying

My Excel has suddenly taken to ignoring the usual rules for the fill handle,
and now will only copy the values of the first cell when dragged, instead of
copying 'all' including formulas. I have explored all the possible settings,
and they seem to be as they should be. I have opened a new sheet in the
faulty workbook, and this exhibits the same aberrant behaviour.

If I open a completely new workbook under the same Excel installation, it
behaves normally, so the problem is confined to one particular workbook with
five worksheets. This has behaved normally from day one until today. No
changes have been made to this workbook, at least, not knowingly.

I have 'repaired' the Office installation, then reinstalled Excel on its
own, and finally reinstalled Office. Nothing makes any difference.
Presumably there is some kind of registry setting that has gone belly up,
but I don't know the registry well enough to find it.

Can anyone help please?

David Kelsey

Good Afternoon,

I have a worksheet 17,000+ rows that I'd like to sort by column A and by column B but not at the same time. I am using the sort + count and match to basically do a filter, but this is faster. I'd like to have the data sorted two different ways, but I don't want to have to sort on the fly as it does take a bit of time. Is the following the best way to do it?


1. On load sort by column A.
2. Copy worksheet and short by column B
3. Provide a refresh button that when a user adds field data they need to hit the refresh button and this button then calls number 1 and number 2.

Thanks in advance.

Hello All,

I'm trying to use the Auto-Fill handle to copy a formula down a column and it's not filling the formula down each row incrementally. Instead, it's copying the EXACT formula using row data from the original location of the formula.

In the example below, the formula I used for the first row with "Blue" (highlighted in green) says "=Amount x total" or..

Code:

=B3*GETPIVOTDATA("COLOR",$A$1,"COLOR","Blue","Amount",100)

so, the formula for the "Blue" row is correct. But when trying to use the fill handle to copy the formula to the "Dark Blue" row (highlighted in red), the formula is still basing its data on the "Blue" row and NOT the "Dark Blue" row as it should. Here's the formula it copied down to the "Dark Blue" row..

Code:

=B4*GETPIVOTDATA("COLOR",$A$1,"COLOR","Blue","Amount",100)

However, if I do the "Dark Green" row manually by putting in the formula

Code:

=B5*GETPIVOTDATA("COLOR",$A$1,"COLOR","Dark Green","Amount",100)

The formula works correctly (without using the auto-fill).

Does anyone know what's wrong here?


COLOR Amount Total Blue $100.00 1 $100.00 Dark Blue $100.00 3 $100.00 Dark Green $100.00 3 $300.00 Dark Red $100.00 3 Green $100.00 1 Light Blue $100.00 2 Light Green $100.00 2 Light Red $100.00 2 Red $100.00 1
Thanks

Using Excel 2007
column A has text from row 1 down to row 123 - no blank rows
column B - I want to enter text in B1 and use the fill handle to fill cells B2:B123 and it doesn't fill

When I double click on the small black square to auto fill a formula it stops
short. It should go to the last cell with data in the preceding column but
it doesn't. If I double click again it still stops short.

http://www.ozgrid.com/forum/showthread.php?t=158948


In the above thread, jindon has

VB:

Sub Macro3() 
    With Sheets("sheet1").Range("f7") 
        .Value = 5 
        .Copy .Offset(6) 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Am I right to see this as "hard" coded?

If it is, could this be changed to

VB:

Sub Macro3() 
    With Sheets("sheet1").Range("f7, f12") 
        .Value = 5 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I know that this is more or less irrelevant and I am certainly not questioning jindons expertise.
Is there an advantage of one over the other?

Just to ease my mind and to see if my thinking is right.

Thanks and regards

John

How can I fill downwards the following formula?

=(SUM(B$2:B13)*0.05)*(J2/12)
=(SUM(B$2:B12)*0.05)*(J3/12)
=(SUM(B$2:B11)*0.05)*(J4/12)

Thanks

Hi guys. I need some help with copying cells using fill handle.

I want to copy cells with incremention using fill handle.

A1: ='PO#1'!K2

then I want A2 to be ='PO#2'!K2 instead of ='PO#1'!K3

can somebody tell me how to do it??

I don't need to use fill handle if there are any simpler/easier/quicker way to copy cells like these.

Thanks!

Hi Can any one help me please.
I am trying to get numbers in a column to follow each other by dragging down instead of having to type them all in does anyone know haow to do this??? example below might help if i haven't explained myself very well - thanks in anticipation of any help...

1
2
3
4
5
6
7
etc
etc

Good afternoon!

Sorry if I missed a topic where this question is addressed.

I have created a VLOOKUP formula to pull the address for a customer from a different sheet within the same workbook. The formula looks like this:

=VLOOKUP(A2, All!A2:AB350, 22, FALSE)

When I use the fill handle to add this formula to the cells below, it should change the first value (A2 should change to A3), but All!A2 should stay the same -- if it changes to All!A3 then by the bottom of the sheet the formula will only be looking in A350:AB350 and it will not be able to find the information I need.

Does anybody know how I could customize the fill handle so that it only changes the first value of the VLOOKUP formula? Alternately, is there a different way I can do this? I can probably create a macro, but that would take a long time!!

Thank you for your help!