I would like to create a list with reference to IF (E1:E150=1) list F1:F150
No Blanks. Cells E1:E150 will be either a 1 or a blank.
The new list will be created in cells Y5:Y whatever. List will then become a drop down list.
I have created the list but it has blanks between names and I just want the names....
I've got a large selection of lists on drop down menu's the second drop down is based on the selection of the first but when you select the second drop down it starts at the bottom with lots of blanks you have to scroll up to get to the top of the list how do I make this start at the top and ignore the blanks?
I've got the ignore blanks box ticked on the data validation but it doesn't make any difference?
I currently have a list on a worksheet named "Options". It is a named range entitled "Type" with rows A2:A500. Another worksheet uses "Type" as a drop-down list. Currently there are only five entries in the list - cells A2:A6. Other items will periodically be added to the list. That is the reason for the long range - up to A500.
In the drop-down list, all of the unused cells in the named range show up as blanks. Is there any way to not have the blanks show up in the drop-down list?
Or...
Is there a way to define the named range as a variable range to enable users to add items without redefining the range and have the added items automatically show up in the drop-down list?
I have a list generator that creates a set of data in a multi-column & row dataset. I would like a formula to create a list of the alpha data points only which excludes blanks and any errors.
Example:
AADD
#N/A
BB23EEFF
#N/A
#N/A12GG
CC
Results:
AA
BB
CC
DD
EE
FF
GG
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
I do not know how to create a drop down list to ensure the user inputs only the values from that list. Once I figure this out I would love to add a drop down calender for a cell as well.
View 14 Replies View Relatedis it possible to create a list of parts from a selection in one drop down box
ie
i have 1200 parts with descriptions etc in list . using a drop down box i want to select and create a workto list.
ideally this list would start on another worksheet starting on row 10
I'm able to create a simple User form Drop-down list that references a cell range list. ( as a test for myself) I have 4 separate macros I would like to run individually. Rather than create 4 separate buttons, I'd like to be able to select each one via Drop-Down list.
View 4 Replies View RelatedIn Column A I have a list of Products and in Column B is the list of corresponding prices. I'd like to create a drop down list with the items in Column A that would automatically fill-in the pricing from Column B next to it. I've tried using an IF statement, but my product list is too long.
View 5 Replies View RelatedI have ranges that are named Math, Reading, Science. I would like to create a drop down list in validation that chooses the range based on a value in a particular cell.
View 4 Replies View RelatedI'm using dependent drop-down lists. In the first box the user can choose a color which drives a set of options in the second box. (e.g. User selects "Red" and in the second dropdown they can then choose "Red A", "Red B", "Red C", etc.). Right now, if you select "Orange", it will still show "Red A" from the previous selection until the user selects the dropdown in the dependent box to refresh the list of options. Is there a way to make it so that second dropdown box will default to "Orange A"?
I tried writing some code to do this, but I can't seem to make it work. I've attached my file.
I am making a spreadsheet for food and calories, On sheet one i have a meal tracker, I want to have a drop down list in colum 1 that references the food list on sheet 2, column A=food, column B=Protien, column C=Carbs, column D=fat. The values from Sheet 2 columns B-D would then be inserted into Sheet A in the respective slots. I have fiddled with vlookup and tried numerous things but I can't seem to figure this out.
View 3 Replies View RelatedI have seen it accomplished where all worksheet tabs are arranged in a drop down list, and was wondering how to make that happen.
View 6 Replies View RelatedMy problem is i need to create a drop down box in excell, now i have a list of names,
Name 1
Name 2
Name 3
Name 4..........
I need all thos enames in a drop down list but i think i keep doing things wrong, I am doing it like this,
All names are in cells A23-A33, i have highlighted them and named them (Names) using the name box in the top left corner, i have then gone to Data, Validation, List, Then i have sourced it to "Names" and clicked ok, this is where my problem is. It Makes all the boxes for Name 1 - Name 10 Drop Down Boxes... And when i click on any of them it gives me a list of all the names, when i click on one of the Names that name appears in the drop down box but the original Name dissapears from the box completely!
Name 1
Name 2
Name 3
Name 4
Name 5..................
I am trying to create a drop down list so that when the user choose Option A, Outcome A populates the cell.
So for example, I am looking to show a list of all Grades within the company, when the user selects a grade, the salary is populated into the cell.
I have a spreadsheet called contacts which is as it says, I have another sheet called project management, I want to have a drop down list which refers back to the contacts spreadsheet, it this possible as they are two different files?
View 4 Replies View RelatedI have a list of jobs being displayed using the following code. All sheet names that start with AJ, CJ and PJ within the workbook are how the list is created.
Sub ListSheets()
Dim sht As Worksheet
Dim lRow As Long
Dim rCell As Range
With Sheet1
Set rCell = .Cells(2, 12)
End With
For Each sht In ActiveWorkbook.Worksheets
Select Case UCase(Left(sht.Name, 2))
Case Is = "AJ", "CJ", "PJ"
lRow = lRow + 1
rCell(lRow, 1) = sht.Name
Case Else
End Select
Next sht
End Sub
what i want to do is create a drop list within each job sheet within the workbook that will display the names of the jobs above. Now the thing is I cannot choose the range like normal from data - validation - list as I will not know how many job names will be displayed so I dont know how many cells to include in the range.
I need to create a 'sub' list from a drop down list.
I have 8 categories in a drop down list in column A, but i need column B to also have a drop down list of sub categories depending on what category is selected in column A.
And once column B is populated, i need column C to auto populate with a value. All data is stored on the spreadsheet.
This is what I'm trying to do:
1. When a user clicks on a Drop down box and selects "Add New", I would like a Macro to run
2. I want this Macro to open up a Dialog/Text box to say "Enter New Information" where the user can enter in some text
3. The text that is entered by the user into this box will be added as a new entry into the Drop down box selection
My VBA knowledge is still at a beginner's level and I know how to get a Macro to run based on a cell value. However I don't know how to create a Macro for the dialog box nor do I know how to create a Macro for updating the drop-down items.
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies View RelatedI need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
View 4 Replies View RelatedI have 2 validation boxes. The first is a simple list of 5 options (based on
a 'name' range). The second is a filter of a larger list depending on the
answer in the first box. I have made my 'name' range as big as the largest
list but other lists are quite short. The problem I have it that the drop
down box is showing all the blank cells in my 'name' range.
The bigger problem is that when you select the pull down it highlights the
blank field first instead of the first item on the list so you have to scroll
up. This is a problem as staff think there is nothing on the list. So how do
I either not show the blanks or get the drop down to start at the top of the
list?
I have a drop down box in column N, however I would like the options in that drop down box to change depending on what appears in column M.
If column M Says "Lapse" then I want one drop down box to appear in column N, If coumn M says "NTU" then I would like column N to show different drop down options.
I'm using Excel 2010
I'm working on a workbook for tracking debt. It has 15 sheets. Instructions, Charts (for showing debt change over time), "Invisible" (this is where my chart data is, I plan to hide it), and 12 month sheets. On the Invisible sheet, for example, I have my data set up. I'm pulling the data from other sheets. In the screenshot, I'm using =Month12!P48 as the formula to pull my data from the other sheets. But, the user hasn't entered data yet for month 12, so the line chart drops to zero there. I want it to just stop and do nothing, until there is data in that cell. (I would prefer to use a line chart vs a scatter chart for this, but I have the same problem in both)
data.jpg
chart.jpg
I would like to exclude blanks from the source of a validation list, I have found a really neat solution for vertical list in this topic, I have modified it, but I have missed something, the formula is not working.
View 2 Replies View RelatedI have a table for gym members and each member is assigned a unique member number.
spreadsheet.PNG
I wish to create a data validation field that wont allow you to leave the cells blank or use the same code twice.
this was my attempt that failed: =AND(ISERROR(MATCH(A:A, A2, 0)) <>FALSE, A2<>""
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
Data is in a horizantal list, say row 2:
A2: SAM
B2:
C2: MARY
D2: JOE
E2:
F2: DAViD
i.e. like this:
SAM-blank-MARY-JOE-blank-DAVID
I am looking for formulas (not VBA) to compact this list by removing the blanks so the new will be placed in K2, L2, M2 etc. as follows:
SAM-MARY-JOE-DAVID
This is a good article but only works on vertical lists:
[url]
I think this must be fairly easy to do. I've found various posts here and tips elsewhere that indicate ways to do this, but I've not found one I could get to work.
I have a column with numbers in it. These numbers are formatted as TEXT (because they are 001, 002, etc). This list is generated by pulling the unique values from another column where these numbers are repeated.
In the following example, B has the original data. H has the same data with just the unique values. That is how far I have got. What I now want is what you see in Column I (the unique values with NO BLANKS in it).
B | H | I |
-------------------------------------------
001 001 001
001 002
001 003
002 002
002
002
003 003
003
003
I am aware that I can use an Advanced Filter (not that I manage to get that to work without it generate an erro). But this is not what I want, as I'd like the data in I to be dynamically generated such that if more unique values are added to B then H will reflect this (it does that at present) and then I will also reflect this.
I need to generate a list of items from a column of data which frequently changes. In the attached example, I need to list all entries other than those that are blank (NB - the data generates the numbers as text and the blanks as "").
View 8 Replies View Related