Auto-Update Validation List When New Entries Added To Source
Mar 18, 2008
how to make all cells displaying an item from the list source update with new information added to list source.
Example:
Cell "A1" on worksheet 2 displays the 3rd slot of information from list source on worsheet 5. I want cell "A1" on worksheet 2 to update when the list source information in the 3rd slot on worksheet 5 is changed.
Notes:
The list is set up by naming the columns on Worksheet 5 and is set as a validation list on the other worksheets. So on Worksheet 2 column "A" has the validation
VB:
AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
List=(Coulumn A 's name on worksheet 5)
View 9 Replies
ADVERTISEMENT
Jul 21, 2008
i have a list called "parts". i want this list to be updated everytime a new part # is entered into the list cell. i can get it to work in one cell. i have been able to get it to work in up to 6 rows, however i want it to work in up to 50 rows. here is the vba code i am using
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address >= "$D$3" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Range("parts"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Range("parts").Cells(Range("parts").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub
i am using insert>name>define for the list and this is what i used for that
=OFFSET(lists!$A$1,0,0,COUNtA(lists!$A:$A),1)
like i said, it will work for d1-d6, then it stops working.
View 9 Replies
View Related
Jun 9, 2006
I have a graph that is showing the date on "Y" axis and a value on "X" axis, when I add a new date and value to my data the graph does not update, it just shows the data when I first created the graph.
How do I get the graph to display the new data I entered ?
I created a line graph by clicking on the "A" in column "A" (Thats where my date is) then clicking on the "B" in column "B" (Thats where my values are) and clicking the create graph button then clicked finished.
View 6 Replies
View Related
Dec 19, 2012
consolidating data from 3 worksheets which contain same data fields but each representing 1 brand. I like to have a summary/mastersheet to contain data from all 3 worksheets and then when the data in each worksheet is added or updated, the summary worksheet should reflect the changes (adding new data to the last row).
Project Submissions.xlsx
View 2 Replies
View Related
Apr 1, 2009
Not sure if this can be done, still a rookie at this stuff. Everything works but can something be wrote into code too change list source?
View 5 Replies
View Related
Mar 13, 2013
Is there a way to automatically create list of the Tab names, and have it automatically update when new tabs are added?
View 2 Replies
View Related
Apr 8, 2014
I try to find a solution (with or without macro) to assign different ranges named "Failure_C..." in a data validation source depending on the value of another data validation list (32 values called)
I started in the Data Validation List Source to fill in IF function but 32 inputs are not accepted and too much caracters in the source.
The formula is starting like that:
=IF(A57=$M$78,Failure_C01,IF(A57=$M$79,Failure_C02,IF(A57=$M$80,Failure_C03,
IF(A57=$M$81,Failure_C04,IF(A57=$M$82,Failure_C05,
IF(A57=$M$83,Failure_C06,IF(A57=$M$84,Failure_C07............
View 2 Replies
View Related
Jun 25, 2007
Is it possible to have a list from a different workbook as the source list for a validation dropdown on a cell? If so, what is the syntax.
View 2 Replies
View Related
Apr 28, 2009
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies
View Related
May 6, 2009
i just want to generate a list validation where source resides in another workbook?
i have used named range concept ?but not working can anyone help..
View 9 Replies
View Related
Mar 10, 2008
when you choose Data Validation for a specific cell, you can choose list. Once you have chosen list for "Allow", you can select the source cells for what will be in the drop-down menu. I want to be able to use VBA (or just use Excel) to modify those source cells.
For example:
There will be 3 lists with drop-down menus using Data Validation.
List 1:
Choose first option in list -> List 2 uses source A
Choose second option in list -> List 2 uses Source B
etc...
List 2:
Choose first option in list -> List 3 uses source C
Choose second option in list -> List 3 uses source D
View 11 Replies
View Related
Sep 2, 2006
I'm trying to make a sheet that will allow users to either pick from a drop down box, or if the choice they want isn't there, to input data in the same cell. That part I have got. What I can't do is figure out how to have excel add that user input to the list of options, so that they next time the drop down box is used, that "user input" will appear as an option in the drop down. I used data validation to do all these drop downs.
View 2 Replies
View Related
May 23, 2007
I have a spreadsheet that needs to have a validation drop down list in three cells in every row (D, E, F). There are three items in the list, say, "One", "Two" and "Three".
My only problem is that there can only be one entry of "One" in each row.
View 9 Replies
View Related
Jan 2, 2007
I've created multiple ranges in a sheet named ' Schedules' that all use one Data Validation list. The list source is a Named Ranges called 'AbbNames', which points to a column ($S) on another sheet named 'Students'.
AbbNames is simply a list of student names--that are values; AbbNames does not contain a formula. However, AbbNames was created with VB code in the Worksheet Change Event. The code is:
Dim rng As Range
Dim rng1 As Range
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set rng = Target.Dependents
On Error Goto 0
If Not rng Is Nothing Then
Set rng1 = Union(rng, Target)
Else
Set rng1 = Target
End If
On Error Goto errHandler
If Not Application.Intersect(rng1, _
Application.Range("Abbreviated")) Is Nothing Then
Application.EnableEvents = False
Application.Range("AbbNames").Value = _
Application.Range("Abbreviated").Value
End If
errHandler:
Application.EnableEvents = True
If Err.Number <> 0 Then _
MsgBox "Error occured " & Err.Number & vbNewLine & _
Err.Description
Note that students' names were originally concatenated in a column named 'Abbreviated' located in column G.
(this code creates the 'AbbNames' data in column S and updates the data if data is changed in 'Abbreviated'--if data is originally changed in columns A and B, which are firstname and lastname)
To make a long story shorter, I needed a column of concatenated names that were values--not calculated--to base the listboxes off of in the Schedules sheet. Validation list boxes can't be based off of calculated columns, right??
However, the listboxes do NOT work. When a user clicks on the drop-down arrow, Excel pauses for about 20 seconds (the initial time) and then shows nothing in the list except a blank field. When the blank list is clicked, the top name from 'AbbNames' is entered. However, no names can be seen or selected in the list box.
View 9 Replies
View Related
Mar 14, 2007
I have a drop down list in a column where i want the user to select one of the options which is an action to be taken. The actions would be dependant on the what the persons performance rating was, so for example, if the person has Exceeded their performance targets, the only 2 applicable actions would be High Potential and Emerging Talent. Please see attached file for the actions related to the peformance rating.
I want the validation list of actions to change accordingly depnding on what the persons performance rating is. So in the example attached, i want to look up the rating in column A and have the options appear according in the same row in column B.
View 9 Replies
View Related
Mar 5, 2009
In sheet 1, there are two columns, Category and Subgroup. The cell validation for Category is pulled from the category list on sheet 2. Basically, what I want to happen is depending on what is selected as the category, only the related sub-groups would be populated in the sub-group validation dropdown list. In other words, if Fruit is selected as the category, I only want Apple, Orange, Pear, Banana, etc to be in the validation drop down list for the sub group. If vegetable, then only Corn, Green Bean, Cucumber, and Peas should be shown.
I've included two options for the relationship list as I'm not tied to either style and not sure which would be easier to work with.
View 9 Replies
View Related
Jan 11, 2013
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
View 2 Replies
View Related
Apr 15, 2011
On Sheet2, data validation is applied to cells D21:D70 to limit data to values to a list, which is a named range on another worksheet. There are ranges on two other worksheets with the same data validation rule. The range for the list is ‘Sheet1’!D12:D26 (which is named “Processes”).
On Sheet1, the user enters a list of their processes. Then on Sheet2 (and the other sheets where the data validation is applied), they select one of the processes from the list. This all works fine until they go back to Sheet1 and modify one of the previously entered values.
For example, "Brush Teeth" is entered as one of the processes on Sheet1. It is then selected on Sheet2. Then, the user goes back to Sheet1 and changes the value to "Brush Hair." When you return to Sheet2, the value is still "Brush Teeth." It can be manually be changed by selecting “Brush Hair” from the drop-down list (“Brush Teeth” is no longer available), but it doesn’t automatically update.
Data validation was applied to a single cell (A3) and the list was on the same worksheet (B1:B3).
Option Explicit
Private oldValue As String
Private Const DVCell As String = "A1"
Private Const dataList As String = "B1:B10"
[Code]...
'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in.
On Sheet2 (I assume I need to add this code to every applicable sheet), I tried editing the first part of the code as follows (changes in bold), but when I click on any cell on Sheet2 that has the data validation rule applied, VB returns the error “Run-time error ‘1004’: Method ‘Range” of object ‘_Worksheet’ failed.” If I go into the debugger, the second instance of “If Not Intersect(Target, Range(dataList)) Is Nothing Then” is highlighted.
[Code] .....
View 9 Replies
View Related
Nov 7, 2007
I am trying to do 2 things involving data validation. 1) Count the number of entries in a data validation dropdown list and display a result. 2) Use an If statement to pull the formatting from a cell into another cell.
An example would be if Cell A1="A", then show the drop down list associated with cell or sheet "!ryanB2". Alternatively, I would like to also display the number of entries in the drop down list.
View 9 Replies
View Related
Nov 30, 2009
I have a sheet that creates a list of columns that may need to be updated or edited as necessary. Column A is a list of names that are alphabetized automaticaly from column D, so any change in column D would need to be reflected in column A. The attached sheet will (I hope) make things clearer.
View 14 Replies
View Related
Jan 17, 2014
I have data validation in a cell that says only options from a list are allowed. i.e. =indirect(A1)
If this refers to a list where there is only 1 option available is there a way to display that value automatically without the user having to click the dropdown to select it?
View 1 Replies
View Related
Mar 21, 2008
I am using the same VBA that is discussed in several threads on the internet to create a combobox on top of underlying excel validation. The most relevant post I have found on this site is linked below, however I am told that it has expired and I can not reply to it so I started this new thread.
Data Validation Autocomplete Via Combobox Modification
The VBA code discussed in the above linked thread is working pretty well in my excel file which has MANY different fields being validated. However, there is a problem that I can not seem to get past. Specifically, the combobox is currently showing the validated data behind it, but will also allow IN-valid data to be entered. Is there a way to stop this and force only valid data to be used (i.e., is there a way to force a "re-validation" on exit from the combo box)?
View 5 Replies
View Related
Jan 12, 2010
Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?
View 3 Replies
View Related
Jun 19, 2008
Is there a way to make a chart adjust automatically when you add to the source data? I'm trying to use a named range in order to power a chart (something like chart_data=offset(a1,,, counta(a:a),counta(1:1)); the range is working fine). When I put "chart_data" into the source field for the chart, it seems to work fine; it picks up the right range... But when I later add more data, the chart source data field seems to have converted my named range into a hard-coded range, absolute range. So the dynamical named range updates, but the chart doesn't and I don't see the new data. Am I doing something wrong that I need to tweak? Does what I'm trying to do just not work? Is there another way to make the chart pick up the new data without adjusting the range?
View 3 Replies
View Related
Oct 27, 2006
this problem is just an follow up question to this thread [Solved] VBA : user input to combobox xla. it supposed to be a follow up question but as per RULES is concerned i have to post a new one "4. Never post a question in the Thread of another member. You MUST ALWAYS start you own New Thread.". my question is how to retain the inputted values upon next opening of the workbook? you see that in this thread everytime the userform initialize the values added are also refresh , but what i want is after i manually input an entry of the combo box on next open the values are saved correctly..
View 5 Replies
View Related
Aug 4, 2014
I'm trying to populate a collection with PossibleAnswers to a Question. Every time I do this the values from the newly added entry get propagated to each entry in the collection (best way I can describe it - see code attachement).
Here's the existing code - including the current debugging code surrounding the add! The function is a Method of a Question object.
[Code] ....
All the Find and FindNext procedures do return the appropriate next value. However, the debug loop produces the following:
[Code] .....
As you can see, something is being added, but it seems to be affecting every single entry.
View 3 Replies
View Related
Oct 20, 2007
I am trying to make a line graph of a company's profitability. Each day I add a new page and use data I collect to determine the day's profit. I want the graph to update each time I add a new sheet. The cell that contains the day's final profit is the same cell on every sheet.
View 2 Replies
View Related
Aug 19, 2006
I have attached a copy of the chart and data. My chart starts on the bottom. I had to take out a lot of data but beneath the data shown is much more data..
I want my chart to automatically update when new data is added. Here are the issues. I have several lines of headers and below that my data. Then below my data I have other data that I don't want displayed in the chart (its not shown here because of size limitation of attachmnet).
The new row will be added each time right below the header, right above the
most recent data given. So a row will be added above row 8!
View 9 Replies
View Related
Aug 8, 2007
I have a pivot table I am try to update. The table references another tab where my data sheet exists. If I add data to the datasheet how can I get my pivot table to recognize the additional information.
View 3 Replies
View Related
Mar 12, 2008
I used VBA to write a number of formulas into various cells in a workbook. After the VBA is done running, I change the values in the cells that are referenced by the cells I wrote formulas in. The formulas do not update after I enter this new information. My calculation is set to automatic, and F9 doesn't help.
View 3 Replies
View Related
