I have set of data in excel which is basically a table of workload and I need to be able to select someones name from a dropdown list and all the other names are removed.
I've attached a screen shot of an example, I want to be able to select any name from a dropdown e.g. Select Trevor and then only cells which contain Trevor are shown (i.e. the other names disappear). BUT all the jobs 1-10 and Stage 1-3 have to stay even if Trevor doesn't work on them.
I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?
I have a Excel Table, everytime I type a new record in the last row all the fields above that has drop-downs and formulas get's carried over and displays, except for one field which never tends to display a drop-down. I end up having to copy and paste the drop-down from the above row. I don't like to do this.
I have some data validation drop down lists in excel, I can tab through all the lists but I have to press alt + down arrow to show the list, Is their a way it can be automatically shown as soon as I focus on the tab.
How can I get the dropdown list to not include blank cells in the list reguardless if the sort filter is used or not.
Sheet 1 (STATS) is the dropdownlist Cell B12 Sheet 2 (Orders) is the data, I cant convert to tables because the cells are active and storted Column B2: is the data named.
I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'
I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'
I'm attempting to familiarize myself with pulling data from an online database into spreadsheets for manipulation. I'm relatively familiar now with pulling tables using webquery, etc. but my next feat is accessing data from sites which require some "input" before retrieving the desired data set.
Currently, I have a site which contains information and prompts for the "year" of information in a dropdown box. I've attempted to do this as indicated below, and was able to "select" a year, however the page doesn't load the data like it would if I were to manually click on it.
Sub GetEmissionsData() Dim ieApp As InternetExplorerDim ieDoc As ObjectSet ieApp = New InternetExplorer ieApp.Visible = True
[Code] ........
Separately I've tried setting the year using another method, but this just give an error
Sub GetEmissionsData() Dim ieApp As InternetExplorerDim ieDoc As ObjectSet ieApp = New InternetExplorer ieApp.Visible = True
[Code] .......
I'm not sure if the error is due to some issue with my code - or if "Label1" isn't the correct label for the dropdown / combobox on the site. I didn't post the site source on this page - but the URL indicate in this post is the one I'm interested in.
I'm using Excel 2010. I have developed a calculator (attached) to make life easier for my colleagues. It works perfectly fine. But one thing keeps bugging me.
In Cell C3 - You have the option to choose "Daily" or "Weekly" (Drop Down). This selection effects your selection for Cell C6.
If you choose Daily in Cell C3 then your options for Cell C6 are restricted to select/enter an amount between £0.00 and £6.00.
If you choose Weekly in Cell C3 then your options for Cell C6 are restricted to select/enter either £0.00 or £12.00.
This works perfect.
However, if I select "Weekly" and then select " £12.00", all my results are perfect which is exactly what I want BUT if I then go back up to C3 and change "Weekly" to "Daily", the £12.00 in C6 remains (though the data validation for "Daily" is restricted to £0.00- £6.00) and all the results are consequently wrong until C6 is changed.
The obvious thing is to delete the £12.00 from Cell C6 or introduce a "clear" button BUT I don't want my colleagues to do this as it leaves the possibility of error and since we are dealing with money, I can't have it.
So what I would love and what would complete this calculator and deem it ready is if every time Cell C3 is selected (i.e. from the drop down list you choose Weekly or Daily) it as a result resets Cell C6 to £0.00.
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.
I want to select more than one drop down box at the same time, and copy them as well as rename the cells they refer to.
For example: in A1 there will be a box with LinkedCell set to A1 and ListFillRange set to B1:B5. I now want a similar box in A2, with A2 as LinkedCell. However, when I copy past the box in A1, it still refers to A1 and B1:B5 (the last part is ok). I can easily change A1 to A2 by just changing it in the properties, but the thing is that I also need these boxes in A3:A100. Is there any faster way of changing this?
Also, the ListFillRange might expand in the future. Therefor I would like to know if its possible to select the boxes in A1:A100 and change the ListFillRange to B1:B6 for all at once.
I am trying to create a chart that would show all of the Amount values for each Size value that is in the example data below. The Size column has fixed values. In my example the values are: .5, 1, 2, 5, 8, 13 and 21. The Amount column data is variable. Any of the static Size values can be associated with many Amount values. I was attempting to have the static Size values on the X (horizontal) axis and the Amount values on the Y (vertical) axis. I would like to have all of the Amount values for each Size value be shown directly above the corresponding size value, in one vertical line. See example below.
I an creating this type of chart in Excel 2010.
Note, I am still new to this forum and don't know how to post pictures or Excel data into my posts. I am also using dots (periods) to provide separation in my examples.
I have a dropdown box to choose a category. When the category is chosen it displays the relevant course names which corresponds to that category (this could be 1 course up to 10 different courses). Along with that course name it gives the relevant course overview.
This works exactly how i want it to. (thanks to Zbor from a previous thread)
I now want to expand further and give the corresponding course objectives for each course that shows up in the results.
I have attached a workbook as its much easier to see the dropdown box working.
Sheet 2 is the working sheet and sheet 1 will contain all the source data. I have highlighted the columns affected in Blue Blue column on sheet 1 will have the course objectives on the same line as the course they belong to Blue column on sheet 2 is where they will be displayed when the correct category selection is made.
The course objectives are unique to the course title and course overview.
In my IE site there is a field which is a dropdown field with the value 'Not Known' and 'Specified Date'. If I select 'Specified Date' another field named 'Date' becomes visible where I can give a date. I was able to ensure that the value 'Specified Date' is selected using the following statement:
IE.document.GetElementById("startDateBasis").Value = "SpecifiedDate"I also was able to populate the date field with the statement: IE.document.GetElementById("startDate").Value = SOA
Where SOA is populated with the date string I need to be given as input in IE.
My issue though is that, eventhough the data is provided by the macro the field 'Date' is not visible. So I have no way to know if the input was succesful until I submit the screen. I think I read somewhere that this is because of the way datebasis is populated.
How to get the field 'Date' made visible if datebasis is given as 'Specified Date'?
I've made a drop down list and when I select multiple cells and choose from the drop down list, it only fills one. Here's pictures:
So in that first picture, I've selected multiple cells. But when I drop down the menu and click my selection, this happens:
Why is it that all the cells I've selected does not fill with the option that I chose from the drop down. I highly doubt that I have to manually click all the ones I want to fill with what I choose from the drop down list.
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
I have 5 cities and each city has a set of data in separate tabs. Right now i have data pulled for all 5 cities in a single sheet but its too cramped up..
I want to use a drop down list (data validation) and if any of the 5 cities are selected in the drop down, the data from that particular city (tab) should be pulled up. This way my sheet size will be reduced 5 time which will look good.
A common v lookup wont work because the data has to be pulled from 5 different tabs for 5 different cities.
Basically on a summary page, on sheet 1 say I have a formula which picks up the unit cost (I have this as an array across 5 years)
What I then want to do is multiply this by a volume driver. I have a list of these drivers on a seperate page, so for convenience lets say DRIVER_01,DRIVER_02 etc. These are also 5 years arrays.
So what I want to do it be able to select which driver I want to use for volume on the summary, from a drop down box I have set up, and then the formula pick up that named range, and not just recognise it as a text cell.
I need the ability to change the driver in the future, hence the drop down box. I can also get the unit array part to work fine, I'm just struggling to get this driver array to work..............
I'm trying to make a spreadsheet that can be used to easily build a collective list of steps, for a user to read and follow line-by-line.
I want a source sheet of "steps" that I can change over time, and the resulting tabs that reference the source sheet get updated/populated automatically.
I've pieced together some VBA code from other sources, which kind of does what I want it to:
VB:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row > 1 And Target.Column = 1 Then Dim SourceSheet As Worksheet Set SourceSheet = ActiveWorkbook.Sheets("Steps") Dim TargetSheet As Worksheet Set TargetSheet = Target.Worksheet Dim c As Integer Dim Source As Range
[Code]...
But there are some problems:
1) The data auto-populates into a row. It would read better if each step was in the same column, meaning rows would need to be automatically added upon selecting something from the drop-down list. The number of rows that need to be added vary based on the number of steps in the source sheet, for the selection made from the drop-down list.
2) If you make a change to the source sheet, my goal is to have the other sheets referencing the source sheet's lists of steps update automatically, so you only need to update the steps in one spot and everything you've built from them gets updated instantly. Currently, you must select a different choice from the drop-down list, and then change it back, before it populates the "new" steps from the source sheet.
This is my first time using VBA.
What I have so far is attached: testAutoPopulate.xlsm
I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.
Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.
I have a price list with type & price, I want do a drop down list for type (this is solve), so when i select list at type, the cell I want it show price.
I have 2 related columns with one to many relationship, on a tab populated with some data. On another tab I'm referencing these 2 columns. When I populate one column I would like a drop down showing all the related data from the 2nd column of the first tab in the 2nd tab. I'm unable to do a named range as my first column has spaces in its name.
I have some rows in my sheet locked and some unlocked rows. When user clicks on a unlocked row I should be able to enter some information. I am suppose to populate some dropdowns in that row for him to choose. How can this be done??
I know when a user clicks a row worksheet change event is call. I can write a function in the worksheet change event to populate values in the dropdown. I went through some articles, and they say it can be done by hiding and unhiding rows.
How do i make a drop down list in a cell show Select One in the cell when the cells previous contence (Option selected from drop down list) have been deleted. E.g Cell E5:E400 must show Select One in the cell and when clicked on show drop down list I dont want a input message box or error box i can do that with validation
Is is possible to get a dropdown list to show the actual characters as they appear in windings or marlett as shown in the source? When I try I am just getting a load of u's with accents and dots above.
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
