Email:      Pass:    Pass?
Subscribe for Free Excel tips & more!
E-mail:
Advertisements


Free Excel Forum

Dragging Formulas

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am worked with the formula, when I drag the formula to expand to the next row it automatically increases the undefined values: A4, D3, and D589.

=(VLOOKUP(A4,'Raw Data'!A:D,4,FALSE)/SUM('Raw Data'!D3:D589)*100)

I am working with roughly 4000 lines of data. In each line I need the first cell value ( A4) to increase as I drag the formula down, which is does automatically.

But in the next part of the larger formula /SUM('Raw Data'!D3:D589)*100) I need D3:D589 to remain the same through out the entire column.

Is their any way to lock the second part of the formula so it does not increase as I drag the formula down?


Similar Excel Video Tutorials

Helpful Excel Macros

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
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
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
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

Similar Topics







All,

Here is my formula that works so far...

=IF(Q589=0,"",IF(F589="","",SUM(D589/R589/1000)))

BUT, what i need it to do also is add another IF statement that read:

IF J589 = "YELLOW" then for it to be a blank cell, but if it doesnt say "YELLOW" then =IF(Q589=0,"",IF(F589="","",SUM(D589/R589/1000)))


How can i make this happen, so it looks in two cells and shows 2 different things "blank" and "0" but if it shows neither then do the formula...


Thanks All, look forward to your reply.

Regards,


I have a question on formulas and dragging formulas to automatically populate cells with the same formula.

I'm using Excel 2003 on a Windows XP system.

I have a formula in one worksheet(tab) that references a cell in another worksheet. I would like to drag the formula down in the first worksheet so that it automatically populates the cells below or next to it with the same formula. Traditionally, Excel will just increase all the references cells by one cell. So, for instance if your formula is "=100+A1", when you drag it down the formula will become "=100+A2". What happens if you don't want it to always increase by "1"? What if you want it to skip cells or rows, so that your formulas become "=100+A1", "=100+A4", "100+A7"? In this example, the formulas increase by 3 each time.

And an even more advanced question is, what if you wan the formulas to swich sheets? For example, "=100+'Shee1"!A1", "=100+'Sheet2'!A2", and so on?

Is there a way to do this?

Thanks!


I am hoping this will be an easy one for you Excel experts. I have a simple formula that works and does everything that it is supposed to and I want to drag it down and have the VLOOKUP reference increase as I do. I have about 100 rows and and 11 instances of this formula in each row and within each formula are 3 numbers I am currently changing manually. Is there a way to have the example below increase as I copy and drag the rows down? Any assistance would be greatly appreciated.

I want the Copier1, Imaging1, Computer1 reference in the VLOOKUP to increase as I drag the rows down.

Eaxample

ROW1
=IF($A$2="Copier",(VLOOKUP("Copier1",Data,5,FALSE)),IF($A$2="Imaging",(VLOOKUP("Imaging1",Data,5,FALSE)),IF($A$2="Computer",(VLOOKUP("Computer1",Data,5,FALSE)),"")))

ROW2
=IF($A$2="Copier",(VLOOKUP("Copier2",Data,5,FALSE)),IF($A$2="Imaging",(VLOOKUP("Imaging2",Data,5,FALSE)),IF($A$2="Computer",(VLOOKUP("Computer2",Data,5,FALSE)),"")))


Hi

If I have a formula in cell A1 = B$2.

Now I want to select cell A1 and drag it down. So that cell A2 = C$2, and A3 = D$2. (I.e. the column increases by 1.)

If I use the drag feature it just repeats the B$2 when i want the B to increase by one with each cell I go down.

Any Ideas?


Hi once again

I actually have been facing this issue for quite some time and hav'nt taken the time
to get my question out there on this. So here it is, Hope there's a solution...

Is it possible, or how do you, drag a particular formula and have the increments
focused on ONE data you want accumulated (Increased)? I know this sounds
confusing, but it is hard to explain, so here an example of what I'm asking:

