Limiting Number Of Items In A Range From A List
Nov 19, 2008
Here's my problem. In column B, i wish to allow users to choose 15 items for which i have created a list using validation. Each cell in the range B1 to B15 will contain one item. I have managed to allow them to select only 15 items by locking the other cells and allowing only the range B1 to B15.
However, i need prevent users from entering more than 5 identical items from the list. They can only choose a same item up to a maximum of 5 times.
Would you guys have an idea of how i can get around this problem? I really need to prevent them from entering the same item from the list more than 5 times and not only warn them.
View 10 Replies
ADVERTISEMENT
Aug 15, 2013
Im building a booking/diary system for work.My system runs 9am till 5pm, 10 min slots. The team is 4 people. So I have 4 columns, one per team member, and each row is 10 mins. 9am, 9.10, 9.20 and so on.
I want to limit it so that if say 2 people already have something in at a time it wont let another person type in that slot. I would prefer a none VBA sollution if possible due to the ancient computers in running it on, but not too fussed if thats the route I go down (as I suspect I will)
The sollution I was considering was having a COUNTA for each range, and if it is 2 or over, not let people type into the other slots. This would also let me add exceptions by using a COUNTIF to minus other characters if needed, but im not sure if I can make excel "block out" a time slot. My other sollution would be similar, but have them to autopopulate the empty cells with an "X" if they are 2 or over as thats what I use to block out slots (breaks, lunches etc)
My other problem is that if they then type into the cell, it will obviously type over the formula, which makes me think it will have to be VBA.
View 2 Replies
View Related
Oct 2, 2008
I basically have lots of lists of 12 cells. how ever some of those lists contain errors (e.g instead of having 12 numbers it will only have 10)
so instead of doing =sum(a1:a12)/12 i want to divide by the amount of numbers in that area in this case 10 so i can get an average.
View 3 Replies
View Related
Apr 25, 2007
In one sheet i have a list from cell a1:a1000. I have named this selection 'headkit'
In another sheet, i am using 'headkit' to make a drop down list in a cell.
At the moment a lot of headkit hasn't yet been filled and contains lots of empty cells that will be filled as time goes by.
The drop down list currently has all the items in the list including the blank cells therefor making a very large blank list.
Is it possible to limit the list to show only cells with an entry in them?
View 9 Replies
View Related
Dec 22, 2012
Can you increase the number of fields shown in the list so that you don't have to scroll through the entire list?
View 2 Replies
View Related
Feb 3, 2012
The setup of my worksheet looks like this:
Excel 2010ABCDEFG1Tch grpTchr Split27B-MA5AWAWAWAWAWAW37B-MA4CB1,JDCB1,JDJD,CB1MMF,JDMMF,JDMMF,JD47B-MA3SXGSXGSZBSZBSZBSZBTchGrps
A great chance to use the HTML Maker for the first time!
What I would like to do here is show how many of each value there is in the range.
So for 7B-MA5, it would say 6. For 7B-MA4, it would be 2 / 1 / 3, since there are 3 unique values and their counts respectively. Finally for 7B-MA3 it would say 3 / 3
A formula option would be great but I'll also take a macro option since it is a macro that generates these lists in the first place, so I could just add the new code to the end of it.
View 4 Replies
View Related
Dec 15, 2008
I work for a localizer of text heavy software. Our developers normally use a converter to pull text out of a file and insert it into the program. This text is normally input into an Excel file since it has to correspond with files/locations in the program.
Since the space on the screen of the program is limited, we often face character limits when inputting text into Excel. Now, one cell might hold up to 255 characters, and I could restrict that with data validation, but within one cell I need to restrict each line (with a hard return) to 30 characters to fit on the screen.
So for example I have a row of cells that look like: ....
View 9 Replies
View Related
Jan 21, 2009
I'm creating a spreadsheet (Excel 2003) in which a user enters data in several cells, each of which will permit different numbers of characters (to include spaces). For example, in the first cell, the user will be limited to 50 characters, in the second cell, the limit is 30 characters, and so on.
I found the data validation error alert feature, but want to give the user a cue that the entry is too long so they know to stop typing before moving to the next cell. If they only are alerted when they finish making the entry, they might not take the time to properly reconstruct the entry to meet the size limitation. I'm trying to make this more user-friendly.
Is there a way to set up the worksheet so the user knows that the entry is approaching the character limit? For example, each character filling an individual cell or having a display appear with a count-down for the number of characters remaining in the limit, or something similar.
View 6 Replies
View Related
Jan 9, 2014
I need to limit the number of Characters that can be inputted to the popup InputBox to just 31. How do I set MaxLength? Code I am using is listed below.
Code:
Sub NewRecipeSheet()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("1. Recipe Master Sheet")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
Application.Goto Reference:=Sheets("1. Recipe Master Sheet").Range("A1")
Range("A1").Value = InputBox("Menu Item Name?")
End Sub
View 5 Replies
View Related
Sep 9, 2008
I would like to make a list like the one below
1 - 50 mm 87 pieces
51 - 100 mm 99 pieces
101 - 150 mm 46 pieces
151 - 200 mm 42 pieces
etc..
I have a list with values all ranging from 1 - 200 mm. Which formula can I use to sum them up in the above 4 classes?
View 9 Replies
View Related
Jul 4, 2009
i had a user form with a Combo box,list box,text box and a command button. I need the code that works upon selecting:
1)An Item from the combo box should display the list in the list box.
eg: If Country is selected from the combobox then the list box should contain all the names of the countries from the country Column
2)An item should be added to the list in the worksheet when an item is entered in the textbox.
Like wise, when an item is selected from the combobox from "Delete Items List" all the items relating to the item selected from the combobox should be displayed in the list box and a choice to delete the items relating to the combobox item should be provided.
View 4 Replies
View Related
Jun 27, 2014
I would like to be able to limit a drop down list's contents based on a user's selection in a different drop down. I am using Excel 2003, and I know how to do this in Access, but I can't figure it out in Excel. Basically, I have a list of themes in one drop down. The user picks a theme, and goes to a second drop down which is a list of sub-themes attributable to that one theme and no other theme.
View 1 Replies
View Related
Aug 15, 2014
It is about finding the number of unique items in a range that contains empty cells.I can intuit the form of this:
Formula:
[Code]....
And many of the variations of it when there are no empty cells in the range.
For a range of that contains empty cells I've tried this:
Formula: [Code] .....
And variations of it. It does not work, and I don't know why...............however..........
I ran across this on another [SOLVED] thread and it does work:
Formula: [Code] .....
I would have never considered this plausible. What is the magic behind appending an empty string to the criteria in the COUNTIF function?
It strikes me that this strategy likely has application elsewhere, too.
View 14 Replies
View Related
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
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
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
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
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.
scheduledemo.xlsx
View 1 Replies
View Related
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
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
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
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
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
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
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
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
Mar 20, 2012
I built a form with a listbox that is populated from a list of products on a worksheet. I want the user to be able to type in a product and if it doesn't match the products already listed I want it to add it to the worksheet list of available products. I keep thinking that I should form an if statement within a loop but whatever I try doesn't work.
View 3 Replies
View Related
Mar 21, 2013
I am trying to move items up and down my listbox2 I have the code below however there seems to be an error with the moving down button
Option Explicit
'Move ListBox Items code
Private Sub BTN_moveselectedLeft_Click()
[Code].....
View 1 Replies
View Related
Jan 17, 2008
I have a list 10 of names in Column A. ( On sheet 1 )
I then filter column A to show only three specific names, or four names etc. . .
On Sheet 2, in cell A1, I would like to list the three filtered names from column A on sheet1
e.g. if the three names showing in column A ( Sheet 1) are: john, mary, jane . . . then on sheet 2 cell A1, I would like to see: "john, mary, jane"
View 9 Replies
View Related
Jun 15, 2009
I have a list of >50 contract numbers listed in cells A1:A350, with several of the numbers listed more than once. What I would like to do is, on a separate worksheet, list each contract number only once, in column A.
View 9 Replies
View Related
