If 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?
how can I prevent an entry in a cell even without formula lets say cells B1 , C1 , D 1 , E1
I want to prevent an entry in these cell and if you try an error message will display
Using 2003 - Is it possible to prevent cell data entry automatically creating a hyperlink for email/web url's. Using
View 4 Replies View RelatedI 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.
I have a userform with several textboxes and 2 comboboxes. The data that I need protected from duplication is based in the comboboxes. If I ignore the other fields and presume the following it may be easier to understand:
The comboboxes are populated by the data stored in sheet1 and when the userform is complete, the OK button populates all the data into sheet2
Combobox1 contains names eg, John, Julie, Bob etc
Combobox2 contains colours eg, Red, Blue, Green etc
I want the userform to allow the data to be entered into sheet2 only if it is not an exact duplicate of the choices in BOTH of the comboboxes
For Example
John Red
Julie Red
Bob Blue
John Blue
These are all ok, however, to try and enter John Red again would bring up a message box indicating a duplicate entry and prevent the data from being entered.
=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 RelatedI 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 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 have a small macro that before closing the workbook will check a range ("C4:G24") on Sheet ("Data Checks"). It will then show a msgbox displaying each non "OK" result in the range. I want this to close the workbook if all cells in the range have the value "OK" and cancel the close if the msgbox appears. This is what I have so far...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim r As Range, txt As String
With Sheets("Data Checks")
'for each non "OK" result in range
For Each r In .Range("C4:G24")
If r.Text <> "OK" Then
txt = txt & r.Text & vbLf
End If
Next
End With
If txt <> "OK" Then
MsgBox "Please check:" & vbLf & vbLf & txt, vbExclamation
Cancel = True
End If
End Sub
The problem I have is that although the msgbox displays the correct cell values if they are not "OK", it also appears and cancels the close if all cells in range DO have value "OK"???
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'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 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.
excal VBA programming.I have attached the file name "help" for your easy explanation purpose.
1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell.
2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.
i have an address that is all in one cell displayed like
101 hampton Court, Hampton heath, Hampton Town, Hamptonshire, HA01 1AS
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court
B2 Hampton Heath
C2 Hampton Town
D3 Hamptonshire
E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
I am trying to complete a spreadsheet that will be shared by several users but have come across a slight problem. I need them to be able to enter text into blank cells but when the the text has been enetered for them not to be able to delete or edit it again in columns A & B. They should also not be able to delete rows or columns. I have tried with other peoples help but as soon as the spreadsheet becomes a shared document I get a run-time error 1004 when entering in any box
View 9 Replies View RelatedHow prevent duplicate when entry from list?
Attachment file : testing.xlsx
I have a huge worksheet with 26 columns and 1200 rows. This worksheet is updated by 4 people everyday. Column C, H, N & R are updated daily by these 4 people so these are dynamic columns so to speak. I would like to have a VBA that informs that particular individual when he enters a duplicate data, maybe a popup saying duplicate entry.
View 9 Replies View RelatedI am adding up the value of a range of cells in one cell. What I want is If the value of the cell which is adding up the values of the range of cells exceeds a particular number it should throw an error.
View 6 Replies View RelatedI have a entry form in which i want to register customers. the first field is the customer number (which is unique(created by me) for every customer). This is TextBox 1 in the document. I would like to search for duplicates in worksheet 2, collumn A, when pressing "enter" to move from TextBox 1 to TextBox 2. A search for duplicates should start and a message should appear " duplicate found" if found, otherwise continue to TextBox 2 for further entry of information.
Please see my attached document for clarification.