Establish Dynamic Linkage Between Dropdown List And Final Output Tables?
Mar 13, 2014
I have done all the computation and final output was in the form of tables with different solution sets in one worksheet in an excel.
From the dropdown list (List of solution models), when I select one solution set it should pick that particular workout and populate the final output table in the new worksheet in the same excel. Firstly, I need to establish dynamic linkage between the drop down list and final output tables and then push the final table to new worksheet based on the option selected.
I have a successful dropdown menu that removes items that have already been selected through the dropdown list, however, I need it to be able to handle IDENTICAL items. At the moment, it can only handle unique items.
I'm trying to create a Rota Planner. I have a selection of shifts that need to be selected for each day. Some shifts are repeated (meaning that there may be 4 members of staff on the same shift). As ONE of each of the 4 shifts are selected, I then want only 3 to be displayed in the dropdown menu to be selected, until all of them have been selected.
I've tried to break apart the code that provides dynamic dropdown list for UNIQUE values but I'm tearing my hair out and getting nowhere.
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.
I'm trying to create a set of three dynamic/dependent drop down lists of date values, based on a set of imported data. Depending on whether the preferred choice is to list all dates in a range or just dates on the 15th, the available start and end drop down lists will change.
In addition, if possible I would then like to display a list of valid dates (not sure if this needs to be VBA).
The attached sheet shows/explains what I am attempting but here it is as well:
If B3 is set to show all dates in the month then:
the first dropdown option for C3 will be the first date of IMPORT (D2) the first dropdown option for C4 will be the C3 value, to the last date in IMPORT (D) column
If B3 is set to show 15th of the month then:
the dropdown for C3 will be the first date of IMPORT (D) column with a date of 15th the dropdown for C4 will be in the range of C3 to the last date of IMPORT (D) column with a date of 15th only dates of 15th will be listed in both C3 and C4 dropdownlists
General
the dates in IMPORT column D are taken from IMPORT column A, B & C, which will be imported and will always be date sequential the number of rows of date entries will change with every import but there will never be any blank rows between dates the values in IMPORT columns A, B & C will always be numerical, and can be referenced if required [ideally] the LIST OF DATES will be populated with the date range, based on the option selected in B3
I'm fairly familiar with Excel (2010) and I have also done some VBA but I'm new to dynamic, nested dropdown lists and I'm unsure what the most practical way is to achieve this.
I have a spread sheet that has three sheets, PROJECTS - TIMES - LISTS Time sheet.xlsx PROJECTS contains a list of 'In Progress' and 'completed' projects for work, with relevant other data, I.e. PROJECT No. / STATUS etc This will grow to 1,000's of rows TIMES will contain the amount of time a person spends on a PROJECT on a daily basis. This will grow to 10,000's of rows LISTS contain basic validation list for the first 2 sheets.
Part A I would like to have a validation drop down list for the PROJECT NO column in the TIMES sheet that only lists the PROJECT NO from the PROJECTS sheet where STATUS <> COMPLETED. I.e. I only want to display the PROJECT NO's for current projects.
Part B Even though I only want the PROJECT NO used, can the drop down list also contain other column's data for reference information only before one is selected.
I have a complicated situation with Excel since I need a formula that can SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet, need formula that can retrieve the same information.
When setting up a Direct Debit There are 3 options, the 7th, 17th and 27th. I've put these into a drop down list. I need this list to be slightly dynamic. I must allow 10 working days from the date of the arrangment before I can apply the DD.
To explain further. If someone were to want to set up a DD and spoke to me today. If they then requested the DD be taken on the 7th, I wouldn't be able to set it up until the 7th of December as 10 working days are required. I'd like the list to reflect this. I can do it in OO with a simple if / switch but excel is a strange creature and I'm not sure how to approach the problem.
From that I think I also need to calculate the number of weeks remaining in the current financial year. I think I should be able to do this by using the Today() function and some maths.
I am attempting to sum the final two values in a list. Each week, I add a new value to the bottom of the list. I would like the formula to calculate only the final two values, and update each week. For example, in Week 1, I would have the values 4,10,12,6 in Cells A1, B1, C1 and D1. The formula would calculate the sum of C1 and D1 = 18.
In Week 2, I would input the value of 15 into Cell E1. I would want the formula to now calculate the sum of D1 and E1 = 18.
I'm looking for a way for excel to output data to a cell dependent on what I choose from a drop down menu.
For example. I have a drop down menu with 3 options. 1,2 and 3. What I want is that if I choose option 2, then in cell A2 (or whatever) would output the value of 2, say "2222". Or if I choose 1, then A2 would output the value of 1, say "12345".
I got all three dropdowns to be dependent on each other, now however, whenever I change the value in the top Input Dependent Box M39, I would like M43 & M47 to appear blank. I want these two cells to appear blank until you select the dropdown which will allow you to select your values of choice.
Trying to set up a dynamic data validation, where each of the subsequent data validation boxes change based on the selection in the first data validation box (while maintaining 4 parameters within a cell, except for the last one).
I have used the IF-statements to determine the "Week's SUM" in H5.
What I'd like to do next is to determine a formula in the "Week's SUM" cells (H6,H7,H8) based on the choices in data validation boxes "Week #" (G6,G7,G8) with the following rules in place:
1. If I made a choice in G5 to be "1" which caused data validation in cell G6 to update, then in G6 I choose "2", the output in H6 should be just week 2's value of 20 (not the sum of week 1 and 2).
2. However, if I choose "3" in G6 (after I chose "1" in G5, as above) then I'd like the output in H6 to be the SUM of week's 2 AND 3, if that makes sense and so on. (e.g. If I chose "5" in G6, then output in H6 should be the SUM of week's 2 AND 3 AND 4 AND 5).
Same rules apply for H7 and H8. I have a feeling this may involve MATCH function and INDIRECT or SUMIFS but how to approach this.
I've attached a file : Dynamic DV and Dynamic SUMS.xlsx
I maintain an excel application for a local theatre group using alot of vba actioned by buttons on worksheets. I regularly email the application back and forth between the treasurer and myself and I have noticed that the process of doing this often seems to result in the buttons losing their reference to the code I've assigned to them.
why this happens (is it a bug?) and how to make the linkage more robust? Can I link the buttons and code using vba so that I could establish all the links when the workbook is open?
Worksheet A - I've set up a dynamic range to display the last 12 row entries of 2 columns Worksheet B - pivot table of the range
The dynamic range is working perfectly, it displays the correct rows I can set up the data source once... the results on the pivot table display corectly
Problem When i press refresh, the data source "dissapears" and i have to rebuild the pivot table.
I have a data set that is defined as a table (Ctrl + T). There are formulas so I locked those cells and Protected the sheet. If I am at the bottom right cell, and press Tab to add a new row, I get an error message: "Cannot use this table functionality on a protected sheet"
I really need to protect the cells with formulas so that the are not accidentally deleted. But I also want to retain the Table format.
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e. First dynamic range: called "Milestones" at A11 Second dynamic range: called "Activities" at A25 & make a Validation list that will list content of both
I have 7 sheets including the table as you can see on the attached. Now, each sheet is allocated to individual items e.g. item 10, item 20 and so on. I can't figure out how to return all the Item 10, 20, 30 ... to the corresponding sheet. It says Item 40 to all sheets.
This example workbook contains a datatable, which is inputted via a userform. The datatable has 4 columns: Date, Invoice no., Loads, Tonnage. This table is dynamic, as a new row gets entered each time data is entered in the userform. A different userform (the one in the example) has comboboxes which refer to the data in the table. This userform asks the user for the Date, Invoice no., Loads and Tonnage. I want to use comboboxes so that they will advise the input based on the users previous input.
The first combobox asks the user for a data, and should contain a list of all the unique dates that are stored in the table. When the first combobox is inputted, the list for the second combobox will change. The list of the second combobox should be a list of unique invoice numbers, based on the date that has already been entered. The 3rd and the 4th combobox should also show a list of unique values, based on the previously entered date and invoice no.
Example: The first combobox should advise the dates: 4-Nov-14 and 15-Nov-14. User chooses 4-Nov --> second combobox should advise unique invoice numbers based on chosen date: 1252 and 1311. User chooses 1152 --> 3rd combobox should advise unique values based on previous values: 3, 8 and 7. 4th combobox: 57, 23 and 47.
I am using the following code to find out if a file is open or not. The file name and directory is base on a cell value.
Sub SumTransfer111() 'Establish file path and file name based on cell value J5 (say 15204) fname = Range("J5") dirname = Int(fname / 100) * 100 dirname = dirname & " - " & dirname + 99 dirnamesub = Range("J5") Const sRoots As String = "S:MTL_DATAJobs" Dim sPaths As String Dim i As Integer sPaths = sRoots & dirname & "" & dirnamesub & "" & "Job_Cost_Summary_" & fname 'Establish if file is open Dim blnWorkbookIsOpen As Boolean.,.................
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
I'm trying to accomplish the attached. This is just an example of what I want to do. There are no formulas in this spreadsheet.
Spreadsheet 1: Search by Number
Column A is a list of names
Column B is a list of numbers
Column C is a list of the names that correspond to the number "1" from Column B. Note that there are no blank rows between the names.
Spreadsheet 2: Search by Name
Column A is a list of names
Column B is a list of offices
Column C is a list of the names that correspond to office "Williams" from Column B. Note that there are no blank rows between the names.
I have used the index & match formulas to do this WITH duplicates or blank rows, but I would like to produce this without duplicates. I am willing to get as complicated as need be to make this happen.
I have successfully used dynamic lists that remove items that have been selected from a dropdown menu already, however, is there any way dynamic lists can be set up to handle multiple identical items and then if there are say 3 identical items in the dynamic list, once 1 has been select only 2 remain in the list...?
I have a worksheet that is computing the average price for an apartment rental, and I want the average to change when I de-select or select different qualities from my drop down list. For example, I want to look at buildings that allow pets and Utilities included in rent, which hides all but 4 buildings, yet the average remains the same that was calculated with the whole data table.
I know it's relatively easy to just then average that out, but I would really like to have it be dynamic for future additions and comparing many variances quickly compared to other data sheets of buildings in other areas.
The following VBA code creates a list of selected listbox values when used in conjunction with a command button:
Private Sub CommandButton1_Click()
Dim lngLastRow As Long Dim lngCol As Long Dim lngIndex As Long
lngLastRow = Range("K" & Rows.Count).End(xlUp).Row + 1 lngCol = 11 For lngIndex = 0 To ListBox2.ListCount - 1 If ListBox2.Selected(lngIndex) Then Cells(lngLastRow, lngCol) = ListBox2.List(lngIndex) lngCol = lngCol + 1 End If Next
End Sub
This works fine for the listbox, however, for my application, I am using a combobox instead of a listbox. When I create a combobox and substitute ComboBox1 in for ListBox2 and run the code, I receive an error message.
I am trying to analytically solve an equation to establish a physical value. Unfortunately, it's difficult to do the algebra, so it needs to be done this way.
I have an experimental value for this in "J2", so what I want to do is change F2 to that "I2" is equal to "J2"
Is it possible to get excel to match these values - I have about 200 different cells of this?
The formula is in cell "I2" =B2*((1-(C2/D2)*((F2/G2)^1.2))^-1.5125)
i'm trying to simulate badminton match with MS Excel..
i just want to make this project simple..
|..A..|..vs..|..B..| Player A vs Player B
Two players compete in a match.The possibility is 50% for both players to win.However,to make it real,each player have their own strength and ability.For this project,i just indicate 3 which is smash power,net power and stamina.[/size]
Ability:Smash Net Stamina
Players with more ability have more advantage.How can i relate it with the final score?
Scoring system:2 or 3 set in a match (3rd set is when both tied at 1-1) A match is the best of three games. Played with 21 points If the score reaches 20-all, then the game continues until one side gains a two point lead (such as 24-22), up to a maximum of 30 points (30-29 is a winning score)
In MS Excel:
...|Player A|...vs...|Player B|... <--Player A vs Player B
............|SIMULATE!|............ <--When I click Simulate!,scores appear
| 1st Set | 2nd Set | 3rd Set | <--Here where the scores appear
|............| Smash |............| <--Comparison of ability(can be edited) |............| Net |............| |............| Stamina |............|
