if it is possible to have data validations on a cell within Excel but allow a user to input a formula? The data validation is a decimal between 0 and 1 and the formula would be a vlookup.
View 4 Replies
I have a workbook that has a macro that needs to run before it is closed. I have a button on my sheet that runs that macro and then closes the sheet. Is there a way that I can disable the "X" at the top right hand of the workbook and force the user to close the workbook via my button?
View 3 Replies View RelatedI have attached a sheet that I'm having an issue with. Columns D and E on the Dim Calculator sheet have a data validation formula in the cell. It works in the sense that it pops up if they are outside the parameters that I want them to hit. However, if the user clicks the Cancel button they can just keep going on even though the entry in the cell is aganist the validation.
Example: Columns D and E cannot be larger than Column C. Currently now if Columns D or E are larger than Column C the validation error pops up. But I can just click on cancel and keep going. That is what I want to prevent.
Is there a way to grey out the cancel button?
Should I do something else within the validation itself?
The text box where the end user types in the data - I want to make this have a drop down list like when using data validation but dont have a clue how to.
View 5 Replies View RelatedI 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.................
I have a file where I need the user to fill in a value in a single cell (A1) in order for the data validation I have set up in column B to work. Can I set up a second data validation in the cells in column B? Or is there a way to enter a data validation in cell B11 that would error the user if A1 is not filled in, and circle the cell (A1) that they need to fill in? B11 currently returns the sum of B3:B10.
A
B
1
Straight
2
Time
3
Sun
4
Mon
5
Tues
6
Wed
7
Thurs
8
Fri
9
Sat
10
Sun
11
Total Overtime
I want to prevent user from entering duplicate text or numbers in a cell using the Data Validation.
View 9 Replies View RelatedIn the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6
I would like to delete a column using data validation list. for example :
i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.
And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.
I want to control what a user enters into a single cell based on others.E.g.
A B
1 20 500
2 50 500
3 75 500
4 100 300
Column A is a thickness and column b is a width what I want to happen if the user enters a thickness in cell D1 and a width in cell D2 I want an error to pop up if for instance the user enters 100 as a thickness to only allow a maximum of 300 in the width. likewise if the user enters a thickness less than 100 in cell D1 to allow him to enter up to 500.
I'm using Excel 2010, and I need to restrict the value the user can enter into a cell (E9).
In cell E3 is the screen width (pixels). eg 6024
In cell E5 is the preferred width of a window. eg 450
The user, in cell E9, enters an x coordinate for which they prefer the top left corner of the window whose width is specified in E5.
If the value that the user enters in E9, added to the width entered in E5, exceeds the value of E3, (if E9+E5 > E3) then the value should be disregarded (window will be off right of screen) and the user re-enter.
I'm not familiar with the use of data validation, so I'm uncertain as to how to use it in this circumstance.
I have a text box. I want it to pull the data from Cell B2 and list it in the text box. The problem is that I want to be able to change the data if the user clieck inside the box and changes it.
View 3 Replies View RelatedI'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.
Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).
Also, the Data Validation dropdown includes blanks which I would like to exclude.
I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.
I have a workbook with two tabs, the first tab "Query" is an Access query that pulls in call data from our phone systems. The second tab "Report" is a userform/report. This will be used by numerous people and has to be as userfriendly as possibly. Because our clients want the data sent this way I don't have a lot of room in changing the report itself.
Let me paint the picture, I have it so the user is able to select the month from a validation list I created in B1, when the month is selected is autopopulates each work day of that month in cells A13:A35. There are several (28 total) reports from different phone lines we have. So I have another validation list in cell B3 that has the name of each report we have. So in theory the user should select the month and report and the information will fill in and they can print that specific report off. The issue is this. I had used the formula =SUMPRODUCT(--(Query!A5:A25004=Report!A13),--(Query!B5:B25004=Aban_08),Query!E5:E25004). I'm trying to find total calls for specific lines for specific dates. The "Report" tab has information from January to current with all 80 different call lines (all named differently such as CDN_5700, CDN_5701). One report such as "Aban_08" actually pulls information from several call lines which is bringing me to my issue, I need one tab for the report, and be able to have the user select the different report, but each report pulls from multiple call line names. Again, I need (for instance) B13 (cell A13 is the date June 2) to pull all calls from the report tab for the date of June 2 with (for example) the names "CDN_5700","CDN_5701","CDN_5702".
iam looking for formula to display the condtion result .
i have spec in col B and tolerance in col C and col D ( +ve & -ve tolerance respectively) from col E to I ,observed reading data & col j for results.
i want to check the value of col E to I for B C D value n if entire 5 values are within spec then display "0" in subsequesnt col J else display "1"
OBSERVEDRESULTS.L.NOSPEC`+TOLERANCE`- TOLERANCE123451250.20.2262525.225.425.22100.20.2350.20.2450.20.25510.20.2
I am trying to make a simple planning sheet.
I have a workbook with 2 sheets.
1.sheet contains Columns : DayDateTimeClassTeacherCovered
2. sheet contains the availability of teachers. so all columns start with a date and the rows contain the name.
A row therefore looks like this.
7/1
Harley
Lidia
Livia
Thais
Salete
Carla
My issue: The 1.sheet has a column Teacher, now in this column I want to have a drop down list. (E2, E3, E4, E5 ... etc..) This list is depended on the date which is in column B.
Essentially I want the list to look up date in its row go to the second sheet and give me all the possible names in it. That is all.
I would like the individual cells of C1 and D1 to allow me to input values up to 100. The tricky part is that if D1 equals 100 I would like C1 to be less than 100 and vice versa, if C1 equals 100 then D1 should equal less than 100.
Ex. C1 = 100 & D1 = 10
This should never happen: C1 = 100 & D1 = 100. I would also like an error box to show when either of the cell values exceed 100.
want to get a formula where I can get the details of City if searched Brand wise and details of brand city wise. I also want option to search say five city names at once and get the brands available in all those cities. I have updated a excel with some dummy data.
View 7 Replies View RelatedI have a cell that uses list type data validation. I need to be able to update the list of allowable values via a macro and would greatly prefer not using a range in excel.
I've been able to create the string that I want as the formula but cannot change the formula. I'm not very familiar with this aspect of excel macros (data validation)
How do I hardcode the Offset function? Right now I am placing the offset formula in Validation from the Data menu toolbar.
Also, I have two columns "Expense" and "Income" used for offset. Expense has 11 selections for the listbox, and Income has only 3. When I choose income from the dropdown in column B, I get way too many blank selections in the dropdown just because It doesnt contain as many items as Expense. How can I re-code adn hardcode the formula so I have only few blanks for my selection.
I have attached a copy "data validation test" to help make things more clear.
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies View RelatedInputBox User Validation. Need to modify my code as follows?
View 5 Replies View RelatedI'm trying to set a data validation in B1 from values in A1:A5.
A1: Car
A2: Papers
A3: Pencil
A4: Pants
A5: Shoes
What I want is show in dropdown list the words in singular (without the "s" at the end).
The formula below works if I introduce it in a normal cell,
[Code]....
or as array formula with CRTL+SHIFT+ENTER in this way
[Code] .....
But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.
I am trying to use the validation feature to calculate a check digit and the formula is too long to put in validation. I rewrote the formula to be shorter using math features instead of text, but apparently you can't use arrays in validation. Because of certain distribution constraints, using VBA is not possible so a UDF is out. Can either of these be shortened in anyway? I am not sure if it is even technically possible to solve this problem with these constraints.
Here is the "human readable" explanation of formula:
(Accept only 10 digit numeric value.)
Example Number: 123456789-3
Digits 1,3,5,7,9 are multiplied by 2 (weighted 5)
So 1,2,3,4,5,6,7,8,9 becomes 2,2,6,4,10,6,14,8,18Weighted 5's multiplied values then have their individual digits added (ex:12=1+2=3)
So 2,2,6,4,10,6,14,8,18 becomes 2,2,6,4,1,6,5,8,9
Weighted 5's sum is added to the sum of the Nonweighted 4 digits.
So 2,2,6,4,1,6,5,8,9,3 becomes 43
Add 24 to sum.
43+24 = 67
Checksum = (10-(67 mod 10)) mod 10
(Sum round up to 10 minus sum)
Method 1 ...
I have a range named as follows in a formula:
BaseCase!$O:$O
I have a data validation from which the user can choose another tab.
So, how do I change the reference above to:
TabName!$O:$O
so that TabName refers to the text string in cell B2 (data val. box)
I tried to use TEXT to no avail. I imagine there is a simple solution, but I am at a loss.
INDIRECT perhaps?
Is it possible to validate a string with both numbers and text with a formula using data validation? (I don't want to use a User Defined Function)
in Column C, Starting in cell C2 I have a 12 character string of six numbers, four letters, and two numbers. like this:
280838ZNOV11
is it possible to use a Custom Data Validation formula to ensure the user follows this format?
I am trying to get my array formula to recognize the text selected in my data validation cell. The formula worked perfectly until I decided I wanted to try and use a data validation list to eliminate spelling errors. Even though the text I am trying to recognize in the cell is exactly the same, because it is in a data validation format it no longer works. Is there a way I can modify my formula so it works? Example below...
formula: {=SUM((A2:A109>DATE(2013,10,31))*(H2:H108="Joe Blow"))}
A2: 11/2/2013
H2: Joe Blow (chosen from a data validationi list)
The result of the formula should equal 1 in this example, or more if the same criteria occurs more than once within the range of A2:A109, and H2:H109.
I want one procedure that will validate the CURRENT textbox (not named by name, to allow for the procedure to be included in the change event of several different textboxes), to see if it is numeric (decimal places allowed) in Excel 2007.
I followed the instructions here: {url}, which describe exactly what I want to do. So I put the final procedure listed on that page (the dynamic validation code) into the private module of the user form object as listed below:
Private Sub pipes_Change()
OnlyNumbers
End Sub
Private Sub OnlyNumbers()
'This procedure checks to see if the value
'of the current textbox is a number or not
If TypeName(Me.ActiveControl) = "TextBox" Then
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers are allowed."
.Value = vbNullString
End If
End With
End If
End Sub
I then ran the form and typed a number into the "pipes" textbox that had been validated. A compile error is thrown: "Invalid or unqualified reference", highlighting the ".Value" portion of IsNumeric. I tried to remove certain parts of the code, such as the IF statement and the "And .Value <> vbnullstring", but nothing works. I have a feeling it is because this code was created for Excel 2003 (though I'm not certain).
Can I limit the text length in a cell that contains a formula? You may say "Limit the text length in the input cell". That can't be done because the formula in the resultant cell takes text from two other cells in addition to the input cell. I need to limit the overall text length to, let say, 50 characters.
View 7 Replies View Related
