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
ADVERTISEMENT
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
Mar 1, 2012
I need a VBA macro for filling the blank cells in whole column "D" with some conditions.This condition work for whole column.
ABCDCN
EA
Polymer
DE
MA
Polymer
Medium
CN
EA
Polymer
Fill the blank cell in D column with "Easy", if Column A is "CN" and column B is "EA"
Fill the blank cell in D column with "Medium", if column A is "DE" and column B is "MA".
After Run the macro:
i.e
ABCDCN
EA
Polymer
Easy
DE
MA
Polymer
Medium
CN
EA
Polymer
Easy
I was used "if" condition but I can't able to run.
View 2 Replies
View Related
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
View Related
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
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
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
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
Feb 2, 2012
If column B is not empty, I want the corresponding cell in the same row of column A to contain the value "1".
View 9 Replies
View Related
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
Sep 25, 2007
I want to be able to have a macro that will compare one cell to the rest of the cells in a collum. I have used the COUNTIF() formula and it's getting on my nerves a bit because it seems as though everytime i sort the data, it totally messes up the formula (as in it changes the cell refernces to seemingly randomly generated numbers, but that is a different matter! lol). here is the formula, and it will be followed by the formula that is in the cell directly below it: =COUNTIF(F2:F65536, F2) and the one below it: =COUNTIF(F2:F65536, F3). Only one number needs to change, but when i fill down in excell, it wants to add one to every number, even when i click COPY cells... oh well. So i want to write this macro that will do just that. That way, it has fixed reference parameters e.g. F2:F65536, and one variable parameter e.g. F2, F3, F4 and so on. I'm not too savvy with this whole VBA stuff... the university insisted that everyone uses C++ nowadays, lol. I would like this macro to output its data into an adjacent cell ex. if F2 was compared, the data will be placed in H2.
View 10 Replies
View Related
Sep 26, 2006
In my worksheet I have a column with the number 1 in cell G3 ,I want to fill down the column with the number 1 as far as there are rows that have text in them. How do I tell Excel to fill down only as far down as the end of my table.
I would like to run this in a macro.
View 3 Replies
View Related
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
Jan 25, 2010
Is there a way to fill all rows of a column with the same formula without having to copy and paste it? My table is huge (about 6000 rows) and I need to perform the same computation on each row.
View 8 Replies
View Related
Apr 8, 2008
I have a column of data with various values and a bunch of blank spaces. Essentially I want to leave the values as is but fill in the blank cells with a number. I’ve written a loop to do this in VBA, which grabs the value in the cell above, but it’s somewhat slow. Is there a more efficient way to do this?
Sub Downfill(Max)
'
'This count variable is used to run the loop
Dim i As Long
i = 1
Do While i
View 9 Replies
View Related
Feb 17, 2007
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.
View 13 Replies
View Related
Aug 21, 2013
Let's say I have a value in B2, and a formula in C2. If I drag the formula from C2 all the way down to C20 (spreading it 18 times), the formula wants the value in B2, B3, B4, etc.. down to B20. How do I drag a formula, but have it always reference B2?
View 1 Replies
View Related
Dec 11, 2008
I have problem with inputbox which I can not select the cell by dragging the mouse. Means user have to enter manually the range into the textbox. At below i attached the code and picture of inputbox dialog box.
i am using MS office 2003.
Set GetRange = Excel.Application.InputBox(Prompt:="Select a range:", Title:="Tes", Type:=8)
View 9 Replies
View Related
Apr 28, 2014
I am trying to fill a column with a number series starting with 1 whenever a value changes in the adjacent column. So for instance I am trying to do something like this :
87 1
87 2
87 3
88 1
88 2
88 3
88 4
89 1
89 2
90 1
91 2
92 1
92 2
92 3
92 4
92 5
92 6
View 6 Replies
View Related
Mar 5, 2008
I am trying to auto-fill a column based on certain criteria - in other words, fill in blank cells based on a cell above, just a little more complex version. I have added an attachment to give everyone a visual of what I am describing and then maybe this explanation will make a little more sense.
First, if you open the attachment, the left hand side is a small example of what I have. The middle is the condition. And the right hand side is what I'd like to see happene.
In the example, I am matching up a time and when I hit that time, I am adding on a certain number of miles per hours based on the condition and the cell above. Meanwhile, I am filling in the blank cells with the condition until the condition changes. So a cell is incremented based on the condition and gets its value from the cell above.
View 14 Replies
View Related
Jun 27, 2007
Is there a way that I can make a macro that populates ComboBox1 of sheet 1 with the values in the B column, where the number of elements in the B column might be any value?
For example, if there are values in B2, B4, and B8, I want ComboBox1 to show those three values.
View 9 Replies
View Related
Sep 1, 2009
I have 6,000 rows of check detail. The check number is listed in both columns B & C.
If the check paid multiple invoices the check number is not repeated in columns B & C.
I would like to fill the cells in those columns with the appropriate check number. Data looks something like this-
item ck_01 ck_02 vendor ck_date
1 10100 10100 ABC 09-01-09
2 10101 10101 ALLIED 09-01-09
3 ALLIED 09-01-09
4 ALLIED 09-01-09
5 10102 10102 BEACH 09-01-09
6 BEACH 09-01-09
7 10103 10103 CAP 09-01-09
8 10104 10104 DART 09-01-09
How can I fill the balnks with the check number (above)?
View 9 Replies
View Related
Jul 10, 2014
I am using the formula below to sum across several worksheets, and the formula I am using works fine, but when I copy the formula across to other cells, I can't figure out how to have it update my cell range:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!G:G")))
The range A:A is ok to be static, but I need the G:G to be dynamic so when I drag it to the right it will change from G:G to H:H. I:I, etc.
View 6 Replies
View Related
Feb 19, 2014
I have a workbook with multiple sheets interacting with one another. On one of them the user is prompted to make lists of expenses in multiple columns. The column labels are in row one, and row two has the sums of all the cells below. I have every sheet in the workbook protected, so that only the cells that need to be modified are unlocked. The problem is that the user can drag cells around and change the range of the functions in row two (locked cells).
For example, A2 has the function =sum(A3:A100). But if the user drags the values in A3:A5 to A6:A8, the formula in A2 changes to =sum(A6:A100). Is there anyway that I can allow the user to drag cells (this could be a useful feature), but keep the formulas in the second row fixed?
View 5 Replies
View Related
Jan 2, 2013
When I'm in edit mode (in other words, when I double click on a cell to, say, make a reference to another cell), it still displays the colours of the cells that they're referring to. However, it only shows a simple outline but cannot move it by mouse. I should be able to see small squares in each corner of the cell so I can either move it or enlarge/shrink the cell reference. how to re-enable this feature. I like it because I need to be able to SEE the cells better
View 2 Replies
View Related
Dec 22, 2008
I have two worksheets. One that contains material pricing and another that has misc data, formulas, etc. I have 100 rows of data with 2 columns. Column A I have numerical values. Column B I have a formula =(A1/36.4)*sheet1!C7. I try to drag down the formula but the value for sheet1!c7 changes as I go down (sheet1!c8 then sheet1!c9 and so on). How can I drag down the formula and have the SINGLE value from worksheet 1 carry to all rows?
View 3 Replies
View Related
Nov 20, 2012
I have an excel sheet with 2 tabs. 1st tab provides the data for downtime of a manufacturing line. The last column (shift) is blank and needs to be filled based in shift schedule in tab2
On the 2nd tab I have the shift schedule, which tell which shift is working on particular days
Month
Date
7:00am- 7:00 pm
7:00pm - 7.00am
Feb
1
Shift A
Shift C
[Code] ........
Is it possible to write a macros that will look at the Date and time from tab 1 and assign the correct shift number in the shift column. For example the 1st row of tab 1 reads (Month=Feb, Date = 3:55:59 AM) so according to shift schedule it would fill C shift.
I have lots of data and to assign every event to shift # manually is not possible. So i wanted to know if there is a macros i can use to make life easier and learn at the same time
View 2 Replies
View Related
Dec 12, 2007
Works fine up to and including .List(i, 9) however when it bangs out with an error when it gets to .List(i, 10). Error Message reads: Run-Time Error '380': Could Not set the List property value.Invalid Property Value. If I Switch to using .column(10,i) I get the same error but for column instead
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String, HASH As String
Dim D1 As String, D2 As String
Dim i As Long
HASH = Chr(35)
'*********TEMPINFO********
D1 = "12/3/2007" '*
D2 = "12/9/2007" '*
Agent = "Team_Stats" '*
i = 0 '*
'*************************
glob_DBpath = ThisWorkbook.Path & "databaseTS-Stats.mdb"
glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_DBpath & _
"; Jet OLEDB:Database Password=********;".......................
View 5 Replies
View Related
Feb 12, 2014
So I have exported a canned report showing me payments by day and it is EXACTLY what I need EXCEPT the dates don't repeat (and it has spaces and some headings but those aren't a big issue). Anyway, I need to dynamically fill in these dates for an entire year.
To be clearer, column A starts off with 1/1/2013 and column B has payment amount, column C check number, D invoice number etc.
But the rest of the cells in Column A for 1/1/2013 after the first row are blank until you hit the payments for 1/2/2013. Then 1/2/2013 is listed once and then blank (or junk header data at a page break) until 1/3/2013 and so on. And of course there are different numbers of payments for each day.
I'm looking to dynamically fill in the dates so that I have a real data /flat file that analysis can be run on. I have some ideas ... Could probably incorporate a "do until" statement somehow...
View 2 Replies
View Related
Dec 9, 2012
Not sure if this can or would be done in vlookp??. In my example the print page needs to get data from a list where people set.
View 3 Replies
View Related
