Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Count The Number Of Entries In A Data Validation Dropdown List And Display A Result.

I am trying to do 2 things involving data validation. 1) Count the number of entries in a data validation dropdown list and display a result. 2) Use an If statement to pull the formatting from a cell into another cell.

An example would be if Cell A1="A", then show the drop down list associated with cell or sheet "!ryanB2". Alternatively, I would like to also display the number of entries in the drop down list.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Custom Data Validation With Dropdown List
I have a data validation in a cell that works like this : if a control cell has the value "F" it validates the input with some dates in a custom list, if not it does nothing.

The formula looks like this :

It works fine but I also want it to display the drop down list when cell F2 = F, now all i can do is type a value and it checks if it's ok.
Can I do this without using vba ? I will send this to other persons and I don't want the macro warning to pop up when they open it.

View Replies!   View Related
Narrowing Data Validation Dropdown List
I want to make some of my data validation dropdown lists narrower, so they end at the length of their longest list entry.

I know NOTHING about VB and although I found some "free code" online that claims to do just what I want to do above, I have no idea how to paste it in or where to paste it in, etc.

View Replies!   View Related
Make A Data Validation Dropdown List That Also Enters A Range For Cells
Make a Data Validation dropdown list that also enters a range for cells, for example:

Kitchen ¦ High ¦ Upto 100 ¦ Easy ¦ Blank ¦ 2 ¦ Daily ¦ >6 hrs ¦ Medium ¦ >1 week
Lounge ¦ Low ¦ Large ¦ Easy ¦ Blank ¦ 0 ¦ Daily ¦ >6 hrs ¦ Medium ¦ >1 month
Bedroom ¦ Low ¦ Large 100 ¦ Easy ¦ Blank ¦ 1-3 ¦ Daily ¦ 1-3 ¦ Low ¦ >1 year
Bathroom ¦ High ¦ Upto 100 ¦ Easy ¦ Blank ¦ 0 ¦ Weekly ¦ 1-6 ¦ Low ¦ <1 year

I can make a dropdown list for:


But if I select "Kitchen" I would like it to enter the rest of the cells in that row:

Kitchen ¦ High ¦ Upto 100 ¦ Easy ¦ 2 ¦ Daily ¦ >6 hrs ¦ Medium ¦ >1 week

The total number of rows I use is 10, and the number of items in the dropdown list will be 30.

The data in the cells 2 to 10 does not change from that first set to its data menu name.

View Replies!   View Related
Data Validation List & No Duplicate Entries
I have a spreadsheet that needs to have a validation drop down list in three cells in every row (D, E, F). There are three items in the list, say, "One", "Two" and "Three".

My only problem is that there can only be one entry of "One" in each row.

View Replies!   View Related
Data Validation :: Display A List Of Values
data validation to display a list of values. The problem am facing is .. this list has a lot of names & it becomes difficult to scroll through when i need to search for a particular name.. Is it possible that when I press a key on my keyboard, the name starting with that letter is highlighted in the drop down list.. This is not happening automatically using data validation... Is there a work aorund for this?

View Replies!   View Related
Count In Filtered List And Display Results In Table On Right Of Data
I have a long list of delegates attending functions on different dates and need help with 2 problems:

A) List has filters by date/venue etc but I will want the table to be visible even when list is filtered.

B) I am using the following formula to count "=SUMPRODUCT(--($E$3:$E$728="Thursday 3rd September 2009"),SUBTOTAL(3,OFFSET($E$3,ROW($E$3:$E728)-MIN(ROW($E$3:$E$728)),,1)))" and this works but if I filter to another date then 3rd September shows 0.

View Replies!   View Related
Add New Entries To Drop-Down/Validation List
I'm trying to make a sheet that will allow users to either pick from a drop down box, or if the choice they want isn't there, to input data in the same cell. That part I have got. What I can't do is figure out how to have excel add that user input to the list of options, so that they next time the drop down box is used, that "user input" will appear as an option in the drop down. I used data validation to do all these drop downs.

View Replies!   View Related
If Number =1 Display The Result
I have a count for each site for certain cloumn headers.
But i want to collate these so that if there is a 1 in the column the it will output it with the column header. But there are 10 column headers and I would like to get a result that has all the columns with 1 in.

