Change The Column When Dragging?

Apr 6, 2014

How can I have it so the column (letter) changes in succession rather than the row (number)? Here is my formula :

=SUMPRODUCT(('Web Queries Reference'!A$2:A$600<>-1)*('Web Queries Reference'!A$2:A$600))-0.01

I would like it to be this once I drag the cell formula down to the next row :

=SUMPRODUCT(('Web Queries Reference'!B$2:B$600<>-1)*('Web Queries Reference'!B$2:B$600))-0.01

View 14 Replies


ADVERTISEMENT

Change Column Letter When Dragging Down

Oct 28, 2013

I have a formula in cell C2, =Summary!B2

I want the formula in cell C3 to be =Summary!C2

Then the formula in cell C4 to be =Summary!D2

etc etc.I need to do this for 43 rows so just want to drag the formula and the column number rather than the row number change.

View 3 Replies View Related

Change One Variable When Dragging Down A Column To Auto-complete?

Feb 25, 2013

How do you drag down a column to auto complete the values but do it so it only changes one variable.For example, I am dragging down =IF(ROW()<=$B$5,MOD(E2*B1,B2),"") down my column.

However, I only want the "E2" value to change, and I want B1 and B2 to stay that way. Whenever I drag down, it keeps changing all three values. Is there a way to make it just so that the E2 value is the only one that changes?

View 4 Replies View Related

Dragging Formulas; Values Of The Formula To Change And The Other Not To Change

Jan 29, 2010

I want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:

c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :

how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.

View 3 Replies View Related

How To Change Reference Cells Specific Amount When Dragging

Aug 14, 2014

So I'm dragging my formula down one cell and as expected the reference cell changes the same amount when I really want the reference to shift Down that column 11 cell so when I take my formula

=IF(ISERROR(LOOKUP(21474836472147483647,'Revenue Book'!L5:L11)),"",(LOOKUP(21474836472147483647,'Revenue Book'!L5:L11)))

when I drag it down one cell the reference should look like this

=IF(ISERROR(LOOKUP(21474836472147483647,'Revenue Book'!L16:L22)),"",(LOOKUP(21474836472147483647,'Revenue Book'!L16:L22)))

View 5 Replies View Related

Dragging Down Formulas To Last Row In Reference To Next Column

Apr 10, 2014

I would like to drag down the formula from the last cell in Column A in reference to the last row in column B.

E.g My last data in Column A104 (code to select the last cell and this can vary from time to time the report is updated)
Column B has data till B142. I want to drag A104(contains formulas) down to A142.

Here is what I have but Selection Autofill doesn't work.

Range("A2").End(xlToRight).Select
Range("A2").End(xlDown).Select
Selection.AutoFill Destination:=Range("A3:A" & Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillDefault

View 1 Replies View Related

Filling Down A Column Without Dragging Over Every Cell?

Nov 8, 2003

I have a spreadsheet that will eventually consist of tens of thousands of rows. Several columns contain formula's. Rather than having to "highlight" a cell and then "drag" all the way down the column over thousands of rows before finally using the "fill down" function I am needing a method to short cut this process?

Is there a function that allows you to, say, nominate a range of cells that you can fill down over? For example, starting from the last row with data entered into it down to say row 12500 i.e. D22:D12500.

View 8 Replies View Related

Inserting Column And Dragging Down Formulas

Jul 5, 2006

I have a long list of inventory parts and there can be additions or deletions at any time. Currently the inventory is determined using formulas from other worksheets and this uses formulas. If I have a user insert a row to create a part in inventory, is there a way to make the formulas automatically be created in the cells within the row?

View 4 Replies View Related

Excel 2010 :: Repeat Cell Data Based On Another Cell Column Dragging Down

Apr 24, 2014

Formula that will repeat a cell number as it drags down and as soon as the number changes. I am using helper column that shows the cell number. I need to drag this down about 1000 rows.

Excel 2010
A
B
C
1
Helper Column
Desired Result

[Code].....

View 6 Replies View Related

Excel 2007 :: Change All Matching Value Based On Number Change In Column?

Feb 19, 2014

I have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).

I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.

Demo1.xlsm

View 14 Replies View Related

Add Row If Value Value In Column Change And Paste Specific Value Depending On The Change

Mar 5, 2010

I have used the below code to insert a new row when the value in coulmn A change. I now need to evolve it so that the new row will contain a specific value depending on the changing value:

