Remove Items From DV List Once Used / Display Elsewhere / Reset List When Filling Across

Sep 13, 2013

It's a quarterly schedule for utility meter readers, divided up into days across the top, and routes/areas down the side.
There's a space with each route for the employee ID to go, depending on who's doing it.

Down the bottom of the sheet, there's a few empty lines for all the available employees who haven't been allocated to a route.

On the second sheet labelled EMP LIST, is all the employee IDs. It's also got the areas they work- it's for something I want to implement later.

I can put all the employees into a single column list, then use basic DV to give a dropdown menu by each route, to assign an employee to it. This is pretty straight forward. I've also been playing with this in combination with a countif, so that employees already assigned do not appear in the list.

This is about as far as I've managed to get with it.

I need to get the employees who aren't assigned to anything to appear in a list below the routes. I'm fairly sure this is just another countif, but I'm not 100% on the exact formulas.

I also need the list in the dropdown menu to reset for each day of the quarter, i.e. if I assign an employee to a route on the 30th day of the quarter, their ID will disappear from the list for any other route for that day, but will still be there for the other days of the quarter.

So far the only way I've managed to do this is by duplicating the employee list for every day of the quarter, which is going to get very cluttered, and also makes staffing changes difficult. I'm hoping there's an easier way to do this using one data set.

I've added an example of the sheet. It's one week and it's only got about 1/3 of the routes we would actually have on any given day.


View 1 Replies


Remove Matching Items From A Workbook Based On A Master List

Jun 16, 2009

I have Workbook A populated with skus from a report and I want to remove all rows in this workbook that do not have a matching sku in Workbook B. Each workbook contains only one sheet and the skus are in column 1 of both workbooks.

Also, I am looking for a good resource/tutorial for working with workbooks, worksheets and ranges within. I don't anticipate this being the last time I will be confronted with this and would like to learn more.

View 4 Replies View Related

Extracting Summary List Of Larger List Showing Only Items That Have Quantities?

Apr 11, 2014

It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.

What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.

I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.

I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.

View 8 Replies View Related

Getting Conditional Formatted Dropdown List Items To Keep Their Formatting In List

Feb 12, 2014

I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.

View 1 Replies View Related

Listing Number In A Cell To Display List Of Items In Another Cell

Apr 24, 2014

When inserting "1" in the cell under soup, i was thinking it will bring up a list of its ingredients and how much of that ingredient. The values are listed in the same spread sheet below.

The amount of the ingredient is in D50 (80grams) and the item type next to it in E50(onion)

Is it possible to put in "1" soup then it displays all the items in both those cells range example(D50-E50 to D60-E60)?
Is it possible to put the value "2" in soup and it shows double the measurements? example (160grams) (onion)

View 4 Replies View Related

How To Create A List Of Items For Use In A Dropdown List With Data From A Different

Mar 17, 2007

I Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly

View 10 Replies View Related

Count How Many Items Found In List Based On Another List

Oct 27, 2009

I have a list of names in B8:B200 (unique)

I have another list of names in I7:I15

I want to count how many names in B8:B200 that matches any of the names listed in I7:I15.

View 4 Replies View Related

Remove Entries From List In Comparison To Another List?

Oct 28, 2012

Ok, so lets say i have one list of names (cell:value)

A1:Value 1
A2:Value 2
An:Value n

and create a new list

F1:Value 1
F2:Value 2
Fn:Value n

I want to have a list nearby and i'd like somehow for a function that will remove entries that already exist on the first list.

View 4 Replies View Related

Use Values From One List To Remove Rows From Another List

Feb 24, 2007

I have two lists in the same workbook:

List 1) Contains customer contact information, including an account number. These account numbers may be duplicated in the list.

List 2) Contains account numbers of customers who wish to be removed from the first list.

I need to remove the rows from the customers list (List 2) where the account numbers match, and also copy those to another list for review. So far, I can manually choose and run some code to remove one particular account number only (eg 123):

Set FoundCell = Range("A:A"). Find(What:="123")
'Locate information to remove
Do Until FoundCell Is Nothing
Sheets(" Deleted List").Select


ActiveCell.Offset(1, 0).Select..................

View 3 Replies View Related

Remove List Values Not Present In Another List

Dec 5, 2007

I have two worksheets, one which contains a list of workstation numbers, and another which contains all of the workstation numbers and additional information on the workstations in the company.

I want to remove all workstation numbers and their information which are not present on the first list from the second list.

