How do I get data validation to automatically add an entry into its database.
Ex.
If a cell if formated for data validation and I make an entry thats not currently in its database, it enters it into its database.
I already have various dependent validation lists established using the INDIRECT function.
Now I want to figure out how I can automatically change the selected values given the selected list. For example:
Cell D11 is a validation list with options: Boat/Car
Cell D15 is a validation list where if D11 = Boat the options are Catamaran/Sailboat/Tugboat. If D11=Car the options are Convertible/Sedan/Midsize.
The problem is that if D15 is preselected as Sailboat, for example, and you go back and change D11 to equal Car, cell D15 remains an option from the Boat validation list. I would like it to automatically update as the 1st item on the car validation list.
I am trying to arrange a dependent drop-down list and have the basic listing working. My problem however is that when i pull data from sheet "Mon" for column C using indirect it works but when I try to do the same thing for column D i get the same data as column C. How do i get indirect (or offset if it works better) to allow me to reference the alternate data 1 column over?
View 3 Replies View RelatedI require a formula for custom data validation to permit entry 5 digit aplha characters only.
No spaces, or numbers permitted.
I require a custom formula for in cell data validation of an 5 digit alphanumeric entry. The valid format is ANNNN (1 x alpha & 4 x numeric). Case of the aplha is not an issue.
View 3 Replies View RelatedI need cell H18 to look at cell F4, and if the value in cell F4 is 'CFB', I need validation to limit the entry in cell H18 to the values in a named range 'pripro' to populate. That part I know how to do. (=pripro)
But if the value in F4 is CCP, I need H18 to allow anything. I'm having trouble adding this to the first part.
I'm trying to figure out how to create a database on Sheet 2 by filling in the info in a template created on Sheet 1. I have seen it done with the addition of a button within the Excel sheet itself, and you simply click it to send your information to the database page. I am thinking it is along the lines of OLAP or recording macros
View 8 Replies View RelatedWhen you want to use Data Validation to limit the entry of the number of characters to a cell does this apply only to the cell that you are entering the characters or can it also apply to a cell that contains a LEN(A1) formula, for instance? Also,does the Data Validation limitation function includes spaces as well? Will the message appear while you are entering the characters (when it has reached the limit) or will it wait until you have hit Enter?
View 1 Replies View RelatedThis is the first time Ive tried to use VBA. Im using Excel 2000. In my excel workbook have 1 sheet called Cards in which I Change data in one cell $B$2 (enter a part number) and formulas in this sheet return many values from another sheet Card Data. In the Card Data sheet, formulas that look through a sheet Sales Book and return the data that pertains to the Part Number I entered in the sheet Cards. In the sheet Card Data there are 3 pivot tables (PH CALC, PH QTY, and Pivot Table 3) that use the Part Number data to show 3 different sets of information. The pivot tables are the source for 3 charts in the Cards Sheet.
Problem: Pivot tables dont automatically refresh. I would like to use VBA code to automatically refresh the pivot tables in the Card Data sheet when I change the Part Number in cell $B$2 of the Cards sheet, which, in turn, would then update my charts in the Cards sheet.
I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
I've been using Dave Hawley's "Excel Data Validation List - Automatically Add to a Data Validation List" in my wb with great results, but now I am going to use a secondary wb to keep the Data Validation List and herein lies my problem. I lack the VBA skills to be able to modify the code and references so that when I input into the primary wb the data is updated in the secondary wb. The Primary wb is an xlt that I'm developing, each time a new xls is opened from the Primary.xlt, vba in the Primary.xls opens Secondary.xls. Secondary.xls has a named range "Names" which I wish to be able to update when data validation is performed in Primary.xls per the sample on this web site.
View 3 Replies View RelatedI'm trying to implement the feature from this great Ozgrid 'how to', and it's working for the most part, but the VBA falls over here...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$F$10" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction. CountIf(Range("Dept"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Range("Dept").Cells(Range("Dept").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub
Basically, I have a tab which contains 2 lists. One in Column A and one in Columb B. I want the corresponding dropdowns on a different sheet to allow entry of anything that's not on either of the lists.
I can get it to work if I place the lists on the same sheet as the main 'form', but I need to keep them on a seperate sheet (and as the 'how to' suggests the lists can be on any sheet, I thought it'd work 'out of the box').
trying to incorporate this in to a workbook in which name range is on a separate sheet to where the validation cell is but with no success could any one tell me how to adjust it so that it works,
View 8 Replies View Related1.jpg2.jpg
I am trying to create a database for part stock but with only one entry point that is in image 1 you would select the machine and part from drop down lists and hit add. It would then lookup the corresponding machine and part and subtract 1 from the current stock database.
I am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.
My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.
The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................
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] .....
I'm trying to create a spreadsheet that has a data validation in it. I have a drop down list that has to options to select; Yes and no. Every time I create a new row to add more information I have to go through the process of creating a new data validation for for each cell in this coloumn.
Is there anyway of creating a way that every time I insert a new row, a data validation list box will appear in the coloumn that I want it in?
Sample Workbook: datavalhelp.xls
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.
Hi I have a column where a cheque number can be entered I need to check on entry that the cheque number has not been used before further up the column.
View 7 Replies View RelatedI've posted an example workbook that has sheet1 as how the data comes and sheet2 as what I need it to look like (through some kind of automatic process). I really don't have a clue as to how to get this done, and I'm guessing some VBA is going to be required.
View 3 Replies View RelatedI am trying to force a user to enter a value (any value at all) into a cell once they have selected it.
So the cell is blank, then they click on it... then they can't move to the next cell until the enter some text.
I have 2 sheet in MS Excel (MATTER & REPORT) . Database included at sheet MATTER. Report will be seen on sheet REPORT based on entry date. What formula used to produce report automatically.
View 4 Replies View RelatedSpecifically, I have customer sales data from my web site that contains order numbers and sales data. From Google analytics, I have transaction information that also contains the order number. The data element that is common to both is order number. I can't just paste columns from one file into the other because the records listed in rows may not match up.
I don't want to have to copy and paste data from one file to another for each record manually since I have thousands of records. Is there a way to merge the two files together automatically by having Excel "understand" that it should pair the two files together using the order number to create a row that contains data from both files?
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies View RelatedI have a spreadsheet that I add to daily with 3 Columns that are always the same. Name, ID number and Phone Number.
Is there a way to make it so that when other people in my department use this that no matter how they enter the name, it is always formatted the same. No matter they enter the ID number and phone number they are always formatted the same. I've tried some variations of data validation but couldn't get to where I need to be.
Attached is a simple example.
I'd like the name to always be formatted as Smith, Bob
I'd like the ID Number to always be formatted as xxx xx xxx
I'd like the Phone Number to always be formatted as xxx xxx xxxx
I am trying to get VBA code in Excel to copy formula in specific cells to copy the cell below when an entry is added to a database.
View 3 Replies View RelatedIs there a way you can copy formulas to the next line when a new entry is automatically added to a spreadsheet via a form?
View 1 Replies View RelatedI've got a master spreadsheet grabbing data from 12-13 different tabs/spreadsheets.
One of those has a table, and I was wondering:
Is it possible for the master spreadsheet to automatically add a new line every time someone adds a new line and creates a new entry in a different spreadsheet?
In other words,
If there is a table in the master spreadsheet, and someone adds a new line, is there a formula which allows the master spreadsheet to add a new line in order to autopopulate?
Is there a way to automatically move cells down after I entered data in it? This way I do not have to scroll all the way to the bottom of the list to add new data.
View 5 Replies View RelatedI am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
