Is there a way to get a list of values in a drop-down list (using data validation) from another workbook? Excel doesn't let me reference another spreadsheet in the formula section under the validation criteria.
View 6 RepliesThe Room ID values in Column A are associated with the Room values in Column B. I'm trying to move the values in Column A Room ID to Column G Room ID by having excel look up value in Column C Room or Area #, compare it to Room, associate that with Room ID and automatically fill in Column G Room ID. There are 1000s of these so it's not possible to do it by hand.
I attached a picture where i had 2 different workbooks. In reality, I'm working off of 2 worksheets within a workbook.Excel Question.jpg
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
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?)
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
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.
I have a table with 150 or so Job Titles down the first column. Across the top row, I have 25 or so courses listed. There are Xs in the table indicating which courses are required for each job title.
This worksheet is intended to be used by individuals who will look up their job title to see which courses they must take. My goal is to simplify this process. I would like to create a worksheet in this workbook which has a drop down list of job titles. When the title is selected the data will transfer to a table on this new worksheet.
I've attached the spreadsheet I have and it's pretty self explanatory.
I have a worksheet with two dropdown menus. The first contains months from January to December, the second contains years from 2011 to 2025. Upon opening the worksheet, I would like the entries selected to match to the month and year of the current system date. I know this is relatively easy to implement but, unfortunately, the macro recorder is of no support here. How to refer to dropdown menus in an Excel worksheet and how to select a specific entry from the dropdown menu?
View 4 Replies View RelatedI 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.
I am working on excel based tool where I need a small piece of macro. I used two IF loops in the code.
The second IF loop is working fine. The problem is with first IF statement.
What I need to do is -
If E6 is Yes, then in the same worksheet named "Input Sheet", i need to change values of cells D14:M14 to zero and also
I want to restrict people to change this value. So, after changing the value to zero, I am hiding that row.
But if E6 is "No", then -
Unhide the Row 14 and allow users of this tool to change value of cells.
I believe the only issue is with statement - Sheets("Input Sheet").Range("D14:M14").Value = 0
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [$E$6] = "Yes" Then
Sheets("Input Sheet").Range("D14:M14").Value = 0
Sheets("Input Sheet").Rows("14").Hidden = True
[Code] ..........
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
I am trying to create a document that runs a scenario based on the two drop down choices selected that determines which worksheet in the workbook autopopulates over to sheet one based on the selection. I tried doing an if statement but did not have any luck.
View 1 Replies View RelatedI'm a little out of my element(normally program PLC's). I have always been lucky on searching others programs and have been real successful at piecing them together to fit the application I needed. The current workbook I am working on for my supervisor will allow him to keep track of personnel's weekly job assignments. The workbook and userform work fine when just using one worksheet.
What i would like to be able to do is use the "assigned to" combobox be the indicator as to which worksheet the data is saved to. I have tried to do it several different ways but have been un-successful. Have spent the last two days reading and searching for a solution but have yet to find or understand one.
I have created validated dropdown, which in turn I have used for conditional formatting to highlight specific consecutive rows based on the dropdown.
Is it possible for the worksheet to be positioned based on the dropdown chosen, so that the highlighted rows are visible without having to manually scroll down, looking for the highlighted rows?
If dropdown chosen highlights rows 50-60, I would like the page to automatically show rows 50-60 (not necessarily hide the others, but at least scroll to rows 50-60).
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 RelatedI'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 RelatedI am building a workbook which will be used to quote cleaning jobs and i want to add a drop down selection menu with hidden values which will calculate timings for jobs - for example
if you select DOOR WITH VISION PANEL i want that to have a hidden value that is the time it takes to clean e.g. 1 minute the auditor then enters the number of doors lets say 3 and the spreadsheet can then calcutale that 3 doors with vision panels will take 3 minutes to clean
I am trying to create an excel spreadsheet that calculates nutrition based on a dropdown menu. The values would change depending on the formula selected. How to I pull the data from sheet 1 and bring it in sheet 2 in order to calculate protein, carbohydrates and fat content. I have attached a copy of the excel spread sheet.
View 4 Replies View RelatedI have a sheet which shows values by month across several years (sample attached). There are two options in cell A2, 'Alive' and 'Dead'.
I'm trying to find a way, either using a macro or not if it can be done without so that, should the user select 'Dead' from cell A2, that all the values in B2:M2 and O2:Z2 are set to zero. However, I need some way of remembering these values because there may be instances when the user then decides to change Cell A2 back to 'Alive' (and they still need to be able to change the value in any cell at any time) and if they do that, I need the numbers shown in B2:M2 and O2:Z2 to be restored to their last set of values. I assume they somehow need to be stored in an array, but I have no idea how to achieve this in reality.
It should be noted that the same will apply to every row in the sheet of which there will be around 3,000, so this needs to work on a row by row basis and there will be 4 years worth of data, rather than the two on the attached sample.
I created a drop down list for products my company sells. Now I am wondering how can I get Excel to display the cost of the item in another column so I do not have to type in the cost each time I bring that product up.
I have searched google and this site but of course I can't describe my search well enough to find what im looking for.
I'm sure this has been explained I just can't find the answer to my problem.
I have a worksheet called "Lookup" with several dynamic named ranges (each is 1 column wide) including facility, department, shift, etc. On a summary worksheet in the same workbook I want users to use a dropdown or combo box (don't care what type) in cell B2 to select a facility and then based on their selection, copy the department named range data and paste it into the summary sheet beginning in cell A5 and paste the shift named range data into the summary sheet beginning in cell B5.
Example: user selects "AR Plant" from the dropdown or combo box and the data from the "AR_Rpt" named range is pasted into cell A5 and the "AR_Shift" named range is pasted into cell B5.
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.
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 RelatedI 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
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.
I need to be able to display a list of values from a drop down window. When the value in the drop down window is selected I need to have the data populated in the rows to the right of the drop down window. I have created the Drop Down using data validation in B12, I need C12 & D12 to auto fill with the info listed in H2 & I1 according to the item picked in the drop down.
I have tried to follow an example listed on the website but i keep getting a formula error.
Is it possible to assign values to names in a list, so that when you validate it as a drop-down list, you can select a name from the drop-down and it's corresponding value will be added to separate cell? Basically, I have survey questions which have five possible responses: Excellent - Very Good - Good - Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for Very Good and so on, so that when a response is selected from a drop-down, it's value appears in a separate cell (so that we can calculate a total and average score from the selections).
View 5 Replies View RelatedI want to control 2 cells values from a dropdown list (linked to another worksheet). How is it possible?
I can do it with embedded ifs as long as the number of values is not too big, but I want to get more extended.
see attached document - this is a stock record sheet. what i want it to do is when Bed is seleceted in column B - i want colum C to show the BedSize list which is in the lists tab. and the same with Chair i want it show ChairSize
View 14 Replies View RelatedActually I plan to create an eRequisition Form for our branch sites. My focus would be the consumable items of Lexmark printers. I want to create cascading drop-down lists based on data table (worksheet : All) as per attached. From lots of examples I've seen in this or other similar forums, I notice that in order to allow this cascading to work is by creating column with header name identical with the input in the 1st List. But I'm thinking, if I need to do that, then there gonna be hundreds or maybe thousands columns to create as the number of branches are expanding, so I think tht should not be practical.
I found examples that shows how lookup 1 value can return multiple values but I totally cannot think of a way to allow those multiple values to be returned as dropdown list rather than displaying all in rows of the worksheet.
What I need is that, in 1st list, upon selecting the branch, it'll populate all items related to the branch and when selecting the item, it'll populate brand/model list for the particular item only. The input in the dropdown list should not have duplicates. Been trying using pivot to do this with macro that enables auto refresh once any changes made in the table.
create this cascading drop-down lists just based on the table as per attached? I really want to avoid having to create extra tables to allow this cascading to work.
[URL]