How To Autofill 9,000 Rows With Changing Reference In Same Column
Aug 9, 2012
I have over 9,000 rows of data. In column A, I have different values that I need to populate down to associate with values in other columns. I can't simply autofill all 9,000 cells in column A at once, because the values that need to be filled down change at irregular intervals.
My end goal is to be able to filter out values in column B to show their association with the value in column A, but I need column A fully populated.
So I need a way to fill A2:A7 with value from A1 (I don't care about B7 being empty, I can still have Martha in A7 with no adverse affects). But I need the fill to continue through 9,000+ rows where the number of rows to fill is inconsistent between value changes in column A (Martha-5, Sarah-3, Beth-4, Donna 3), and there are over 400 unique values in column A.
This is definitely more involved than I am familiar with, but any simple way for me to identify and list which of the 400 bakers made scones..
A
B
1
Martha
[Code]....
View 5 Replies
ADVERTISEMENT
Mar 6, 2008
A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.
When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")
ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
View 2 Replies
View Related
Apr 15, 2014
I'm working on a decision matrix to evaluate different alternative production methods. Before that criteria have to be choosen and weighted against each other. The user would fill out the yellow/ blue cells with (0=less important; 1=equally; 2=more). Then values are added up and put into some kind of ranking.
The example was done by hand, which costs a lot of concentration. I would now like to extend the matrix - do you know how to fill out the grey cells more efficiently?
(Excel 2007, Win7)
View 1 Replies
View Related
Aug 13, 2008
I have a table of consolidated data that consists of the date at which certain payments occur and their amounts that other sheets need to reference to perform calculations. I am currently using the formula below to put the data under the correct date column. =INDEX(ImportedData!$B$10:$DB$10,MATCH(B3,ImportedData!$B$4:$DB$4,FALSE)). The problem that I have is that the INDEX range, currently B10:DB10, will need to change depending on the number of files that are consolidated ie with more files the row will increase.
View 2 Replies
View Related
Dec 9, 2013
I have 2 Sheets in Excel: Sheet 1 & Sheet 2
Sheet 1 has some basic data (numbers) in a column, say AB1, AB2, AB4, AB15 (specific cells)
Sheet 2 basically references the data in a nicer template, with the basic forumala: ="Sheet 1"!AB1, etc..
Now...Sheet 1 has a new column inserted and new data added weekly, so AC1, AC2, AC4. AC15
How do I ensure that Sheet 2 recognises the new Column Insert (incremental letter) and references it automatically?
View 6 Replies
View Related
Jun 4, 2009
I have referenced data in two colums on a sheet A and B. Column A contains the latest data, each month i insert new column (moving column A to column B). However all of my references continue to follow the original data (eg will change from column A to column B). this happens despite using Absolute references. (=$A$1). Is there a way to lock these cell references to only ever display column A etc?
View 2 Replies
View Related
Dec 7, 2013
I have to create a number of spreadsheets with changing dates / times / both.
This would be a fairly good example:
Add one day + 1 hour for 31 days of a month.
"01/01/2014 01:10"
"02/01/2014 02:10"
"03/01/2014 03:10"
"04/01/2014 04:10"
"05/01/2014 05:10"
"06/01/2014 06:10"
"dd/mm/yyyy hh:mm"
The above format is important, and - it has to be in quotes.
Which, if necessary I will manually add using replace, but would prefer not to.
So I may then need to take the first date... lets say 06/12/2013 07:00 then, Add one day + 3 hrs 30 mins.... so as the days roll between 1 to 31 it is obviously going into the 24 hour clock, rolling argument.
And, to top that -
1. the csv then needs to be put into software (I assume it reads the data in the sheet and not the formulas).
2. I can't have extra columns as they would need to be deleted.
I have tried copying down, like a standard auto-fill but it always goes wrong.
View 3 Replies
View Related
Jan 23, 2013
Excel 2010
I have this code in a macro, the range will change as more data is added. so that I dont have to keep changing the range. How can I have this code autofill from the activecell to the last cell that has data in column M.
Selection.AutoFill Destination:=ActiveCell.Range("A1:A50000")
ActiveCell.Range("A1:A50000").Select
View 7 Replies
View Related
Aug 16, 2013
I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.
Below is an example with the solution I am attempting to produce.
Sample Table
Name
Dept
Abbruzzese,James L
188100
[Code] ......
Output
Name
Dept
Abbruzzese,James L
188100, 231100, 600377, 600656, 600663, 600708
Abdi,Salahadin
600607, 600670, 600878, 600879, 710432, 710432, 710435
View 2 Replies
View Related
Mar 17, 2009
I have 2 sheets in a workbook where i would like cell G8 on sheet 1 to equal the value on A11 sheet 2 & G9 to equal A12 and so on down the sheet. The problem is that G8 are 3 merged cell so every time I try to use autofill the reference number jumps by 3. The formula in cell G8 is
View 3 Replies
View Related
Jun 24, 2009
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
View 9 Replies
View Related
Feb 17, 2014
I have an workbook (AUTHPbWB) that has a sheet(Replacement) where data is input by end-user. it also has a button(Accept) which stores data in another worksheet based AUTHPbWB cell value D5. The variant data is in range B8-h15 of AUTHPbWM/replacement. When the accept button is clicked, it searches the database sheet for the next available row and inserts the new records. The database sheet has column A that has predefined values that should not change.
what im looking to do is, on clicking the accept button and when the records update in database, the corresponding values in column A of the database sheet get copied to another worksheet (data) only for the new records updated.
[Code] .....
View 1 Replies
View Related
Apr 8, 2014
I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:
Sheets("Sold Articles Database").Select
Range("U3").Formula = " =VLOOKUP(LEFT(K3,2),'Input Variables'!$A$48:$B$52,2,FALSE)"
Range("V3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$C$10000,3,FALSE)"
Range("W3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$D$10000,4,FALSE)"
[Code] ........
View 3 Replies
View Related
May 26, 2009
I'm working on setting up a chart. The formula I'm using for cell B21 is this: =ROUND(F3,0) & " " & IF(E3<F3 ...
View 2 Replies
View Related
Nov 27, 2011
Autofill column B with sequential values based on whether value in column A changes its value.
I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.
This is what I imagine.
A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"
I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.
I'm using MS Excel 2007 in Windows 7.
View 2 Replies
View Related
Jul 14, 2009
I have three sheets - Actual( which has actual figures from Jan - Dec), Budget ( figures from Jan - Dec) and a Summary sheet ( which is just the summary for the current month, say July and compares the budget VS Actual). Every month i need to change the summary sheet to the next months set of figures, say Augusts etc etc.
Currently i've been using find and replace, and changed the formula to the correct months column - but its a rather large spreadsheet( its a management accounts pack and has many many more sheets that link up - like lasts years comparison etc etc)
Is there any easy way of linking ( without VBA) or anything complex, so that each month i can change my data easily?
View 3 Replies
View Related
Aug 23, 2007
I want to select a specific worksheet by it's VB name. In other words, if the sheet comes up as: Sheet3 (ESF) in the VB Editor, I would like to choose it using the Sheet3 designation. I can do it using the (ESF) designation using:
Sheets("ESF").Select
But I prefer to use Sheet3 so when the users change the name, the macro still works.
View 6 Replies
View Related
Dec 14, 2007
I have recorded a basic macro that sources data from a number of workbooks.
If I change the filename in the workbook with the source data or move this file to a different folder path the macro does not run without errors.
Does anyone know what vba code I can use to address this problem?
View 6 Replies
View Related
Oct 28, 2012
I need this solution if possible:
On one part of sheet I have items set in rows : A1= apple,A2=banana, A3=orange etc.
Now I need to set in lets say A55=A1, B55=A2, C55=A3 etc. How to do this in fast way without writing a formula in each cell?
View 5 Replies
View Related
Dec 15, 2008
Example:
Tab where the information is captured:
___A____ B____ C____ D
1 _TP1__ TP2__CON___PT
2 __3____3_____2____4
3 __2____1_____6____5
The tab where I want the info to pulled to runs in a row, not in columns, and there are 6 different branches with 35 different categories that I need to add the formula to to pull the info I require.
So, when I type the formula (=TAB1!A2) in A1, I get the correct info I require, but then when I drag it to the right (A2), it changes the formula to (=TAB1!B2) as apposed to what I want which is (=TAB1!A3). So now I have to manually type the formulas which will take me forever!
View 9 Replies
View Related
Nov 14, 2009
I have a master summary sheet that contains all the required information for 21 other worksheets. Each row contains the information for a single sheet. I have linked all the relative cells to the first sheet but would like create a formula that changes the row reference in each of the links rather than the tedious task of updating each individual link in every sheet.
I have tried using the indirect function but constantly get errors returned, the basic formula i have been using is: =INDIRECT("Summary"!C&,X1)
'C' is the column in the summary sheet that the information should be taken and 'X1' is the cell in the worksheet that will define what row the data should be taken e.g in one sheet the reference for that specific cell will be C5 and the next C6 and so on.
View 4 Replies
View Related
Jun 23, 2009
I'm trying to take data from multiple worksheets and copy them into a summary sheet in the same workbook, and then make a pivot table from the summary sheet.
I got the first part done, but I can't figure out how to make the pivot table to incorporate new data in the summary sheet as new worksheets/data is added.
View 2 Replies
View Related
Aug 10, 2013
I'm using the following formula which is entered by using VBA on the worksheet change event but the cell reference 'A2' is not changing for each row, i.e. A3, A4 etc.....
Code:
=MAX(IF( 'Device Use - 4 month Period'!$A$2:$A$20000=A2, 'Device Use - 4 month Period'!$C$2:$C$20000))
View 4 Replies
View Related
Dec 5, 2006
I have a set of formula in column C to H. I have set a formula as sheet1+sheet2+ like this for about 24 sheets with different reference of cells. I want to change in the entire range of cells C to H as =sum(sheet1:sheet24!b04). The reference what I have mentioned changes in different cells.
View 9 Replies
View Related
Feb 2, 2010
I am trying to run a quick format on a table that includes adding a Vlookup formula. The formula references a table that comes out of a pivot table, so it has a different name every time. If I look up the new generated table name, and change it below it works. Is there a way to capture the table name and insert it into the formula? Or possibly rename the table to the same thing everytime (which I think is going to cause some conflict).
....
View 9 Replies
View Related
Apr 7, 2007
I am creating a schedule to use for our helpdesk - it has pre-defined tasks that need to be assigned each day - easy enough to accomplish with a formula. And I've done this by using this similar formula on each line of the tasks (This first task is based on a manual entery into one field (B19))
=IF(AND(B6="Liz"),"Paul",IF(AND(B6="Paul"),"Mark",IF(AND(B6="Mark"),"Diane",IF(AND(B6="Diane"),"Jeff",IF(AND(B6="Jeff"),"Dan R",IF(AND(B6="Dan R"),"Chris",IF(AND(B6="Chris"),"Liz","")))))))
So, B4 is one task (phones) and the formula above (B6) is from another task (tickets) and then a third task (admin) looks at the value of B5 and uses the same formula above, etc.
However, I want to be able to change the value of a single cell, based on the (manual entry) value of another (out of task) series of cells (i.e. Sick (B35) & vacation (B36)) - and I don't want any of the other task cells(in the C cell series of 'tasks'), that are basically linked together by the value of the other cell, to change. Thus, in turn, would mean that the same person would now be in two different cells, and thus is creating a circular reference.
Let me spell this out a bit clearer.
The first manual field is B19 - If I enter 'Chris' into this field, then field C5 matches it. Then field B6 uses the formula above to figure it's value; as does B7 thru B12.
Now, If I put another manual entry into 'Sick' (B35), then I want whichever task that person is assigned to (for this example, we'll say I entered 'Jeff" into Sick, and Jeff happens to be on 'tickets' that day) to change to whoever is in cell B12 - but I don't want any of the other cells to change - but, since they are all dependant on each other, this doesn't seem like a possibility.
I've tried creating a count of names in the Sick or Vacation fields and then added this to the formula
=IF(AND(C5="Liz", B38=0),"Paul",IF(AND(C5="Paul",B41=0),"Mark",IF(AND(C5="Mark",B40=0),"Diane",IF(AND(C5="Diane",B40=0),"Jeff",IF(AND(C5="Jeff",B43=0),"Dan R",IF(AND(C5="Dan R",B37=0),"Chris",IF(AND(C5="Chris",B45=0),"Liz",B12)))))))
B38 being Pauls count; B41 being Marks, etc - so as long as their count equals zero, they are 'eligible' to fill that slot.
B12 is the last person on the schedule.
View 8 Replies
View Related
Mar 8, 2013
I'm trying to autofill "vertically" in a spreadsheet using a formula which loads value from a different sheet.
Lets say I have cell (A1 for example) ='2013'!A39
Now I want to fill A2-A30 in a way that the formula updates to ='2013'!B39, ='2013'!C39, ='2013'!D39...and so on
I've tried using ='2013'!A$39, but this only copies the formula as it is. When I try to autofill cell A2-A30, the formula still is ='2013!A$39
Is this possible to do?
View 1 Replies
View Related
Nov 30, 2009
I have searched for this on the site as I thought it would have already been asked but I couldn't find anything. I have a formula in cell C7 which I wish to copy both down to the last row (xlup from column A) and across to the last column (xltoleft on row 6) but I cant seem to work out how to do it. I have used the .autofill before but for dynamic rows only. Is it possible to do for columns too?
View 2 Replies
View Related
Oct 8, 2011
I currently have an array that is 6 columns by 7 rows that is populated by referencing other workbooks and returning the sum of their values. For issue tracking revison etc... I would like to have the rows incrementally numbered (0,1,2... until it reaches number "X" which is a grand total in one of the fields.
This would happen for column 1 row 1 all the way through column 6 row 7 and fields with 0 as a total don't get a number or can have a 0 or blank.
Ex.
1 2 3 4 5 6 (rows)
1 3 x x x x x
2 x 7 x x x x
3 4
4
5
6
7
row numbering...
1
2
3 (skip col. 1 row 2)
1
2
3
4..... continues on till the end.
I would like this to be something somewhat automatic to eliminate the redundancy of having to manually perform this task.
View 2 Replies
View Related
Jul 26, 2012
I have wookbook with a lot of sheets on it
The first sheet to be filled is called "Local Info"
In this sheet in the Active Cell "G7" the user must register a number between 0-500
This number must affect my next sheet called Competence Map Autofilling rows 7+8 from Columns A to AM
So If for example I put 83 in "local INFO" G7.
I will get 172 rows with text (the first 6 Headers) the 7-8 original and from 9-172 filled with the information given.
View 3 Replies
View Related