I have the need to drag down a formula 1000 rows, but I only want the
"Sheet#" (worksheets) incremented as I drag, but I want the cell "B2" to remain
constant... The formula looks like this: =Sheet1!B2 ... I want to drag this
formula down so it resembles this:

=Sheet1!B2
=Sheet2!B2
=Sheet3!B2

on and on....

When I drag I get increments in the cell B2 only:

=Sheet1:B2
=Sheet1:B3
=Sheet1:B4

etc...

is there a way to tell the drag to increase the sheets only without incrementing
the cell B2?

This has boggled me for sometime now, I hope it's possible

Thank You For Any Help On This...


I wasn't quite sure how to word this question for a google search, so hopefully someone here can help me.

Is there a way to drag sideways to fill cells with a formula, but instead of the cell reference increasing sideways, it increases vertically?

For example, if I have =B1+A1 in a cell, can I drag it across to fill the adjacent cell, but have the formula increase to =C1+A2, instead of C1+B1??


I am trying to create an excel sheet with a formula for dates on it. I have it all set up OK if you enter a date directly into the cell. I am looking to be able to drag cells into the cell the formula is using, then be able to drag out of that cell again, without the formula changing. Is this possible?

So, for example, the formula is,
"=DAYS360(G4,E7)" As it is now, if I were to drag a date into cell E7, it would work fine. But if I drag that cell, to F7, then the formula automatically becomes "=DAYS360(G4,F7)". Is there a way to lock the formula in as, "=DAYS360(G4,E7)"

Thanks for the help!
You can download the file for more information at, www.sapphireprint.com/Financial.xls


I have a formula that needs to be entered 400 times and was looking for an easier way to do it than having to type it in every time. I was hoping there was a formula that I could just drag down.

In cell AA1 i have the formula =SUM(C12:C14)
In cell AA2 I have the formula =SUM(C27:C29)
In cell AA3 I have the formula =SUM(C42:C44)

When I try to drag the formulas down, cell AA4 populates with =SUM(C15:C17), but I need IT to increase from the previous cell by 15 rows. It should say =SUM(C57:C59)... etc. for 400 rows.

Any suggestions?


Hi

I have a list of data and I am using a vlookup forlmula to auto populate 4 fields. However when I drag the formula down the sheet, it carries exactly the same data as for the first row. Even though it shows that the cell number has changed to the next row.

I have vlookup(G4,CALLDRIVER,13,FALSE) which works in row 4 - shows: 11:37am. When I drag the formula to row 5, the formula shows = vlookup(G5,CALLDRIVER,13, FALSE) But it shows the data from row 4 - shows 11:37.

Can anybody assist please?


Hi,

I hope someone can help.

I Have a number of identical worksheets all with mulitple formulas contained within and i am looking to create a summary sheet i have used the $ sign to stop the rows increasing as i drag and the formula but i cant get the sheets to increase by an increment of 1 i.e.

formula in A1: ='Sheet1'$B$3 when i drag to the right i want the formula to show ='Sheet2'$B$3 constantly increasing the sheet only for every cell right i drag.

Is this even possible, i've been going round in circles for hours!!

Many thanks in advance

Smarty


Hello friends,

i have a data s in two columns A and B the no of the rows that contains the data in that column are dynamic, everyday it will change. i have a formula in column c. whenever my row of column A increases or decreases i need to either drag the formula down or delete the formula in the cell.
i need a macro or a formula so that when there i s a data present in column A the formula in C is automatically copied from above or if the data is not present in A then it will delete the formulas in C.

Please Help. thanks.


Okay, here is what is happening.

In cell C2 i have the formula =IF(A2>"",1,"")
In cell C3 i have the formula =IF(A3>"",2,"")
In cell C4 i have the formula =IF(A4>"",3,"")

I wanted to drag these formulas all the way down to cell C250 or so, but when i drag i end up with:

C5 has the formula =IF(A5>"",1,"")
C6 has the formula =IF(A6>"",2,"")

As you see it doesnt copy the formula correctly, as they should not both say "1" and "2" at the end, but "4" and "5".

Why is the formula dragging doing this, or am i doing something wrong? I selected C2:C4 when i did the drag.


I need to submit my report in excel and I am having a hard time using the mixed referrence function...

This is an example
1. I have data A1, A2, A3....down a column.
2. I have one value on C2 ( constant value)
3. the formula is a very simple multiplication formula. I want to multiply every single value of column A to C2. But it needs to be in across a row that starts in D4. So,formula needs to be in (D4, E4, F4) there are other datas in the worksheet
4. So when I input =$A1*$C$2 in D4 it calculates it fine but when i try to drag the formula across the row so [E4=$A2*$C$2, F4=$A3*$C$2...] it repeates the same fomula as D4 and does not adjust for the increase in column number.

How can I fix it so when I drag the formula across a row it can still account for the mixed reference?

Please help and thank you.


I am having a problem dragging down a formula. Its easier for me to give an
example rather than describe the problem. Example: The formula im trying to
drag down is: c41+c87+c137+c184. If i drag this down i will get
c42+c88+c138+c185 in the next row but the formula i result i would like for
the drag down is d41+d87+d137+d184. Any thoughts on how i can correct this?



Hi,
When I drag formula down, it will copy the value of the cell, even through
the values of the formula get updated.
What I mean, if I were to drag the formula in 1A down to 3A, it would result
in:
Comumn A Column B Column C
Row 1 3 (formula is 1B+1C) 1 2
Row 2 3 (formula is 2B+2C) 3 4
Row 3 3 (formula is 3B+3C) 5 6

So the formulas are correct, yet the valus are wrong. It will work if I
clear the contents and retype them, or if I right click on the first cell,
drag and click "Fill Formats". This just started yesterday, on two different
worksheets (no viruses). Anyone???



I have a worksheet with over 8,000 lines of data. Each line represents days. At the end of each row is an amount. What I am doing is comparing the amount for one day against the amount the previous day so I enter a formula at the end of each row to compare the two amounts.

Once I setup the formula for row one, I click on the formula and drag it all the way down, and Excel automatically fills in the formula for me.

The problem is that I prefer not to have to drag it all the way down 8,000 rows. Since there are so many rows it takes a while for me to get all the way down there. Is there some way to do this without the click and drag?


I am using the following formula

=VLOOKUP(B2,Sheet2!$A1:$B$6,2,FALSE)

I need to drag the formula box down to cover a couple thousand cells, but when I do the (Sheet2!$A1:$B$6) part increases as well. Is there something I can put in this to make the B2 value increase (to B3, B4, etc..) but leave my A1:B6 the same as I extend the forumla to my other cells


i have some data in C3:I6 and C9:I12

then i use formula in cell C14 IF(C3=C9,TRUE,FALSE)
and drag to I14 then to I17, this will apply formula C14:I17

is there any quick way to do this without dragging??? coz i can't drag 1000's of row and cols...

Hi All,

I am trying to drag a vlookup formula across several cells and eventially over thousands. However i cant seem to get a consistant sequential formula the way i want it.

I have attached my document to this post.

for example in cell AG i have this

=VLOOKUP(N1,INDIRECT(A2),2,FALSE)*N2

as i drag this across to cell AH the INDIRECT(A2) changes, i want it to remain (A2). However when i want to drag the formula down to the next cell i want it to change to INDIRECT(A3).. and so forth.

Can someone help me to achieve this please? or give me some better ideas.


thanks

Aboo


If I am creating a vlookup forumulae, and I want to return values from several columns, I have been manually changing the column it's returning the value from when I drag the formula across the columns. This is ok for a few columns but a pain for lots.

