Automatically Create List Of Column Contents Based On Criteria?
Jan 15, 2014
I have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".
V
W
X
Y
Z
AA
AB
AC
...
FB
[code]....
List of Dates Absent
x
x
x
WR
x
x
x
1/23, 1/24
[code]....
View 2 Replies
ADVERTISEMENT
Aug 10, 2014
I have a list of names in column A, and want to automatically create worksheets within this same workbook based on the list.
List of values:
Dairy NI
Dairy SI
Sheep and Beef
Equine
Sports Turf
Water Industry
Horticulture Services
Production Horticulture
Other Agriculture
Therefore, a macro or coding to automatically create 9 spreadsheets named as per this list.... My list is approximately 50+ hence inquiring if there is an easier way!
View 1 Replies
View Related
Sep 21, 2012
Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)
I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.
Obviously to size of the list will vary depending on how many open orders there are.
View 1 Replies
View Related
Apr 13, 2014
I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.
View 1 Replies
View Related
Jul 31, 2008
I'm not sure that I'll be able to explain this clearly, so I've attached an example. There are 3 columns - Issue, Action Group and Status. Many Issues belong to the same Action Group and others do not belong to any Action Group. The is also a Status summary section that has the total Issues and Actions associated with each status. For example the Status called Resolved could have 10 issues and 5 Actions (including blanks). The problem is that I can't figure out a way to get the total for the Actions. As I said this explanantion isn't great, so the attached sheet
View 3 Replies
View Related
Dec 6, 2010
I am trying to create a drop down list for a table of contents that can link off to other pages within the same work book since it is very large and difficult to navigate through. I can create dropdowns were the lists it draws from is hyperlinks and that doesn't do anything special. It is just like drawing a regular list. I want the drop down when I click on something, to link me over to the other sheet.
View 7 Replies
View Related
Jan 28, 2013
I would like to automatically color the fill of a cell based on the cell contents.
For example, if the cell content is "1" I would like the color of the cell to be cyan and if the cell content is "2" I would like the color the cell to be red.
I've tinkered with several different formulas but I just can't get anything to work.'
View 2 Replies
View Related
Jul 28, 2014
I have a excel file where I have the list of File Path in Column A & list of File names in Column B, Default path location is available in Column C. Now I want create a zipped folder based on the file name in Column B to the location Column C. I had browsed in Internet and found some code for creating the zipped file by selecting the files in Windows browser window. However my case is need to create the Zipped file based on excel cell contents.
I had found the code in the following location
Zip file(s) with the default Windows zip program (VBA)
create a code for creating the zipped folder based on Excel cell contents
View 2 Replies
View Related
Aug 28, 2008
I need some code that will clear the contents of a Row Range(C:T) if either of the contents of cells X and Y = "E"
The code needs to look at cells X17 and Y17 and continue looking until it gets to X216 and Y216
So - If X = E but Y does not, then the row range C:T must be cleared.
If Y = E but X does not, then the row range C:T must be cleared
If both X and Y = E, then the row range C:T must be cleared
If the letter E does not exist in either X or Y, then the row must be left alone
View 13 Replies
View Related
May 30, 2013
I have a client list/table that gets new data each day. (ALL) - see file
I need to create separate lists/tables on new sheets from the main data sheet (ALL) base on certain conditions/criteria...
On the (ID) sheet I need all the clients that have ID numbers to display, on the (Reg no) sheet I need all the clients with Reg numbers to display... etc.
Like I said that the main data sheet grows each day and the lists/tables on the other sheets needs to automatically include all the new clients that's entered.
Is there a way to do this using formulas or do I need code to do this?
View 2 Replies
View Related
Mar 13, 2013
Is there a way to automatically create list of the Tab names, and have it automatically update when new tabs are added?
View 2 Replies
View Related
Jul 12, 2006
I am trying to compile a list from a spreadsheet using one specific criteria but taking information from other columns on that sheet on rows where the criteria is met. Below is an example which will hopefully explain better what I am trying to do:
Example
ABCDE
1JonesRedDayCarGood
2DaviesGreenNightCarExcellent
3EvansBlueDayBusGood
I want to create a list of all those rows where "Good" is in column E. But I only want to include the data from columns A,C and D in the list for the rows that meet the criteria.
I have probably made this as clear as mud.
View 9 Replies
View Related
Jan 6, 2006
Can the information in say cell b:1 be based on the information in A:1? Example: If A:1 has the name Fred Smith then I want cell B:1 to say Nurse. Column A will have a drop down list of names and column B will have a list of Nurse, EMT or Paramedic. When someone pick a name from the list the information in column B will automatically be filled in based on the information in column A.
View 7 Replies
View Related
Apr 10, 2014
I need to clear the contents of a columns G and H starting 11th row based on what is there in F column. The macro should check for last non-empty cell in column F starting F11 (assume it finds F30 to be last non-empty cell), then it should clear the contents of G11 to G30, H11 to H30.
View 1 Replies
View Related
Jan 21, 2009
I have created an Excel spreadsheet teachers schedule for a small school with 8 teachers. I have assigned a number to each teacher (1 - 8) so that a number typed in a cell in Column E will cause a teachers name to appear in a cell in Column G. The ranges are E3:E20 and G3:G20. I hope to find a Macro that will display each teachers name in a different color.
View 5 Replies
View Related
Apr 19, 2007
I have a list that is streets and addresses. All contained in column A. Cell A1 is the street name and then Column A3 is the street number. This repeats down column A for almost 1000 street names. I need to fill column B3 with the street name, as well as B4, B5, B6, etc until the street name changes. I was trying to do this with an if..then but couldn't get it to work. I also tried to work on a do.. loop looking for the change from a string to number. But my programming is a little rusty. If anyone can help I was be forever grateful. I mean the alternative is to sit here and copy and paste all day.
ABBEY CT
1700
1700
1703
1703
ABERDEEN LN
1305
1313
1321
1321
View 4 Replies
View Related
May 27, 2014
Eng list.xlsx
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
View 8 Replies
View Related
Jan 4, 2013
I've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
list-test.xlsx
View 6 Replies
View Related
Jan 20, 2008
I am quite new on this forum.
I would like a dynamic range to be multiplied with 2. The Range is always start in cell H14 to P?
I have multi checkbox in column F,
So here is an eksampel: If checkbox in F4 is true then Range H4:P4, should by multiplied with 2 else if the chekbox is FALSE, then only multiply with 1.
I want to entry data in the Range H14:P? by this code
Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
View 9 Replies
View Related
May 2, 2008
I have been perusing various Excel sites in hopes of finding a macro or function that can create columns based upon two dates input into specific cells. My ultimate goal is to use this macro or automated function so that when the user inputs the dates, the columns would automatically be created for them, rather than having them do it manually.
ideally, the 'start' and 'finish' dates are named cells and the macro could determine the difference between the two and automatically insert columns to the left of a ' Totals' column.
View 9 Replies
View Related
May 16, 2008
I am working on a spreadsheet for a client. I am using Excel 2007 and she is using Excel 2000. I have created a emplate for her to enter survey data into. Using the following code, I have created combo boxes for the drop-down menus so the client can tab from one cell to the next while entering data:
Option Explicit
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub.....................
View 6 Replies
View Related
Jun 12, 2014
I have two lists of values in separate columns with condition in adjacent cells, all on the same sheet
List 1 consists of numbers from 1 to 12, column to the right is either blank or Free
List 2 consists of numbers from 13 to 24, column to the right is either blank of Free
List 1 starts in A2, B2 is either blank or Free
List 2 starts in D2, E2 is either blank or Free
In column G, from G2 i want it to list all values from List 1 and List 2 where their status is Free
View 14 Replies
View Related
Feb 6, 2014
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".
View 4 Replies
View Related
May 28, 2014
The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?
Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
[Code]....
View 2 Replies
View Related
Apr 30, 2009
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
View 5 Replies
View Related
Jul 11, 2013
Macro that could look at a row and take the values of two cells, combine them and then create a range name for a third cell in the row.
For example; for row 5420, in column C there is the word Florida, in column D there is the number 6235, and in column F there is a sentence or two. Is there a way to automatically create a named range for the cell of column F that would be named FLORIDA6235? And if so, can the macro do this for every row even if the word and/or the number changes.
I have roughly 28,000 rows and nine columns that I am working with. That's why I was wondering if there was a way to automate this. There are 10 states and I don't know how many different numbers attached to the states, however there are many state and number combinations that repeat, so there would be several rows with Florida in column C, 6235 in column D but a different description in column F.
View 9 Replies
View Related
Jan 29, 2014
I have 2 worksheets with data. One of them is a bench mark template or expected times to complete devices based on batch size and product. The other worksheet is a report generated that shows the actual time techs worked on batches of equipment by product type. I am trying to automate a report that I will need to run every week. I have most of it automated but need some calculations. I want to calculated the expected time to complete the work, based on the data provided in the benchmark worksheet.
I do not want to use an average, and I want to use the expected time of completion based on batch size and product relative to the tech report. Column T is highlighted and where I want this formula to be entered. So if the Total Batch items is 1, then the expected calculation uses that time relative to the product and then multiplies that number to the batch size. Or if the batch size is 3, then it uses the time relative to a batch size of 3 and then multiplies that number by the batch size on the tech report and is entered in the highlighted column.
I have attached the workbook I am working with : Production Report.xlsx
View 9 Replies
View Related
Feb 28, 2013
I have over 1000 of rows of data and I want a formula or a Macro to create 5 new records (rows) based on the following:
Below is how data in master sheet1 looks like...
STORE#ZIPCODEID #1ID #2ID #3ID #4ID #5
10134567A100B200C300D400E500
10245678A200B300C400D500E600
...
...
With the formula or a macro I want it to look like this in Sheet2 in the same file...
STORE#ZIPCODEID #
10134567A100
10134567B200
10134567C300
10134567D400
10134567E500
10245678A200
10245678B300
10245678C400
10245678D500
10245678E600
. . .
. . .
. . .
As you can see there are 5 records per STORE# listed in Master Sheet1.
View 3 Replies
View Related
Jan 9, 2007
I have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
rng = Range("F" & Target.Row & ":M" & Target.Row)
If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value = "Yes" Then
Application.EnableEvents = False
rng.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub
View 9 Replies
View Related
Feb 16, 2007
I am have a spread sheet (sheet 1) with a number of columns and what i am looking to do is see all the people that are participating in a certain stage in the process that is not common, So what i am wanting to do is copy the names and the corresponding number of these people (on sheet1) over to a new worksheet(sheet2) based on a yes or no criteria further on in the spread sheet(sheet1). sheet 2 has additional columns that the workers here would need to fill in.
The criteria is in sheet 1 cells Y2:Y2000
The number is in sheet 1 cells D2:d2000
The names are in sheet 1 cells E2:e2000
If its at all possible i would also like to then make the names of those people on sheet 1 a hyperlink to the additional information
View 4 Replies
View Related