a b c d e f g h i j k l m n o p q (Organic Suites) (Inorganic Suites)
sitea 1 0 1 0 1 1 0 1 0 1 1 1 0 0 0 0 (O1 O3 O5 O6 O8 OS) (I1 I2)

View Replies!   View Related
Data Validation Dropdown
i put together this code. it works ok in 97 (but not if a cell is changed in col A by validation dropdown which i believe is correct)

it works ok for me in 2007(either thro dropdown or direct input) but user with excel 2003 cant get it to work at all, named range "drop" is just A B C D and is used in data validation dropdown col A

View Replies!   View Related
Create A Dropdown Box (data Validation)...
We've got a dropdown box (data validation - list) which is picking up data from a specific column. The data in the column is in the following format:

1 Sales
2 Contracts
18 Opening Stock
27 Purchase

When we select an entry from the dropdown box the cell obviously displays both the number and the text description to the right. What we need it to do is just display the number after selecting an item from the list. The text is descriptive and to make it easier for users. Is there a way of configuring the dropdown box (or another way) so that say if a user selects "1 Sales" from the dropdown it just displays "1" in the cell.

View Replies!   View Related
Disappearing Data Validation Dropdown
All of a sudden, the data vaildation dropdown boxes on a specific worksheet have disappeared. The list reference is still correct and the data validation dropdowns work just fine on other sheets in the same workbook. (BTW-I'm using Excel 2003 and the panes are not frozen).

View Replies!   View Related
Auto-Update Validation List When New Entries Added To Source
how to make all cells displaying an item from the list source update with new information added to list source.

Cell "A1" on worksheet 2 displays the 3rd slot of information from list source on worsheet 5. I want cell "A1" on worksheet 2 to update when the list source information in the 3rd slot on worksheet 5 is changed.

The list is set up by naming the columns on Worksheet 5 and is set as a validation list on the other worksheets. So on Worksheet 2 column "A" has the validation

AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".

List=(Coulumn A 's name on worksheet 5)

View Replies!   View Related
Display A List Of Cell Entries, Allow The User To Select Which To Delete
Hoping someone would be able to help me with this. I have a sheet (example attached) and this sheet has a number of varying description types in the W coloumn (usually approx 10,000 rows). This field is manually input so there could be spelling mistakes and/or non standard descriptions.

What I would like, if possible, is a macro that would look at the D column and if this is 'GENERAL LEDGER', it would then look at the W column.

An input box would come up, and would list the different descriptions it found in column W, and number them. It would only list each different description once.


1. Bank charges
2. Bank charge
3. Cash
4. Fund Custodian Fees
5. Fund Manager fee
6. Interest income cash account
7. Interest income cash acc
8. Miscellaneous expenses
9. Miscellaneous income
10. Other income
11. Sec lending comm
12. Sec lending commission income
13. Tax Reclaimable - Dividends
14. Withholding tax dividend

The user would then be able to type in the corresponding numbers, if possible seperated by a space, comma or semicolon and the macro would then run through the sheet and delete the entire row if D was GENERAL LEDGER and W was the selected description.

View Replies!   View Related
Dual Data Validation Dropdown Lists
I'm trying to create a cell with multiple dropdown lists in E4 depending on a value from another drop down list in B4. Both E4 dropdown lists are named cell ranges RV_MECHANISM and VALVE_OPERATING_MECHANISM_TYPE. I know how to do this in Data Validation using an IF formula, however, that's not quite "bullet proof" or "idiot proof" enough.

I'm attempting to create a macro to load only the appropriate dropdown list in E4 as called for by B4.

I recorded the Data Validation dropdown lists using the macro recorder, then searched through all appropriate posts I could find at MrExcel and attempted to write the appropriate macro code as follows;

With Selection.Validation
.Add Type:=xlValidateList
If Range("$B$4").Value = "RV" Then
End With


End If
End Sub

Compile error: End With without With

That's as close as I can get ... can one of you kind souls direct my macro coding paths and offer up the correct code for accomplishing this task.

View Replies!   View Related
Dependant Data Validation From Multiple Dropdown Boxes
I am trying to make a drop down list and based on what I select from the drop down needs to populate a different drop down list with information based on what I select.

For example: if I have Sheet1, Sheet2, Sheet3 and Sheet4.
Inside of Sheet4 (My Main Sheet) I have a Data Validation (Drop Down list) in Cell A5 and Cell B5

I would like to choose Sheet1 from Cell A5 and in that same sheet (Sheet4) in Cell B5 I want to have a drop down with a list of names that are located in Sheet1 in Column A:A.
Or If I would've picked Sheet2 in Cell A5 of (Sheet4), I would like to see a drop down with a list of names in Cell B5 (Sheet4) that are on Sheet2 in Column A:A . Is this possible?

So far I could achive it if information I want is located on the same Sheet. But I need it to work if info is located on different Sheets

View Replies!   View Related
Count Number Of Different Entries
I have a list of ID's, many of which appear several times. Is there a formula that will give me the number of different ID's? That is:


the formula would give the answer "3" for 3 different numbers in the 4 total numbers.

View Replies!   View Related
Count Number Of Entries In A Sheet
I have a list of words in one column, some of which feature more than once, in random order, i.e.:


I want to have a function that counts the number of times each word appears, so in the cell next to each entry for "Superman" it would say 3, for "Bird" 2 etc. If I add another "Superman" it should then change to 4 next to each entry. Also, I will be adding new words all the time, so the function needs to be able to cope with that too.

View Replies!   View Related
How To Create A List Of Items For Use In A Dropdown List With Data From A Different
I Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly

View Replies!   View Related
Conditionally Count Number Of Entries Per Month
I would like to count number of approved projects (Yes) and denied projects (No) on monthy basis. See attached file for more info..

If F17 is "No" on the month of Sept 09, then cell "O2" should count number of "No" in the month of sept.

View Replies!   View Related
Count The Number Of Entries On A Sheet That Match An Hour
I'm trying to Count the number of Entries on a Sheet that match an Hour. Looking through the availiable functions i found COUNTIFS, which is exactly what I want. However, when I try to compare the Hour values within the COUNTIFS arguments, there is an error.

This is the function that I figured would work here:

which should count all entries in column G where its HOUR matches the HOUR in E6 (all are time format). I do realize that in the example above there is only one comparison made and i'm using COUNTIFS instead of COUNTIF, but i'll be adding other comparisons to it once i get this first comparison working.

View Replies!   View Related
Count Number Of Rows With Unique Entries In 2 Columns
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.

I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).

I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.

All the cust numbers are unique so would I be able to use a wildcard?

View Replies!   View Related
Count Number Of Entries Meeting Condition/Criteria
I have a spreadsheet that has 35000 entries and i am trying to count how many material are assigned to a particular group but cannot get it to work. I have included a test spreadsheet to explain what i am trying to achieve

View Replies!   View Related
Assign Duplicated List Entries Same Number
I have a long list of items. I would like to assign the same number to each of the replicates. I've been trying to expand on this: = COUNTIF($A$1:A1,A1). which increments the number for each replicate, but don't seem to be getting anywhere. The attached file might make it clearer what I'm after.

View Replies!   View Related
How To Sum A Number List For Specific Result
Its my first post in this forum. Hope you would provided me proper guide line to solve my problem

Suppose I have a column of numbers. such as
I want to find the possible set (groups from these number) which adds up to a constant let 03333333
let for example
if I add
Now adding these numbers results in 03333333
whatever the possible groups, should be listed.

View Replies!   View Related
Count & Display Number Of Filtered Rows
I have some checkboxes that filter rows and a text box that should display the number of filtered rows:

If checkbox1 Then
Range("F2").AutoFilter Field:=6, Criteria:="<>0", Operator:=And
TextBox1.Text = "No of Records: " & Selection.SpecialCells(xlCellTypeVisible).Count

The text box is showing 1669380 record when there is only about 250.

View Replies!   View Related
Dropdown List - Automatic Data Convert
i am working on a long drop-down list which consists of a series of numbers, such as 1,2,3.... When users select 2 from the drop down list, the cell value will be automatically converted to the value "movie" that "2" is corresponding to.

The list behind the drop-down list is like this:

column A column B
1 music
2 movies
3 books
4 games
5 travel

View Replies!   View Related
Build List In Dropdown From Data Source
I have a workbook with two spreadsheets, one blank and one with a Header row and a list of employee names and random dates that will continue to be added to at the bottom per a copy and paste.

What I need is to have a form dropdown on the blank sheet (per a cell reference) that shows employee names and dates that has that name next to it in the data sheet.

The employee names are in three columns Last, First, and Middle and the dates are in a separate column (A).

I guess I need a VLOOKUP that can take the range and pull into the dropdown the employee name and every date associated with that name.

View Replies!   View Related
Card Count: Use Sheet2 To List Each Card Number And The Number Of Times It Appears In The List
I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.

View Replies!   View Related
Data Validation List Automatically Show The First Item In The List Rather Than Showing Blank
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.

View Replies!   View Related
Named List: Data Validation To Restrict The User To Only Selecting Values In A List
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.

Insert > Name > Create
Name: Fruit

Refers to:

When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.

View Replies!   View Related
Text To Number Conversion In Validation List!
I have 2 sheets;

Sheet1 (Also named "TicketNames") and Sheet 2 ("Day1").
On TicketNames I have 4 Columns the first 3 columns with about 5 names per columns.
Each list of five are given a specific Name ("One","Two","Three"). The last column is just
a list for Day1 dropdown menu. (3 names, "One", "Two", "Three").

On Day1, I have a validation list on A1 which will allow the user to select either ("One, Two or Three").

On B1, another dropdown menu, but it will only show the items related to the selection they made on A1.

My problem is that on A1, instead of having "One" show up, I want it to say "$1" for One dollar. But because it is a number not a text it won't allow me to use a validation list using this.

I'm using Excel 2007

View Replies!   View Related
Setting Widths List Box In Data Validation List
dear....can i set width in list box data validation (width in cell i set short).thanks

View Replies!   View Related
Force Choice From List: Data Validation List
is there a way to force so that a cell value can be only of what a validation droplist offers, hence users can not enter their own values and have to use the droplist for cell content selection.

View Replies!   View Related
Data Validation List, Disallow If Not In List
I am using Data Validation on some fields to create a drop down list from a named range! These fields however allow you to enter values that are not in the list.

Is there a way to make the cell have to be an entry from the data validation list?

View Replies!   View Related
Dropdown Box For Selecting A Category And Displaying More Than 1 Result
I have a list of questions that need to be filtered by the user and i was looking for an effective way to do it.

Column A is the question number
Column B is the Question Category
Column C is the actual question.

The user wants to use a dropdown box and select the question category. the results they will get are the question number and the question. there may be several questions within a category so all questions need to be displayed for the category that is selected.

(I havnt explored the use of a list as it gives the user the chance to display all the questions and i am trying to keep the viewing space that this list takes up to an absolute minimum). Also i have mentioned a dropdown box as these are very familiar to the user and so they could use it without the risk of user error. I have attached a list of the questions.

View Replies!   View Related
Macro To Count # Of Data Rows (excl Title &amp; Auto-sum Row @ Btm) And Put Result In L2.
Is there a way to count the total number of rows with data (excluding title row at top and Auto-Sum row at bottom) and put the result in L2?

View Replies!   View Related
Count The Number Of Categories In A List
I am building a tracking list for several items and have categorized them


I will have several of these and the list keeps getting bigger. I have a table above the list that summarizes these catergories and the count of them. How can I count each categorie and so that it updates each time I add one.

The formula would have to sumproduct or determine what categories are there and how many (unique list) and update when I add one.

Currently its a manual process of creating a unique list and doing a countif.

View Replies!   View Related
Count Number Of Each Item In List
I've in a sheet something like this:

Number Item
2 Apples
1 Orange
2 PineApple
5 Orange
1 Apple

I need to do a code to get how many Apples Orange, PinneApple or somethig is in the range

For Example:
3 Apples
6 Orange
1 PinneApple

The items are variable. It's starts in the second row, and finish in the next blank row.

View Replies!   View Related
List Data Validation
The idea is to create a liquor order sheet which takes items and prices from a liquor inventory workbook, which is part of the same file. i'm sure that you can do it all fancy with a macro, but unfortunately i'm not super experienced with excel. so i helped myself with what i know a bit -> drop down lists. I created an extra sheet which has 4 ranges on there... the liquor type (liquor, beer, wine), and the 3 sub classes for each type (red/white/blush/sparkling for wine, draft/import for beer and so on and so forth). On the inventory i named the range of all white wines "white", of all red wines "red" ..

On my order sheet i created a drop down list for type and created 2 more via "indirect" .. so when you select "wine" in the first one, the 2nd one will ask you for white/red/blush/sparkling and the last drop down then for the exact item according to the range on the inventory.

Hope what i did so far is understandable ^.^

Now my problem is that i want the unit cost to be copied when an item is selected. So you select "beer" in A1, "import" in A2, "corona" in A3... and the price for corona (which is written in a cell on the inventory sheet) should show up in A4.

I attached a screen of the inventory sheet so that you can see how it is structured.

View Replies!   View Related
Data Validation From A List
I have a workbook that uses data validation from a list.
The same list is used in several of the individual sheets.
Can one use just one list for different sheets, as I'm having to create separate list for each sheet, which when an overall change needs to be made it is easy to miss one.

View Replies!   View Related
Data Validation - List Box
Hello, could some one please help me with the following:

I have created a drop-down list for the range : C3:C65000 using Excel 2003 and Windows XP Pro. I did this using Excel's Data Validation Tool. The settings I used are as follows:

Allow: List
Source: =GROUPS
Check Box Ignore Blank is Ticked
Check Box In-cell dropdown is Ticked


Now all this works well, such that when I click in the cell range : C3:C65000, then a dropdown list appears - showing a list of all my groups.

However, when I click on any of the dropdown lists in this range, the width of the dropdown list is only as wide as the column. Column C has a width of 20.

What I need please is for the dropdown list to be as wide as the list of information showing in the dropdown list. If some one could please explain on how I can achieve this - that would be great.

View Replies!   View Related
Use Data Validation That Will Pull Data From A Source List
Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?

View Replies!   View Related
Data Validation List: Ignore Blanks Between Data
In column A I have a list of text. There are blank lines in between the cell that actually contain text. What I am trying to accomplish is create a validation list that will give me only the cells with text in them and ignore the blanks. For example in column A1:A7 I have the following text:




My validation list will return those names but will also give me the blanks in between the names.

Is there any way to ignore the blanks?

View Replies!   View Related
Allow User To Automatically Add Data To Data Validation List
I am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.

My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.

The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................

View Replies!   View Related
Data Validation List :: Blanks?
I have created data validation list which has 3 flags (Yes, No and Blank). Normally the user will select either Yes or No from column C but i want each time there is no value in either row A or B; i want the code to automatically to select Blank. So basically every time row A or B are blank i want the corresponding row C to be selected "Blank". See attached for more details.

View Replies!   View Related
Data Validation List From Other Sheet
Is it possible in data validation to have a list from other sheet? Example:my list is in Sheet of the same workbook.

View Replies!   View Related
Hyperlink In A Data Validation List
I have 3 sheets:
-Sheet 1
--is where all the sales people work from entering the details of the leads generated and the turnover for each deal
--Is a single list where sales people can populate with the names of all their leads A4 – A 339, at the moment it is listed as “client 1”; “client 2’ ....the idea would be for the sales person to type over “client 1” with the real name of the client “Jim Smith”
-Sheet 3
--Is a contact tracker for each client, (date and time called, size of project etc...)

In Sheet 1, column C is the “company/lead name” column, I have made this column a data validation list that pulls through all the clients names from Sheet 2 (I have named the range in sheet 2 “ClientList”).

My request:
I would like the clients’ names in Sheet 1’s drop down list to be able to be linked to the specific clients profile in Sheet 3.
--Eg. Jim Smith links to Sheet 3 Cell A54
--Eg. Sarah Mulley links to Sheet 3 Cell E237

View Replies!   View Related
Hidden List Of Data Validation
When I set the data validation list which data must need to place on the same sheet. Does it has any methods to hide the list to prevent the data list?

View Replies!   View Related
Data Validation List With A Chart
I am trying to create a data validation list with a bar graph. I have tried using the IF and Vlookup formula, but its not working for me. I would like to be able to select classroom # from the drop down menu and the bar graph update selected with student’s name and selected data information. I have attached the file.

View Replies!   View Related
Automatically Add To A Data Validation List ..
I'm trying to implement the feature from this great Ozgrid 'how to', and it's working for the most part, but the VBA falls over here...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$F$10" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction. CountIf(Range("Dept"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Range("Dept").Cells(Range("Dept").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub

Basically, I have a tab which contains 2 lists. One in Column A and one in Columb B. I want the corresponding dropdowns on a different sheet to allow entry of anything that's not on either of the lists.

I can get it to work if I place the lists on the same sheet as the main 'form', but I need to keep them on a seperate sheet (and as the 'how to' suggests the lists can be on any sheet, I thought it'd work 'out of the box').

View Replies!   View Related
Copyright © 2005-08, All rights reserved