Before:
Column A Column B
one test
one test
two test
two test
three test
three test

After:

Column A Column B
one test
one test
Coz two............................

View 9 Replies View Related

Worksheet Function: Change C2 To Change To Activecell Column + Row 2

Jun 23, 2006

How would I add this formula as a worksheet function with VBA. I can't see INT, MOD or Year in VBA. Also want to change C2 to change to activecell column + row 2.

=INT(((C2-1461)- SUM(MOD( DATE(YEAR(C2-MOD(C2,7)+3),1,2)-1461,{1E+99,7})*{1,-1})+5)/7)

View 4 Replies View Related

Dragging Down Formula

Jul 14, 2009

i have a sheet with alot of data, its a contacts list. colum A is Company name, B contact name, C address, D postcode. One row = one contact. On the next sheet i want to have on column with each contact set out as if it wer on an envelope, so i did the formula:............

if i select all of this space and try to drap the formula down the next cell fills with =sheet1!A11......I want it to say =sheet1!A3. If i cant drag it down then I am going to have to manually write this formula 100000000000 times.....anyoning.

View 5 Replies View Related

Dragging A Formula With $

Dec 19, 2007

How Do You Drag This Formula Down A Column & Make It Progress And Still Have $'s

This:
=$a$1
=$a$2

Not This
=$a$1
=$a$1

I Dont Want To Have To Drag The Formula And Then Manually Put In The $'s

View 9 Replies View Related

Dragging Vertically

May 27, 2009

In row 3 I have values horizontally. (A3 to Z3)

i link C5 to A3.
If I drag it vertically it does not give the correct values.
Is it possible to drag it in a correct way?

I tried =INDEX($A$3:$X$3,ROWS($A$3:$A3))

View 9 Replies View Related

Dragging Rows

Oct 2, 2009