In the first w.orksheet each workstation number is in a seperate row, and in column one (seperate cells).

In the second worksheet, each workstation number and its corresponding information is on the same row, and each different workstation is on a seperate row with its information.

View 9 Replies View Related

Reset List Values

Nov 22, 2006

I have some list values scattered on work sheet having values [0,1,2,3] how can i reset all list values to 1 by using macro or any other option.

View 5 Replies View Related

Filling A List From The Top?

Jan 18, 2013

I have a table of information on a worksheet and one of the columns determines whether the item in that row is active (there will be active and inactive items in this "Main List")

On a seperate worksheet (in the same file) I want to only display the active items in a new table (Overview) and always ensure it has no empy spaces other than at the bottom of the list, so if row 1/2 in the Main List are inactive items then the first row in the Overview List would display row 3 from the Main List.

Ideally I would rather do it in a formula than VB if possible.

I've attached a file showing what I am trying to explain, where the Overview tab currently has the data typed in it should automatically populate based on column "I" on the MainList tab.


View 2 Replies View Related

Select Multiple Items In List And Then Print Those Items?

Dec 26, 2013

I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.

In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.

View 3 Replies View Related

Reset Dropdown List To First Entry

Feb 18, 2014

I am trying to reset various (data validation) drop down lists in a the dummy worksheet attached. Most of the code i have come across clears the cells completely, however i would like it to return to the 'select' option (first on the list) in the list.

I have come across this code, but it does not seem to work:

[Code] .........

I want the clearing code to run from the command button in the 'instructions' tab to clear the drop down lists in the '1. Inputs' tab.

Attached File: Reset drop down list.xlsm‎

View 4 Replies View Related

Reset Dependent Drop Down List

May 10, 2007

The only thread I could find on this topic was not resolved. I want to reset the value of a dependent drop down list (in H3) to the first valid choice when F3 is changed.

I have the following code that initially worked but no longer does.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errHandler
Dim DLRng As Range

If Not Intersect(Target, Me.Range("F3")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set DLRng = ActiveWorkbook.Names(Target.Value).RefersToRange
Me.Range("H3").Value = DLRng.Offset(0, 0).Value
End If

Application.EnableEvents = True
Exit Sub

MsgBox "Could not change dependent cell"
Goto exitHandler
End Sub

I now get the error message ""Could not change dependent cell" every time I pick a different item in the drop down list in cell F3. If I remove the error handler I don't get the error and the code simply doesn't work.

View 9 Replies View Related

Filling Drop Down List Of Mutiple Names

Jul 17, 2009

What I have is a spreadsheet with a names column A and purchasing information in columns B-AA pertinent to the name in A. Additional information pertinent to the same name in Cell A1 exists in B2:AA2, or 3 etc. depending on the number of purchases made by the name in column A. The spredsheet is well over 100,000 rows long. Is there a method of copying the names so that each purchase has the correct name associated with it in the corresponding (currently empty) cell in column A? .....

View 2 Replies View Related

Filtering Range And Filling Dropdown List

Mar 24, 2009

I have a list of 800 fund names that can be any one of 6 types of funds. It is easy to filter the names using the filter function, but I cannot get it to be a filtered list for my dropdown box. The dropdown box should only list those fund names of funds that is of a certain kind. I know I am missing something small.
If the accompanying data is used as an example, I would like a dropdown box with the names of all the I funds.

View 2 Replies View Related

Create A Scrollbar And Filling It With Data From A List

Oct 11, 2006

I can't seem to find the answer to this question. I have a sorted list of data 3 cells wide, 120 cells long. I wish to have this data put into a scroll bar so I may scroll the data, select, and it will output to a chosen cell.

I assume the ScrollBar control is the choice but since I have no experience with any controls, I am at a loss. I have thus far only managed to insert the scrollbar, click on the properties and then I'm ?.

1. How do I specify the range of cells to display?

2. How do I specify an output cell after selecting from the list?

I've attached a spreadsheet to view the data.

View 5 Replies View Related

Reset Data Validation List All The Time When Opening File?

Jun 11, 2014

When opening the attached file, for some reason, I have to again select the cells from column A in tab "Transactions" and go to Data Validation and select again LIST - only then the data validation from list resumes working. When I close the file after saving and open it again, the data validation does not work - I have to again go to Data Validation and again reselect LIST (making sure, of course, that I do not mess up Source) to have Data Validation list run again.

what is wrong with list validation when opening the file.xlsx‎

View 4 Replies View Related

Filling In Multiple Cells After A Choice From A Drop Down List

Feb 17, 2009

If I choose 1 thing from a list I want it to then fill in many cells with info.

For example: I have a color list to choose from, once I make my choice cells near it fill in with 1.5lbs yellow, 2lbs blue, 4lbs red,.....or what ever I have established as the "recipe" for my color choice. When I choose a different color from my list these cells each fill in with different values.

View 9 Replies View Related

Filling ComboBox List Is Duplicating/Retaining Old Values

Sep 29, 2006

I am currently trying to add some functionality to an Excel workbook and I have a combo box that I am unable to get the values to populate. On the same worksheet I have a command button. Here is the code I am using to attempt to populate the combo box:

Private Sub cmdSendSave_Click()

Call SendSave

End Sub

Private Sub bxLocation_Change()

With bxLocation
.AddItem "Mt. Hope"
.AddItem "Summersville"
.AddItem "Huntington"
.AddItem "Pulaski"
.AddItem "Coastal Bend"
.AddItem "Odessa"
.AddItem "Wheeling"
.AddItem "Hollywood"
End With

End Sub

View 9 Replies View Related

Rank A List Then Re-rank The List While Excluding Certain (or By Criteria) Items

Dec 30, 2013

I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items

Vendor Co
Cost Fee




View 4 Replies View Related

Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0

Jul 17, 2009

I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}

