Create A Dropdown List From Another Worksheet
Nov 3, 2008
I am trying to figure out how to create a drop down list on one page of a workbook with the list of items for the drop down list located on a separate worksheet within the same workbook.
I have a workbook with one spreadsheet with a list of items on it (sheet1/ingredients) and the rest of the worksheets would have the drop down list of items from sheet1/ingredients.
I have been trying to use the validation method with no luck - I just can't seem to get the source path right.
If possible I would also like the drop down list of ingredients to be self updating - the ability to continue to add items to the list and show up in the drop down lists.
When the value in the drop down window is selected I would really like / need to have the data populated in the rows to the right of the drop down window.
While asking is there any sort of auto complete so that one can start typing in the box and the ingredients that start with those letters show up to select from? (Such as if type EVA or EVAP --- then all words starting with EVA or EVAP appear instead of the entire list?)
View 14 Replies
ADVERTISEMENT
Sep 11, 2013
I have two worksheet. One worksheet is a form where in the Field Door No. should be a dropdown list. The list will be retrieve from worksheet Source. Now upon selecting a specific Door No. it should also autopopulate the respective Type, Brand, Serial No., and Type.
First thing i need to know is what should i do on Cell B1 worksheet Form for it to be a drop down list retrieving the Column A on worksheet Source.
Second, upon selecting for example ATC0009 on the drop-down, it should auto-populate like these
Door No. ATC0009
Type AT Crane
Brand A
Serial 43679
Type Crane
View 14 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
Feb 11, 2014
I would like to know if there's a way to create a checkbox list in a cell that influences other cells:
For example, I would have column A as "Materials Accepted", and each cell would have a dropdown list with the options "Wood", "Drywall", and "Metal", in checkbox format. Then to the right of that, I would have each of the Materials as actual columns so that column B is "Wood", column C is "Drywall", and column D is "Metal".
The way I would want it to work from there is that whatever options I select from the dropdown, it would then put a TRUE flag in that column. So if I select "Wood" and "Metal" on the dropdown checkbox list, columns B and D would both read TRUE on that particular row.
Here is a screenshot of a mockup I did in paint - [URL]
Basically on each row, I would select a number of Materials, and the following columns for those Materials would populate TRUE for the ones that I selected on that row. This was just an example, but I would scale this up to around 30 Materials for hundreds of rows/entries.
View 1 Replies
View Related
Jun 10, 2014
Attempting to use INDIRECT function to create dependent data validation list (Type). The tricky part is that the drop down options are dependent on the values of two (2) cell values (Brand and Country).
I have used the INDIRECT function before but dependent on ONE cell value only.
Have attached an example to better explain.
Example.jpg‎
Example.xls
View 4 Replies
View Related
May 6, 2014
I am trying to create 2 drop-down lists (based on named ranges), where the contents of the second selection change based on the first selection. On one sheet, I have a list of equipment that is quite long, but is broken up by categories. I have named ranges for all the category names, and the equipment in each category. On the other sheet (user side) I made the first drop down the list of categories, and would like the 2nd drop down to be the pieces of equipment that correspond with the chosen category. I have tried using data validation and nesting IF formulas, but it only works for the first 4-5 categories, bc my formula is longer than the Source field allows in Data Validation. Any other way I can make this work? Can I use a macro for this? If so, how to write it as this would be my first macro
View 9 Replies
View Related
Apr 1, 2014
I need to create a 2-stage dependent drop down list.
Stage 1:
In cell G7 (on a tab called 'Data'), I have created a drop down list based on a range name called 'Region' .
Stage 2:
In cell G8, I need to create a drop down list which is dependent on the value chosen in G7.
Problem is, I will need to create a dynamic range name here which needs to vlookup or match the value from G7 in a dynamic list of data (the data is on different tab called 'RCA Data' and is spread over columns A:E with row A for headings - the data will go down an undetermined number of rows)
Every time the value in G7 matches a value in the 'RCA data' (the potential matches are stored in column
D) I need the corresponding cell value in column C to be added to the dynamic list. This list then needs to be available to choose from a drop down list in G8.
View 1 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
Sep 12, 2012
I am in the process of creating a database, and have used 'Data Validation' to create drop down lists in order to obtain information quickly from the database.
I have encountered an issue where if I have a list the resembles the one shown below, the drop down list will show spaces.
This is particularly an issue for myself and my database because there is over 1,000 items in the list, and there are countless empty cells within the list. I have tried sorting the list, BUT because the database is quite large, sorting it will disrupt other functions of the database.
Ideally, I would like the drop down list to show only entries within the list that are not empty so the drop down list is entirely relevant, and more user friendly.
View 2 Replies
View Related
Feb 18, 2013
I have 6 macros recorded and I need to create a drop down list of macros so that I know what kind of Macro I am using.
View 9 Replies
View Related
Oct 30, 2013
I have the following table with weeks to display for the holidays and my column H has a drop down list of the first column in the table below with the Named Range, "SNCODE.Season_Codes".
The other two Named Ranges are, "SNCODE.Season_Codes.Start" and "SNCODE.Season_Codes.End"
I need my column K to display a list of weeks from the first week to the last week. For example, the cell in column K for Valentine's Day should have 1, 2, 3, 4.
Basic 1 53
Promotional 1 53
Seasonal Spring 1 13
Seasonal Summer 10 26
Seasonal Fall 23 39
Seasonal Winter 36 53
Valentine's Day 1 4
Halloween 31 40
Christmas 36 48
View 6 Replies
View Related
Jul 13, 2012
I have a sheet with data that is associated with a month or season. I need to create a drop down list or check boxes on another sheet that will allow me to select the data associated with the month or season that is chosen.
For instance, on Sheet1, I have Summer-12 as the label in cell A1, and the data associated with it in cell A3:AH15. Winter-12 is in cell A18 and the data associated with it is in B20:31.
This goes on for 3 years of data.
I need a drop down list or check boxes on sheet2 where I can select one or multiple seasons and then the associated data for those seasons appear in order.
View 9 Replies
View Related
Aug 17, 2011
Is it possible to do a drop down list that allow user to select pre-defined selections but everytime when a selection is chosen, the list reduced?
Example,
I have can choose Apple, Orange, Durian and Mango.
The drop down can only select these 4 fruits.
So there are 4 cells in Column A1, A2, A3, A4.
A1 selects Mango and then A2 will only have 3 options to choose. So until the last cell, he can only choose the last fruit left.
A1, A2, A3, A4 are not selecting in descending order thus it could be A3 selecting first, then A2 and then A4.
I'm using excel 2007
View 13 Replies
View Related
Aug 5, 2014
I am trying to build my first worksheet macro. I have a dropdown list in a worksheet "Active Email" with "Yes" and 'No". When Yes is selected I want to cut that entire row and paste it in worksheet "Archived Emails". I feel I am close with the code but when I select the word from the dropdown, nothing happens.
Here is what I have so far.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "Yes" Then
Target.EntireRow.Copy Destination:=Sheets("Archived Emails"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub
View 2 Replies
View Related
Jan 12, 2013
I have a workbook that has 20 or so sheets that all roll into a summary statement via vlookups/sumifs/and other formuals.
Currently have around 300 vendors that need to have statements PDF'd which I currently do one-by-one.
Input range: VendorCounts$B$2:$B$1500
Cell Link: $C$1
Print area: A3:P437
Would like to PDF from print area each of the vendors in the drop-down list and save file to a designated location with the file name being the vendor name in the drop down window.
View 8 Replies
View Related
Feb 25, 2014
Is it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
View 8 Replies
View Related
Apr 5, 2013
I have a grade book with 17 worksheets 1 for each subject = each of the sheets are identical with the students names in col B and the data is entered manually in the corresponding cols along side the names.
What I would like is a vba solution to enter the data in a worksheet called 'datainput' = which would be identical in layout as the above subject sheets= and by the use of a drop down list with the names of the 17 subject sheets = copy the data in the 'datatinput' worksheet into the next available blank col in the worksheet selected from the drop down list=and with a command button erase the data in the 'datainput' worksheet ready to enter new data.
View 9 Replies
View Related
Apr 26, 2007
Trying to create a dropdown list to navigate to another worksheet . I tried iterations of the following code but am getting an 'object required' error.
If I can't assign the result to a string, how can I change the worksheet from the result of the dropdown list?
Sub DropDown7_Change()
Dim temp As String
temp = ActiveDocument.FormFields(“DropDown7”).Result
Sheets(temp).Select
End Sub
View 9 Replies
View Related
Nov 21, 2012
I am new to VB macros, I am trying to figure out how to color a tab Green based on a selection from a drop down list.
View 9 Replies
View Related
Apr 5, 2013
I've created drop-down lists in cells B3 and B4 (this is in worksheet 1), based on a column in Worksheet 2. I'd like Worksheet 2 to autofilter the data based on the drop-downs in B3 or B4, or both together.
View 2 Replies
View Related
Nov 28, 2013
I wanted to create a multiple drop down lists (using data validation) in column B (50 in all, every 3rd line) whereby, multiple, comma deliminated, results would display in each of the cells - for use elsewhere in the spreadsheet.
I found some code (as follows) which worked perfectly for me
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
[Code].....
As I mentioned above, I don't really understand the code and all I know is that the line about halfway down "If Target.Column = 2 Then" is defining which column (B) this will work in.
The problem I am having is that I need to protect the worksheet and the moment I "protect" the worksheet, the functionality of displaying multiple values goes away and the drop-down list reverts to only displaying one of the available values.
View 9 Replies
View Related
Dec 6, 2012
I have a drop down box on a worksheet and once I have selected the item I want from the list, I would like the price of that item to appear in the cell next to it..
View 2 Replies
View Related
Aug 16, 2013
I have a product list where each row of the worksheet has an image, product details and suggested lists/retails for each local currency.
Some products are not available in certain countries and each market doesn't care to see the other market info. (There are hundreds of items and dozens of countries.)
How can I create a drop down selector so that a user can choose their market, i.e. "Australia," and only the rows and columns relevant to them appear?
Can this be done without macros,? This sheet is distributed among 100+ people and needs to work for lowest common denominator.
View 3 Replies
View Related
Mar 27, 2009
I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.
I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.
View 5 Replies
View Related
Sep 26, 2013
I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
View 5 Replies
View Related
Jan 13, 2014
I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
View 9 Replies
View Related
Mar 19, 2007
I 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 Related
Jan 13, 2008
I am getting a run time error 1004 during a copy-paste of a named range. I've read other posts and the help file and know it's related to defining an object, but I'm not clear on exactly what hasn't been defined.
Private Sub Worksheet_Activate()
Dim lCell As Range
Set lCell = Worksheets("Sheet1").Range("C65536").End(xlUp).Offset(1, 0) ' find first blank cell in the column
With Worksheets("Sheet1").Range("AllDates") 'filter duplicate dates
. AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"E7"), Unique:=True
End With
Worksheets("Sheet1").Range("Dates_Filtered").Copy Destination:=Worksheets("Sheet1").Range("lCell") ' fails on this line
Range("Dates_Filtered").Clear
End Sub
View 3 Replies
View Related
Feb 2, 2009
I am after some code to create a new worksheet called 'xxxx' in my workbook, and then list all the sheets within the workbook (excluding the newly created 'xxxx' sheet.)
View 4 Replies
View Related
Jun 6, 2013
I'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)
I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.
I've tried a few things to make it happen, but nothing has worked.
I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.
I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.
Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?
View 5 Replies
View Related