I have the following forumlas in consecutive Rows on Tab#2 of my file:
Row 1: =(Tab#1!B12 + Tab#1!B13)
Row 2: =(Tab#1!B24 + Tab#1!B25)
Row 3: =(Tab#1!B36 + Tab#1!B37)

Is there any way to drag this formula down the rows below it so that each time it continues this pattern. Just to be clear the pattern is that each time you drag down a row it goes 12 rows in the formula.

View 9 Replies View Related

Duplicate Line Without Dragging

Jun 23, 2014

In one sheet i have a table with 154450 lines from a software output converted in a table , in another sheet i made a table to 'sanitize' and make 'human-readable' the first one referring to some of the cells.

It works fine but, i have to drag the last line to repeat the formula and, as you can imagine, hard to think i will drag for 154450 lines ^^.

View 3 Replies View Related

Variable Effects Of Dragging

Dec 28, 2009

I have found an inconsistency in different PCs with respect to the method of producing incrementation when dragging a grab handle to an adjacent cell. In some PCs just dragging produces the effect, in others a key has to be held down during the drag. I'm sure there are detailed instructions about such a setting, but I have been unable to find them.

View 9 Replies View Related

Lock Dragging Of Cells

Oct 22, 2008

Is there a way to protect a sheet from a user dragging a cell?

View 6 Replies View Related

Skipping Certain Cells When Dragging Down

Sep 19, 2013

I'm looking to easily drag the sum of certain cells in a different column BUT keeping a specific range, it's hard to explain so i'll show an example...

A1
A2
A3
A4
A5
A6
A7
A8
B1=SUM(A1:A4)
B2=SUM(A4:A7)
B3=SUM(A8:A11)

And so on...

Is there any way I can do this by dragging down the cell formula from B1 and it remembering the range of 4, so I don't have to manually select each range...?

View 3 Replies View Related

Dragging The Formula To The End Of The Report

Jan 31, 2007

Here's my Formula:

Range("H2:S2").Select
Selection.AutoFill Destination:=Range("H2:S2702"), Type:=xlFillDefault
Range("H2:S2702").Select

This is dragging the formula to the end of the report. My dilemma is that this was captured with the macro recorder. Is there a way to change "S2702" with whatever row Column S ends with? Meaning this formula will be used on different sized reports so the numbers will vary. I just need a direct statement to compensate however large the report is.

View 9 Replies View Related

Copying A Formula By Dragging

Mar 3, 2007

I have obviously hit a combination of keys that has turned this function off. I can no longer drag and fill cells with a formula, it will only copy the original text/value into adjacent cells. How can I fix this??

View 9 Replies View Related

Auto Cell Dragging.

May 9, 2006

I have an excel workbook with two sheets:
1. Data.
2. TR.

Once the choices are made (dropdown lists) and the button is clicked, TR sheet is filled. I need a macro that would drag the cells of E4,J4,O4,T4. To the cell beneath them, once data is filled in either C or D, H or I, M or N, R or S. The dragging of the cells will display the results (column E,J,O,T), thus, totals will appear in the CONS sheet.

View 2 Replies View Related

Increment By X When Dragging Cells Down

Jan 5, 2008

I need to check the result of the difference between 2 cells in the same column. But It needs to be done with an increment of 2 e.g. A2-A1 then A4-A3 and so on.

View 5 Replies View Related

How To Skip Columns When Dragging Formulas

Dec 3, 2013

I have 2 sheets in my excel document. The first sheet (sheet1) has student data (grade they got on a test) from C5:C35 all in 1 column. On the 'second' sheet (sheet3), I have made a profile for each student (there are 30 of them, spread out 1 per printable page). I want to take Sheet1!C5 result and put it in sheet3!D7. The formula in sheet3!D7 would just be =sheet1!C5. I want to 'drag' that formula to L7, then to T7 then to AB7 etc etc (they are all 8 columns apart), obviously not affecting any of the cells between them.

There are 12 pieces of data in sheet1 that I want to auto update in sheet3, for 30 students, so copy pasting, and adjusting the formula would take a long time.

View 7 Replies View Related

Dragging Down Reference To Another Workbook Cell

Jan 23, 2014

I have a master workbook that I reference data from hundreds of excels on.

I am trying to reference the cell "BU7" that has the workbook name (NT-13-001) in it, to reference sheet name "Purchase Order" at cell "H13".

Next line I would like to reference the cell "BU8" that has the workbook name (NT-13-002) in it, to reference sheet name "Purchase Order" at cell "H13".

The computer path is K:FRG TOOL REQ2013NT FormsNT-13-001

This is the formula I have tried... I have also attempted the INDIRECT function & Hyperlink GO TO.

=IFERROR('K:FRG TOOL REQ2013NT Forms["&DU7&".xlsx]Purchase Order'!$H$13,"")

View 1 Replies View Related

Dragging Down Formulas With Inconsistent Cells

Jun 11, 2008

How figure out how to drag down a formula that looks like this for the first three cells (C3:C5)?

=(SUMPRODUCT(Polls!D2:D5,Polls!B2:B5))/(SUM(Polls!B2:B5))
=(SUMPRODUCT(Polls!D8:D11,Polls!B8:B11))/(SUM(Polls!B8:B11))
=(SUMPRODUCT(Polls!D14:D17,Polls!B14:B17))/(SUM(Polls!B14:B17))
...etc. 

Is there a way to let Excel know that I'm adding six to each cell, rather than it automatically adding one?

View 11 Replies View Related

Excluding Specific Items When Dragging Down

Jun 1, 2009

Im using the following formula to update our prices in our stock list.

=VLOOKUP(C1,'Most Recent Prices 2009'!A:G,6,0)

Column C = Unique part number.

Problem:
I have a tab of 100 or so prices that I don't want to be updated. How can I exclude these pieces of equipment when I drag down the above formula. Assume that these pieces of equipment are in a tab called 'keep' and their unique part numbers are in Column A.

View 4 Replies View Related

Copying To Multiple Cells Without Dragging

Mar 14, 2013

Is there a way to copy a formula from Cell A1 down to A1500 withhout dragging the fill handle?

View 4 Replies View Related

Use VLookup And Skip 10 Rows When Dragging Down?

Sep 13, 2013

I have used VLookup function to reference some information between books. The formula used is:

=VLOOKUP('[Book1.xls]Sheet1'!$A15,'[Book1.xls]Sheet1'!$A$7:$BA$67,12,0)

When I drag down I want the formula to skip from $A15 to $A25 and so on. Is there a way to do this? Or am I better off using another formula?

What I am trying to achieve is:

Book2 Cell D54 = Book1 Cell L15
Book2 Cell D55 = Book1 Cell L25
Book2 Cell D56 = Book1 Cell L35

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved