Drag Down Formulas In VBA
Oct 22, 2012
I have a workbook with five different sheets in it.The first is an inputs sheet where I populate inputs to my file. The rest all contain formulas in a top row (ex: A2:G2 in the first sheet D4:M4 in the second sheet...etc). These formulas are calculating values based off my input sheet and need to be dragged down the same number of rows as is in the inputs sheet (if there are 125 rows in my input sheet I need it to drag down the formulas 125 times, so from A2:G2 to A126:G126 or D4:M4 to D129.
The main problem is that I never know how many rows will be on the input sheet so the piece of code would ideally count and then apply that count to all of the other tabs and drag down the appropriate amount of rows. It is a base file so I save a copy each time I open so the start rows will always be the same.
View 2 Replies
ADVERTISEMENT
Apr 11, 2009
I have data from F1 to AG1...I am unable to create a formulae & drag in excel in a column like
=F1
=G1
=H1.....If i drag after H1...it is reflecting as
=F4
=G4
=H4.
View 3 Replies
View Related
Mar 19, 2014
Is there a way to select cells with formulas for a top row and then fill down the formulas for these non adjacent, non-contiguous, etc. columns? I can paste a single formula, but not varying.
i.e. Cells A1, D1, and G1 have my formulas, and I want to identify that they have formulas in the first place and then drag down to let's say A10, D10, and G10. I will not know which columns contains cells before hand, and the given formulas are not fixed
View 9 Replies
View Related
Mar 13, 2014
I'm wanting to do is drag a formula down and it drop to the next cell rather than the same row number I'm on. For example I'm trying to concatenate a list of phrases whilst changing the main word. Here's an example of the excel sheet
Base Terms
Phrase
Result
car
red
van
blue
bus
red
blue
There is meant to be a space after the second red and blue enabling me to make (in order), red car, blue car, car red, car blue
How can I make it so I've done the relevant concatenate formulas for A2 with the B column and simply drag it down and Excel will switch from A2 to A3 and so on when I've dragged out the 4 formulas?
View 5 Replies
View Related
Jan 23, 2009
I have a workbook which contains 12 sheets, 1 for each month of the year. I also have over 100 sheets, 1 for each agent that covers their stats for each month of the year based on some of the cells from the monthly sheets.
I am trying to drag the formulas over but it will always just refer to January, instead of January and then next cell refers to the February tab, etc, etc.
View 9 Replies
View Related
May 19, 2014
On the 'Total' sheet I'm wanting subsequent sheets to fill in on the rows. Do I have to redo the formulas for each row because it will correlate to a different sheet? I only know how to drag the formula down, but that doesn't seem to work right!
View 11 Replies
View Related
Jul 9, 2014
I have a perfect formula
=IF(A2>1,A2/1.16,"")
It does exactly what I need , it does not calculate on empty rows , but it calculates on heading rows , how can I modify to avoid this.
See file attached , I don't want the value on b6 when I drag downwards.
07 09 value avoiding.xls
View 11 Replies
View Related
Jan 5, 2013
I need to disable cut and drag only in a workbook. The idea is to avoid formula reference errors when unlocked cells are cut and pasted or dragged. I can find many macros online but none that do only these two things.
View 2 Replies
View Related
Jun 23, 2014
I have a formula that I would like the column references to change as I drag the formula down. I would also like the row reference to stay the same.
I have attached my workbook for reference. I listed the value in red I would like to achieve
Book1.xlsx
View 2 Replies
View Related
Mar 14, 2014
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A1:C2)
If I simply drag the above formula down I end up with
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A2:C3)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A3:C4)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A4:C5)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A5:C6)
I am trying to drag the above formula so that it copies in the following manner:
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A3:C4)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A5:C6)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A7:C8)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A9:C10)
How can I achieve this result?
View 3 Replies
View Related
Jan 3, 2009
i need a macro that will simulate the little corner crosshair that you drag to fill in and increment formulas. i need to fill down on sheet 2, columns a thru m a number of rows equal to sheet 1,column b, count -1.
View 9 Replies
View Related
May 11, 2009
I am trying to link the cells up in a new workbook the problem is the data I need is setup in a way the formula needs to skip every other row.
For example:
View 2 Replies
View Related
Jun 24, 2009
is there any way where instead of selecting a cell one by one and run a macro, i can just drag the cell with the macro across other cells like how a formula runs?
View 5 Replies
View Related
Jan 14, 2010
How can I stop drag and drop across a row but you can drag and drop down a column.
View 14 Replies
View Related
Feb 18, 2010
I would like cell D2 (on Sheet1) to default to the value of "EACH", unless the value is found on Sheet2 (column C). If it is found on Sheet2, then I want it to match the unit beside the respective upc code (see Col Q on Sheet1, and Col A on Sheet2) . Then I would like to be able to drag down cell D2 and all cells on Sheet2 would match and any that do not match, would be filled with "Each". Desired results are shown in attached example file.
View 5 Replies
View Related
Jan 23, 2007
I have tried absolute and relative on the formula in A5 of this sample data below to get A6 to equal D2 and so forth down the sheet. Nothing works except typing in the formula for each cell....
View 9 Replies
View Related
Apr 23, 2007
i have a formula that needs to be dragged down 65,000 rows in excel is there a quicker way of do it than with the mouse?
View 3 Replies
View Related
Jun 3, 2008
When I drag a text file from my desktop into an existing Excel worksheet it automatically creates a new workbook. I'd like to know how to modify this such that I can drop the file into my existing worksheet (and not have a new workbook create) and then run some VBA macros on it.
View 3 Replies
View Related
Jun 20, 2014
Drag & Drop Example.xlsx
In the example attachment on the weekly tab, you will see I have daily totals and a sum for weekly. On the summary tab I have the columns going down with a link to the weekly totals.
I have over 5 years of data in this format and don't want to link each cell in one sheet to another. If I drag and drop, obviously the weekly doesn't come down correctly.
Is there another way as I don't want to have to do this for over 250 weekly totals (and counting) manually.
View 3 Replies
View Related
Dec 16, 2013
I have sheets with over 40000 rows. I grouped them in 60 and I need the average for every group on the 61st (which is blank). For example, when collapsed I have visible the following blank cells:
A61
A122
A183
A244
and so on.
What I want is to enter a formula in A61 for the average of A1:A60 then drag down and have the correct values in all the rest (average of A62:A121 in A122, etc.).
View 4 Replies
View Related
Jul 10, 2014
I am trying to drag a numerical value down (X4-X10) but I need it to repeat two rows in between and simultaneously keep the numbering in sequence only after the repeats 2 rows in between . So far, I have to manually enter or when I select cell X4 and X5 together ( having a value of 1 and 2 respectively) and drag, the value does not repeat 2 rows in between and does not keep the numbering in sequence after the 2 rows.
At Present:
X4 1
X5 2
X6 3
X7 4
X8 5
X9 6
X10 7
I need to have
X4 1
X5 1
X6 1
X7 2
X8 2
X9 2
X10 3
X11 3
X12 3
and so on
I have a 2000+ rows to manually input and hence it is not practical!.
View 12 Replies
View Related
Jul 29, 2008
I want the range to stay as i put it without it +1 when i drag the formula down my spreadsheet, below is the formula im using (for a creche in case your wondering)
=IF(G5<A8,"Baby",IF(G5<A16,"Wobbler", IF(G5<A24,"Toddler",IF(G5<A30,"Playschool","Please use DD/MM/YYYY FORMAT"))))
G5, A16, is taken from a list of dates down the side so i need these to remain constant otherwise my formula goes out.
View 3 Replies
View Related
Sep 5, 2008
I am trying to link from one spreadsheet to another and drag the cells down to copy the forumula, however I want to drag vertically on Sheet 1, and Copy the values horizontally from sheet 2.
For example, in sheet 1 I link cell A1 to equal cell A1 in Sheet 2. If I drag down the formula in sheet 1 A1:A10 then it will copy the values in cells A1:A10 in sheet 2.
Now what I want it to do is for me to drag the formula in cell A1 down to A10 in sheet 1, but for this to return the values of A1:J1.
View 3 Replies
View Related
Sep 22, 2008
I understand how to click and drag a cell or row of data but is it possible to click and drag a group of rows of data? I have 12 rows of data that will repeat itself every 12 weeks. I can "save & paste" easily enough, but just curious if there is a way to click and drag 12 rows of data so that they remain the same for every group of 12 weeks.
View 2 Replies
View Related
Oct 18, 2008
I am not able to drag & fill series only in one particular sheet, in other sheets this function is working fine.
View 2 Replies
View Related
Dec 31, 2008
I have an inventory sheet where I occassionally have to create more rows with the same information. So I will create rows and then copy and paste info into them. What I would like to do is be able to drag the information by clicking on the lower left corner and populate the cells that way. The problem is that if there is a number in the cell then it will automatically add to it. For instance, if I try to drag the contents of cell labeled klaraid 1112 then it will add incrementally to this in the cells below.....1113, 1114 and so on. If there are no numbers in the thing i'm dragging then it works fine. How do I drag cells with numbers without it adding to the contents incrementally?
View 3 Replies
View Related
Oct 4, 2013
I would like this formula to increase down the column as follows:
=Temp_Data!A1
=Temp_Data!A5
=Temp_Data!A9
Sequence is addidng 4 each time... Dragging results with:
=Temp_Data!A1
=Temp_Data!A5
=Temp_Data!A9
=Temp_Data!A4
=Temp_Data!A8
=Temp_Data!A12
I have seen the following work for others:
=INDEX(A:A, (ROW()*2)-1)
Can a macro be created to store the number as a variable and loop a certain amount of times adding four to that variable every time?
View 3 Replies
View Related
Nov 9, 2013
i wanna to put check boxes in colums d4:d41 with a formula inside which beginning with this formula (=Sheet1!$D$4) in cell d4.how to copy and drag down this formula into d5:d41?
View 8 Replies
View Related
Aug 26, 2009
I need to drag a cell using a macro and using relative reference.
i.e. drag activecell to activecell.offset(1,-1)
View 3 Replies
View Related
Jan 27, 2010
I would like to copy a large selection of hyperlniks down a column of 200 plus cells and would the hyperlink to increment as I drag the cells downwards.
Is there a simple way to copy and paste down a large selection of cells, so that I do not have to edit each hyperlink individually
Have enclosed example.
View 4 Replies
View Related
