How To Fill Down Selection But Increment Formula In Cell By +1
Mar 29, 2014
I have items in a column like below:
Line (A1) asdfasdf
Line (A2) adsfadsf
Line (A3) asdfasdf
What I would like to be able to do is highlight the three columns and fill down, but I would like for it to increment and paste the next three lines filled like this:
Line (A2) asdfasdf
Line (A3) adsfadsf
Line (A4) asdfasdf
Then below that the next three lines filled like this:
Line (A3) asdfasdf
Line (A4) adsfadsf
Line (A5) asdfasdf
The (A1, A2, etc.) are cell ranges and already contain the concatenate function that pulls data from a different location. The problem I am having is when I
highlight and fill down, it doesn't increment the cell by +1 (Ex. A1 beomes A2) but instead by +3 (A1 becomes A4) the number of lines highlighted.
View 8 Replies
ADVERTISEMENT
Oct 3, 2006
I tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:
1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.
I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................
View 2 Replies
View Related
Aug 4, 2007
I dont know if this can be done, but is it possible to automatically fill a column with check boxes which correspond to data in adjacent cells? I am aware of the marlett method, but Im really looking for some sort of rigid check box appearance, where the user just needs to click, and not type a, or y, etc. I have a macro which outputs a column of numbers in column A, and I would like to figure out if it is possible for a macro to automatically add check boxes in column B for all the values which show up in column A.
This is so that when the user selects some check boxes, the corresponding values in column A are then each copied to copies of a specific sheet, thus creating a multiple copies of the same worksheet for each value. This sounds like it could make a huge memory problem (from what I've read), but Im really dealing with a very small amount of data.
View 4 Replies
View Related
Dec 17, 2012
I have items in a column like below:
50
50
50
50
What I would like to be able to do is highlight the four 50's and fill down, but I would like for it to increment and paste the next four filled like this:
51
51
51
51
Then below that the next four filled like this:
52
52
52
52
If its a macro that'll be required I'd like to to be able to change the number of rows to be repeated (4 in this case, but could change).
View 4 Replies
View Related
Jun 14, 2013
I have a workbook that has a few data sheets and a summary sheet. On this summary sheet I have to grab data from the data sheets which is in the same row number but different column (always 5 to the right of the previous one).
Eg: Person's Name is in Row 10, Col E (E10), the next name is in J10, then O10, etc. This carries on past Column Z. For each person there is a bunch of other data which is in the same format (set row but 5 over for column).
Since row stays the same i thought i could absolute reference that and then leave column as relative and drag down. This works but only if I drag to the right (across columns) and only if i need to increment by 1.
View 2 Replies
View Related
Apr 28, 2014
I have data in several rows whereby my column D looks like this:
FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_04.0000
FebtReport14_05.0000
Throughout the week, additional data is added to the rows, thus creating blank cells within this D Column:
FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_04.0000
FebtReport14_05.0000
Is there a macro i can run which will re increment only the blank cells to look something like this?"
FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_03.1000
FebtReport14_04.0000
FebtReport14_04.1000
FebtReport14_04.2000
FebtReport14_04.3000
FebtReport14_05.0000
Also , I will be adding new data quite often, in which newer rows may need to be added in between: ie:
from this
FebtReport14_04.0000
FebtReport14_04.1000
to something like this:
FebtReport14_04.0000
FebtReport14_04.0100
FebtReport14_04.0200
FebtReport14_04.1000
View 5 Replies
View Related
Aug 30, 2008
I have been frustrated trying figure out what I would assume is actually a pretty simple solution. All I am trying to do is grab a set of cells and repeat them down the column a set number of times. The 2 forms of this are discribed below.
A: I have a set of numbers from A1 to A51 I want to repeate those numbers down the columns 84 times.
B: In addition to this I need to have the cells from B1 to B51 Repeat 84 times but with an increment of 200 each time. So I would have say 1 in B1 - B51 and in B52 - B102 it would turn into 201 and so on and so forth.
View 5 Replies
View Related
May 22, 2008
When I have a text field, such as 123 Main Street, Suite 456 and I copy it down, I get
123 Main Street, Suite 456
123 Main Street, Suite 457
123 Main Street, Suite 458
What is this feature called, and how do I turn it off?
View 2 Replies
View Related
Jul 30, 2013
I have got the following problem. I have got a table which looks like this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
When I mark the selected cells and scroll down using the autofill option I get this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
=Time!A6
=Open!A6
=max!A6
=min!A6
=Close!A6
Why does it give A6? I want A2 instead of A6 because I want to create a table from A1 to A500 but without these jumps from A1 to A6.
View 5 Replies
View Related
May 24, 2008
I'm quite a novice at Excel. I have a column of values that I sum as follows;
A
0
0
0
0
0
0 <----------------sum of A1:A5
A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
View 7 Replies
View Related
Jan 20, 2010
when I select a value from my drop down list how can I fill in say.. cell A50 with a number automatically?
Pretty much on a drop down selection I want to fill in another cell.
View 11 Replies
View Related
Aug 27, 2012
I have to run reports every week where I show the number of days items are pending some sort of action. I use an autofill action in a macro but the number of rows changes every week. Here is the current section of my macro that I use. I just input a row number that I know will not exceed the number of rows required by my data (12900). What can I replace this with (Selection.AutoFill Destination:=Range("J2:J12900") so it will only fill the number of rows current filled in in either row I or K?
Range("J2").Select ActiveCell.FormulaR1C1 = "=R1C19-RC[-1]" Selection.AutoFill Destination:=Range("J2:J12900") Range("J1").Select Selection.Copy Columns("J:J").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=FalseApplication.CutCopyMode = False
I have a date in cell s1 that I subtract from the date in row "I". I then copy the result of row J and paste it as values.
View 6 Replies
View Related
Jul 6, 2007
I want to dynamically list items in combobox placed in excel sheet. I have a list of 10 values in a column in excel sheet. I have placed two comboboxes in the same sheet. Now depending on the first combobox selected value i want to list either all or only few values from the available 10 values.
View 4 Replies
View Related
Oct 30, 2009
Have 2 listboxes, the contents of the second (fmmultiselectmulti) is populated based on selection of first. Sometimes (50%) when I open the workbook I receive a "Object Required" runtime error..
Private Sub ListBox1_click()
Select Case ListBox1.Value
Case "All"
ListBox2.ListFillRange = "_Sheet2!A1:A1"
Case "A"
ListBox2.ListFillRange = "_Sheet2!B1:B18" <--- example of line that gives the 424 - Oject Required
Case "B"
ListBox2.ListFillRange = "_Sheet2!C1:C18"
End Select
End Sub
Looks as though sometimes when it runs, listbox2 is not yet initialized ?? If I go into Debug and look at ListBox2 it shows up as type "Variant/Empty" and not " ListBox/ListBox" ??!!?? Is this some type of timing/race condition on the loading of controls ? I'm out of idea's. Both listboxes are on the same worksheet (Sheet1). The ListFillRange for Listbox1 (which is a fmmultiselectsingle) is hardcoded and also references a range in _Sheet2 - no problems with this control.
View 2 Replies
View Related
Jan 5, 2008
There is a small command button in cell A11 that brings up the userform. I have a multi-tab user form the userform contains a number of combo boxes with an associated textbox right of each. I would like to populate the textbox based on the value selected in the combo box. For example when Ice Foot is selected (Type of Fast Ice) populate the textbox (txtFastIceEncoded) with the value of 6. These values are located on 'decode' sheet. I then want to take the four values and place them in the textbox 'txt_Tw_Tw_Et_DE_ai_group'. Ultimately I want the values to end up back in the worksheet 'Synoptic Ice Obs'. Perhaps there is a better way to do this than I am trying. I have poured through countless threads in this forum and have tried a number of these. I however always seems to run into some problem that I do not understand fully. I have included a copy of the workbook.
View 4 Replies
View Related
Dec 16, 2012
auto fill the rows after selecting a particular option from a drop down list. For Ex., In Drop down list we have Cool drinks and Ice cream as two options. If i select Cool drinks the respective columns should be filled with brands like, Coke, 7 Up etc...
View 3 Replies
View Related
Dec 19, 2013
Source tab contains vital information about some clients.
In the aggregated tab (Cell C10) I created a formula that pulls the Inflows from the source in a very specific array. So for client 1, this works fine. Now, if i copy my formula to the client 2 (Cell C14), it obviously wont go and look in the correct array in my source.
What i need to do is to be able to copy/paste my formula
[Code].....
(from cells C10 to CC10) to cells C14 to CC14, but when copied, the look up array changes to:
Formula: [Code] ....
I will have to fill this formula to at least 100 entries down, so i need to make it work with ease
The good thing is that all look up values in the source increase by a fixed number of rows (12). I tried playing with index/rows formula.. no luck..
Attached File : samplev1.xlsx‎
View 1 Replies
View Related
Dec 23, 2008
I read in a book that if you enter a formula in a cell, like cell A1 contains
=rand() for instance, that if you select Go on the menu tab, and then enter the final destination cell or range (ex: A1,A200), then hit ctrl+Enter simultaneously, it will fill the formula down to that cell. I can get it to select the range, by hitting Shift+Enter, but not copy down the formula using Ctrl+Enter, or Ctrl+Shift+Enter. I am using excel 03, XP. It only returns blank values for the range.
View 2 Replies
View Related
Jun 12, 2008
I have written a macro that creates a new spreadsheet and copies text into it. The number of rows varies depending on the input.
Yet, running the macro results in a sheet with the max rows (65k+). This is unmangeable, as it makes it a huge file and size and harder to print.
I only want there to be the number of rows that have text in them.
The larger problem is how these were even created in the first place. Currently:
' Formula for Job Name
Range("A2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[12],8)"
' Fill Job Name
Range("A2").Select
Selection.AutoFill Destination:=Range("A2", Cells(Rows.Count, "A")), Type:=xlFillDefault
View 3 Replies
View Related
Sep 22, 2008
I am trying to make a calendar spreadsheet to enable me to track allowance payments i am due from my company.
This payment is worked out by three conditions - where i am working (7 regions), how many days of the week (4,5,6 or7) and how many hours (<7,8-9 or 10-12)
I have made drop down lists to select each of the values for the three conditions and want to show the total for them
i can total the entire spreadheet for the month up using fairly large "COUNTIF" statements, but i want to be able to show what the exact amount is in each cell not just a complete total.
i.e. i am away for 6 days i get £15 extra, if i am in Europe i get another £50 and if i work 12 hours i get £30.
I want to be able to calculate this total in each cell for that day.
I can send a copy of the spreadsheet i have got at the moment if that will make things clearer than what i have tried to explain.
View 12 Replies
View Related
Dec 20, 2013
How do I write this formulae ??
For cell range M6:AV6 fill with N/R If the data in cell D1 = Wont have
View 5 Replies
View Related
Aug 19, 2014
I have created a drop-down menu combobox of districts and depending on your choice of districts the list of Health Centers in each district changes. What I want to do is to protect the contents of the cells that contain the health centers. The problem is that when I lock the cells and I choose a district, the Health Center cells cannot change because the cells are locked. Is there anyway around it?
I have attached the sample (Please look at sheet 2. The column that I want to block is the column FOSA)
View 4 Replies
View Related
Mar 11, 2013
I am desperately find a way to fill down formula from active cell, example given below,
AA
BB
CC
Total
QTY1
QTY2
City 1
5
2
3
10
12
15
[Code] .......
I need to fill down col Qty 1 & Qty 2. Number of Rows and Columns vary in my Work Sheet.
My code below, Ctrl+Enter not works.
HTML Code:
Cells.Find(What:="QTY1").Activate
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-3]"
'Fill Down Active Column
Cells.Find(What:="QTY2").ActivateActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=+RC[-5]*RC[-3]"
'Fill Down Active Column
End Sub
View 3 Replies
View Related
Nov 14, 2009
I have a formula
=LEFT(L2,FIND(".",L2,1)-1)
I need a macro to copy this to the last blank cell in the column, the last blank cell will be different each month otherwaise i would have just been able to enter a range L2:L6000.
View 9 Replies
View Related
Jan 6, 2007
what is prompting this formula to auto fill down when a value in an adjacent cell is entered.
In column A I enter an unformatted fax number.
In column B I use the following Text Formula:
"="("&LEFT(A20,3)&")"&MID(A20,4,3)&"-"&RIGHT(A20,4)
Someone in my office copied the workbook and now any time a value is
entered into the next available Column A cell. The cell in column B will automatically apply the above formula and format the fax number. The person who copied the workbook does not know how this is working nor does anyone else in the office.
There are no macros embedded in this workbook so no event is firing to cause this.
I have attached a small copy of the workbook called "Auto format1.xls"
How to use:
-Select cell B20 and not nothing is in it.
-Enter any 9 digits in A20 and B20 somehow
copies down the formula from B19.
how to duplicate this.
View 9 Replies
View Related
Sep 15, 2008
I have a formula that I'd like to have increment by one, but I can't figure out how to do it.
Here's the formula:
=IF(INDIRECT("'Supervisor'!B"&ROW()-4)="Y",E12-D12,IF(INDIRECT("'Supervisor'!B"&ROW()-4)="N","0",IF(INDIRECT('"Supervisor'!B&ROW()-4)="","0")))
I'd like to have the -4 in the equation increment to -5 when I paste it to the next row without having to fix each one of them.
View 14 Replies
View Related
Dec 26, 2012
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
View 6 Replies
View Related
Feb 10, 2012
Can I have a formula in A1 cell to display the No. of cells in selection (in a range) any where in the sheet. Just the No. in A1 cell is sufficient
View 4 Replies
View Related
May 12, 2013
I need to clear a value in a cell(what ever may be the value) based on a condition in another cell and set it to blank. Ex: If cell A is blank and then i need make cell B also blank, if cell A has some value then i should not disturb B I need to do this using formula. I have tried the below, but no use, IF(AA1="",REPLACE(Y1,1,10,""))
View 2 Replies
View Related
Jul 17, 2014
I've created the following Macro based on some of the posts on this great forum! It works great, but I'd like to tweak it for another purpose where it would fill the formula down until a cell in column AF had the value "#N/A" or stopped having the value "clean". The #N/A is from a formula result so I'm not sure if or how the Macro would treat this.
Code:
Sub Y_CleanUp3()
'
' Clean Up Bad Data Macro 3
Dim LR As Integer
Range("AH2").Formula = "=VLOOKUP(X2,'[Territory by Zip Code.xlsx]Sheet1'!$A$2:$B$135000,2,TRUE)"
LR = Range("AF" & Rows.Count).End(xlUp).Row
Range("AH2").AutoFill Destination:=Range("AH2:AH" & LR), Type:=xlFillDefault
With Range("AH2:AH" & LR)
End With
End Sub
View 2 Replies
View Related
