I am looking for a forumla, which I think will be an If forumla, to allow/block entry into a cell depending on what is entered in another cell.
So if "Yes" is entered into cell 1, I want cell 2 to show "N/A", and if "No" is entered in cell 1 I want the user to be able to enter data into cell 2.
I am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple:
Cell A1, they can select Rice, Cheese, or Rabbit
I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10.
Can I do that with data validation? I can't get any if thens to work in it.
I am using the below code to enter the data in Cell "D" & "E" of the worksheet.
Dim a, b As Integer
a = WorksheetFunction.CountA(Sheet2.Range("C:C"))
ActiveWorkbook.Sheets("Retailing Data Sheet").Activate
'If Range("C" & a + 1).Value "" Then
[Code] .......
But, If someone wants to enter the data manually into the Cell "D" & "E" its allowing which i dont want.
It should be enter by using the form only...
I am working in both 2003 and 2007 Excel.
This is a simplified version of my worksheet:
I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".
Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:
[Code] ......
The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.
I have attempted to attach a sample worksheet : Xl0000003.xls
I have two coloumns on a worksheet. In the first column I have data validation drop down list. I want to be able to: if the first column "Other" is chosen then the next cell will allow text for description, if anything other is chosen in the first drop down list cell then I want the next ( text for descritption ) cell to be resticted.
View 4 Replies View RelatedI've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
The 'gist' of what I need is for events scheduled (top of spreadsheet), no school can be placed into an event within 29 minutes (because one-half hour apart IS ok). The time increments are on the left side of the spreadsheet (rows). I'm trying to "automate" somehow so that if we try to plug a school into an event too soon from their last event, the spreadsheet won't allow it.
View 2 Replies View RelatedI'm am trying to restrict the length of data that can be entered into a cell based on a drop down in that row. I have achieved this, but what I need to be able to do is use data validation to restrict the cell length to one of two values - so for example the length can only be 6 OR 9 characters long.
I cant see how to do this. At present the best I can do is rectrict to between 6 and 9 - but this would allow 7 and 8 which is not allowed.
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))
I am currently using the above formula and need to make an addition to it.
If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30
I have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?
View 14 Replies View RelatedIf A1 = "Yes"
Then don't allow input into cells B1 and C1. Or delete anything in B1 and C1 perhaps?
Cell A1 would be the result of a formula
How would I go about creating something like the above?
In column A I have Dates
January till....
The code below count the number of months and then should create only, this amount of new tabs (based on monthlist array)
So if I had Dates from January till October it should create only 10 new sheets.
So far is entering all of them. I know I'm missing a loop somewhere
Code:
Sub AddSheets()
Dim lmax As Long
Dim lmin As Long
Dim lmonth As Integer
Dim cc As Integer
monthlist = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
[Code] ..........
I have a userform with 8 text boxes and users have to enter different numbers separated by commas like 1,5,6,15,28 and so on.
I want to put a validation that if a number is entered in any of the textboxes than the same shall not be allowed in any other textboxes. Even in the same text box, no duplicate number shall be allowed. ie if in a textbox 1,2,15 is entered , then in the same textbox also, user cannot enter 1 or 2 or 15 again.
dont know if this can be done using a formula rather than VBA...but is there a formula that would change the countif range based on another cell entry. so the formula below has a column range of 2...so the other cell entry would be '2'.
=COUNTIF(B1:C1,"=Y"). If i then changed the cell entry to '4' the formula would change to. =COUNTIF(B1:E1,"=Y")
I will try putting the Code Tags in correct this time, I think I figured out what I have been doing wrong.
This code will allow one cell to make a sound when a condition is met. I am trying to have each cell stand on its own. Example cell A1 would sound a Ding if the number >100 and cell A2 would sound a Tada if the word Cat would be returned ....
I am trying to find out whether you can restrict the rest of the userform from being available, unless a certain field is entered.
I have a tax invoice userform, I want to restrict the user from entering any other data unless they have selected a customer.
There are other fields such as Customer Address, Customer Number, Disc% and Terms that populate through vlookup code once the customer is selected, so still need that to work once the customer is selected. I just don't want them to be able to enter any other data on the userform without the customer being selected.
I wasn't sure whether I needed to enter code into every other textbox or combo box that if cboCustomer = "" then pop up an error message, but that would require a lot of code to be entered. I have 30 comboboxes and 53 Textboxes that I want to restrict to not being available unless a customer is selected from combobox.
How can I restrict entry into a textbox to 1 decimal place?
View 9 Replies View Relatedi'm trying to basically use VBA, so that when a list of work tab names are entered into a column in say Sheet 1..these tabs are then selected.
View 4 Replies View RelatedI have a dilemma.
1.I want to be able to a have a cell be filled with a certain value when the user enter data in Column C.
2.I know that this can be accomplished by a formula, but my spreadsheet will work my efficiently with a macro.
3.I do not know much about VBA but I believe that I will need a macro for each sheet that fills in the word.
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3
i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
I would like to write the code or create a macro that will execute when the value of a range of cells is greater than null. The macro or code that I would like to execute will UNHIDE a group of consecutive rows.
View 14 Replies View RelatedI have a workbook with 3 worksheets which contains a single spreadsheet where the user enters data and 2 worksheets containing all the named ranges and formulas(divided up by product model) used on the data entry worksheet. What I've been trying to accomplish is to choose one worksheet based on which product model I select on the data sheet.
View 1 Replies View RelatedI need to force our salespeople to enter information in a cell based on a condition. This is the condition: If I5=3, then I need to move the user to cell I3 with a message that says "Enter miles to site" -- I don't want them to be able to move from the cell until they enter the information. If I5 does not equal 3, then I don't need anything to occur. I need to apply this code to four separate worksheets in the same workbook. Also, they will be entering a number in cell I3, so is okay to leave I3 formatted for Number or General Number? Note: This question is also posted at the following link, but has not yet been answered by that group: http://database.ittoolbox.com/groups...dition-2270643.
View 2 Replies View RelatedHow do you restrict a user to only be able to enter up to 2 decimal places in a cell without it automatically rounding for them?
View 6 Replies View RelatedI have the following formula in cell H2:
=AND(EXACT(G2,UPPER(G2)),(LEFT(G2)>="A")*(LEFT(G2)<="Z")*(MID(G2,2,1)>="0")*(MID(G2,2,1)<="9")
*(MID(G2,3,1)>="A")*(MID(G2,3,1)<="Z")*(MID(G2,4,1)>="0")*(MID(G2,4,1)<="9")*(MID(G2,5,1)>="A")
*(MID(G2,5,1)<="Z")*(MID(G2,6,1)>="0")*(MID(G2,6,1)<="9")*(LEN(G2)=6))
I would like to prohibit data entry in G2 if H2 reads "false" and allow data entry in G2 if H2 reads "true" .
i am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid)
example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account.
If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1.
If D:D is "payment pending" do same as above but in row 2 and so on.
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case).
I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
I have a row of dates in row 2 (all sequential, from Nov through to June next year)
I have a row of number entries in row 3 (to correspond with the date in row 2 it was entered on).
I want to be able to add a new entry (a number) into a text box, click a macro button, which makes the text box input be moved into the cell underneath todays date (so every day the cell will move one column along)
I've managed to make myself a textbox, and a macro button, but I'm stuck with how to make it all work.
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.