however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.

View 9 Replies View Related

Excel 2007 :: Combobox List Filling And Export Selected Data To Another Worksheet?

Mar 20, 2014

I have a combo-box in a Multipage control. Now I want to get the combo-box populated with list in 2 columns from 2 different consecutive columns from the same worksheet (Product List). Now, Once the list gets populated, user will be selecting his choice from the list, which is then needs to get copied to another worksheet (Order Placed). Both worksheets exist in the same or one spreadsheet.

I am trying to make it work with the following code. I am able to see 2 columns but with no data getting populated. I am working in MS Excel 2007.

[Code] .....

View 3 Replies View Related

Create A Reset Button That Returns Combo Box Values Back To The First One In The List And Clears All Cells At The Same Time

Nov 19, 2009

I have 4 combo boxes with selectable options and several cells for inputting data into. I'm looking to create a reset button that returns combo box values back to the first one in the list and clears all cells at the same time. I've figured out the cell reset as follows;

View 3 Replies View Related

Output Certain Items In A List?

Apr 30, 2014

I'm trying to accomplish the attached. This is just an example of what I want to do. There are no formulas in this spreadsheet.

Spreadsheet 1: Search by Number

Column A is a list of names

Column B is a list of numbers

Column C is a list of the names that correspond to the number "1" from Column B. Note that there are no blank rows between the names.

Spreadsheet 2: Search by Name

Column A is a list of names

Column B is a list of offices

Column C is a list of the names that correspond to office "Williams" from Column B. Note that there are no blank rows between the names.

I have used the index & match formulas to do this WITH duplicates or blank rows, but I would like to produce this without duplicates. I am willing to get as complicated as need be to make this happen.

View 3 Replies View Related

List Items That Has Been Booked Out

Jan 10, 2013

I have sheets with list of goods and in the last column I have a dropdown box you can choose - book in or booked out. See attached file.

On another sheet I want a list of all the items that has been changed to booked out.

View 3 Replies View Related

List Of Items In A Column

Nov 1, 2007

I have a list of items in a column. I would like to have a userform pop up with a list box (?) of all the items and I want to be able to select multiple items to perform an action on the row corresponding to the selected item.

I am thinking of other examples I have seen where there are two list boxes (?) and then arrow buttons between them. When you click the arrow the item moves from one list box to the other to see which have been selected.

I have never used list boxes before so if you have a link to some sample code that would be useful.

View 10 Replies View Related

Adding Items To A List Box

Jun 1, 2009

I have the following code used to add items of expired IDs in a listBox,, it actually works well for adding the ID type, but it doesn't add the details of the expired ID completely .. It adds only the details of the last expired ID (I think the code overwrites the details)

I hope that my problem is clear for all,

Can you please find me a solution for that?

View 9 Replies View Related

Dropdown List With More Than Eight Items

Oct 5, 2009

Hi all. I need to create a dropdown list that can handle more than the standard eight rows that data validation allows. My list requires 75 different items in the dropdown. Is there any way that I can do this?

View 14 Replies View Related

Copyrights 2005-15, All rights reserved