eg =VLOOKUP($D2,$AB:$AJ,1,FALSE), dragged across columns, and then manually edited in each column to read
=VLOOKUP($D2,$AB:$AJ,1,FALSE), =VLOOKUP($D2,$AB:$AJ,2,FALSE), =VLOOKUP($D2,$AB:$AJ,3,FALSE), =VLOOKUP($D2,$AB:$AJ,4,FALSE)
etc

The value doesn't increase when you drag it.

is there a short cut for doing this automatically?


I have some data in a spreadsheet that I'm trying to manipulate and I'm not sure if what I want to do is possible.

I have data in B2: D2 and I have data in E2:E4. So one set of data goes across the columns and the other goes down the rows. I want to subtract these two sets of data. I click on a new cell (B21) and put =B2-E2. That works fine. However when I drag the formula to populate the cells is where my problem starts.

I want to drag the formula across the columns to populate B21:P21. The problem is that when I drag across the columns B2 increments to C2, D2 etc...I would like it to instead increment to B3, B4 etc...

Is is possible to drag a formula in one direction (whether across the page or down the page) and have the cells used in the formula increment in the opposite direction?

I hope that was explained well enough for someone to figure out what I mean. I attached a file as well so you might be able to see what I'm talking about. Thanks


I have values in columns "A1:Z1", on shhet1 and on another sheet I want to refer to these column values in rows for e.g I want column A1 value in cell B1 and column B1 value in B2 and so on. I can't drag the formula down, I have to give column name seprately everytime I drag the formula? Is there a way we can achieve this by dragging the formula down?


I'm trying to have a cell show exactly the same data as a cell in another spreadsheet. I accomplished this by throwing in = and the clicking on the cell in question. Works great. I want to be able to drag the formula

='[StaffingReport.xlsm]Staff & PH Data'!$A$35

have it populate the next cell with the next cell from the other spreadsheet so that the formula would automatically read:

='[StaffingReport.xlsm]Staff & PH Data'!$B$35

Only when I drag, it's dragging the exact same formula instead of going up one column.

Does that make sense?


Can I use autofill (drag across) to increase the column count?

For example:

On row U, I have the following formula

=IFERROR(VLOOKUP($A10,Online!$A$1:$J$153,9,FALSE),IFERROR(VLOOKUP($A10,Allocations!$A$1:$J$153,9,FALSE),IFERROR(VLOOKUP( $A10,'Corporate Exp'!$A$1:$J$154,9,FALSE),0)))

and I want to increase row X by 1 and so on (I have about 24 columns)

=IFERROR(VLOOKUP($A9,Online!$A$1:$J$153,10,FALSE),IFERROR(VLOOKUP($A9,Allocations!$A$1:$J$153,10,FALSE),IFERROR(VLOOKUP( $A9,'Corporate Exp'!$A$1:$J$154,10,FALSE),0)))

I will also like to expand the data - expand to K233 as the data increase.



Thanks in advance for you help.


I am in the process of creating a fully automated Excel file for an accounting report that we process monthly (with all the macro bells and whistles). But, I have run into a problem. I have two worksheets in my workbook. One contains the data that feeds in from our ledger, the other is the formal report that we print (and where I have my formulas set up). The formulas are quite simple (add & subtract) and are linked to the data from the corresponding month on the data ledger tab. That's the set up, here's the problem:

The formula tab is set to drag down one row at a time, while the data tab pulls in three rows for each month (which may not be removed). I need to make it so that when I drag my formula down one row, it captures the corresponding data three rows down. For example: month of July-09 is in row 1 on the formula tab, and row 3 on the data tab. My formula is set to grab the data from row 3 in the data tab. Now the month of August-09 gets dragged down to row 2 on formula tab, and row 6 is populated on the data tab. My formula will automatically grab the data from row 4, because that is the next row in the series. Make sense?

How can I make it so that when I drag my formula down by one row, the formula knows to grab the data three rows down from the next worksheet???

Thank you!!!