How can I make a cell mandatory, if another Cell states XX?
For example:
If column B has "agency" then column C must be completed stating which agency was used?
However sometimes column B could say "no agency" therefore column c doesn't need filling!
I trying to require data entry in Cell 2 if Cell 1 contains text. Normally Cell 1 is blank but once the user enters data (its a text field) I want Cell 2 to then become mandatory for data entry. (Cell 1 is actually C5 and Cell 2 is actually DK5. So formula I am thinking if is: If C5 contains data then DK5 must also contain data. If C5 is blank, the DK5 is also blank.
View 4 Replies View RelatedI am currently trying to adapt the following code which is in VBA (Sheet1). It currently ensures mandatory cell entry of cell C2 before saving, and if this is not done then it would not save the item into the relevant directory. I would like to adapt this code so that cells B2 to R2 are all mandatory, and if they are not filled in a message box with the cell title (which would be in B1 to R1) would pop up. If the cells are not filled in then it will not save (the same function it carries out for cell c2 currently). The code is attached to a command button, i have tried to make the additional cells mandatory by playing around with the If Trim(.cells(2, 3).value) part of the code with the AND function etc however i have had not had any luck.
View 7 Replies View Relatedhow can I make a column mandatory?
basically if someone puts data in column C, they have to put data in column D
(people are not putting important information on my spreadsheet, so i would like to make that column mandatory)
I have a worksheet with running macros which hides & unhide rows based on input of data in yellow highlighted cells (see attached). I'm trying to make all cells mandatory. The below code works but I run into problems when certain cells made mandatory are hidden. For example if I select "Expense" as Type of Expenditure in the attachment cell C10 & C11 are hidden so if its mandatory I won't be able to move forward to next cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Me.Unprotect "dawnwh81"
Dim myCell As Range
Dim myRange As Range
[Code]....
I am trying to insert custom formatting into a cell so when I enter a set of 9 numbers for phone numbers in the U.S. and 14 numbers for international phone numbers, it will automatically display it in the correct format. Reason for the 0's on the international is because country codes often start with a 0, so I need to ensure it isn't removed from the number.
View 4 Replies View RelatedI'm trying to get a VBA macro put together that would delete all the rows, throughout every sheet, and shift up wherever the cell in column A = "Delete Row" in a workbook I've made. In the below copy of the workbook you can see that a lot of the information from all the tables is linked to the topmost table on the 'Mix Detail Sheet'.
If the the Cell in column A is blank on that first table, its corresponding cells in tables throughout the workbook displays the words "Delete Row". I need the macro to delete the rows and shift everything up where this occurs.
Ideally I'd also like the macro to then delete the rows in that first table where the column A cells are blank.
In Column B of my spreadsheet i have a drop down that only allows users to enter "Yes" or "No"
Is it possible to make Column C a required field if "No" is selected in column B.
So basically if cell b4 is "No" then i want to make it manditory that cell C4 is filled out with a reason why.
Same thing for cell b5 then i would like cell c5 filled out before they continue using the sheet. etc.
We have spreadsheets which record our sales team's prospects and orders. In order for the sales director's summary spreadsheet to be correct, the month which the order came in has to be filled in and correct.
What I would like to be able to do is when column K of a line is turned to "100 - Purchase Order In", a message to pop up saying something along the lines of "Is the Month In correct?"
Looking through previous answers, I think that this is something along the lines of what I need:
Sub Print_Out()
With Range("d6")
If .Value = "" Then
.Select
MsgBox ("Make sure you enter your surname")
Exit Sub
End If
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
However, I don't know enough about VB Code to know how to change it for my purposes...
I am trying to write a macro that will make the bottom cell in a column equal to the top cell. The top cell will not always be in the same row and there won't always be the same number of cells.
View 1 Replies View RelatedOn sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
how to make a cell a color depending on a number in a different cell? Example...if A2 is 92.6 how would I make D2 blue? But if A2 were 91.9 how would I make D2 red?
View 5 Replies View RelatedIs there a way to create a multiple dropdown (Similar to Windows Start menu) so that the user can select the item and its properties then the selections be placed into multiple cells? For example, i select wood from drop down list and i get another drop down list asking for the type of wood, I select MDF and another drop down ask for thickness. when I finished my selections, then the selected Items copy themselves into 3 different cells.
I have a drop down in (A1) cell and I select an item from it. I want (A2) cell to check the name in (A1) then call up another drop down i already made in (A2).
I have several hyperlinks that may or may not contain data, the linked cell will display "0" if there is no data. When I print I dont want the "0"s printing.
How do I make the cell display nothing when a hyperlink cell is nothing?
I want to make a cell blank if nothing is written in another cell. I have used this formula in C4: =IF(ISNUMBER(C3),IF(C3<180,C3+180,C3-180),"")
View 1 Replies View RelatedI have a monthly forecast document that is updated by an operator monthly. At the end of the month, the operator presses a "Copy Accrual" macro that copies accruals for the month and stores them on a seperate tab. After sending the document back to the Finance team (necessary step due to software complications), Finance will send the operator back an updated file at beginning of next month. The operator than presses a "Paste Reversal" macro that is supposed to input the negative of the accrual stored in the seperate tab. The problem is that it is posting as the same value (not negative of value). I tried recording a Macro to fix this, but it did not work. An example of the code that came up with is:
Worksheets("Weekly Forecast").Select
Range("B9").FormulaR1C1 = "=-Accruals!RC[-4]"
Range("H9").Select
Selection.Copy
Range("H10:H33").Select
ActiveSheet.Paste
Thoughts on what to do? I think the two things that are throwing me off is the negative, and moving between the different sheets.
I am looking for a way for a cell to equal another cell but the cell that it looks at is controlled via another cell.
So, for example, cell A1 I type in “E5” and this tells cell B1 to display the contents in cell E5.
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
View 2 Replies View RelatedI am using a “borrowed” formula from a coworker that I do not really understand however it is working for me, sort of.
=+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V17,$W17,$X17,$Y17,$Z17,$AB17,$AC17,$AD17,$AO17,$AZ17)))),I F(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="FFFFFFFFFFFFF FFFFF","FAIL","PASS"),"FAIL *")
What this does is read the range of cells and immediately fails a quality check if it is in a critical cell, or fails if multiple non critical items fail.
I want to use an if/then to make cell blank if there is no data in one particular cell (which means no quality inspection performed). Here is what I tried:
IF(V18="","",+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V18,$W18,$X18,$Y18,$Z18,$AB18,$AC18,$AD18,$AO18 ,$AZ18)))),IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="F FFFFFFFFFFFFFFFFF","FAIL","PASS"),"FAIL *"))
The leaving the cell blank part of the formula works however it allows the remainder of the formula to only work if “fail” appears in the first cell it reads (in this case V18). If the “fail” appears in any of the other critical cells, it does not work at all.
Also as a side project not sure what the substituting to $R$2 does so if anyone knows how to clean this up, let me know.
How do I make a cell value update to equal the furthest value to the right in the same row?
This cell just needs to look along the right in its own row and grab the value furthest away to the right. It will be a letter value, not a number. eg, AB or AC or AD or AE. It will not (in this case) need to look further than about 150 columns or cells.
When I click a cell (say A1) I want a specific message to appear in another cell (say B1); when I click A2, a different message should appear in B1 (basically B1 will be my display zone). The reasons I need it as such are that the sheet is a calendar and so putting all of the messages in would look ridiculous, and I really need a static position (B1) for the messages.
View 9 Replies View RelatedI have an excel file where the user has to enter his user name and password. But the password that is entered into the cell should appear as Wildcards(*) to everyone (Like our Windows login). Is there any way to do it.
View 1 Replies View RelatedIs it possible to have an "If this cell equals this amount, then make a picture appear" scenario?
Say there's a blank sheet, but if cell A1 is 1, then there will be a picture (or shape) in the center of the sheet.
I want to make a list from data table in Sheet1. In order to make the list I use Quantity value in B column at data table.
For example;If quantity is 50,List will be 50 rows with the data columns.
I have tried the following code, but doesn't work unless I protect the sheet:
Range("A1:A4").Select
Selection.Locked = True
Selection.FormulaHidden = False
If I protect the sheet, then it works fine, but how can I make a cell read only
WITHOUT protecting the sheet?
So I'm trying to use the SEARCH function in excel to search for a particular text in a cell. I am trying to create a macro for it.
View 9 Replies View RelatedDim varFind As Variant
Set varFind = Cells.Find(What:="Closed", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not varFind Is Nothing Then Range(varFind.Address) = Hidden
something like
Then Range(varFind.Address) = Range(varFind.Address).row.Hidden
I made a drop down list in cell H11 for Marital status (married,defacto,single,widow). Those are the 4 options in the drop down list.
Now im trying to make in another cell IF (H11="Married" OR "Defacto,950,false)
but wont work, i know im wrong but i learn by trial and error or last resort MREXCEL and m ytrial and error didnt work.
I need a VBA code to make everything entered into a particular tab, in excel 2007, all capital letters.
I've seen it done, i just don't know how to write the code.
I've seen it done, and had the formula on a couple documents, but had a loss of data. What I'm looking for is the formula to enter into a cell for copying the name of the worksheet into that cell.
View 6 Replies View Related