I would like a formula which will give a Y or N response in Column C.
If there is content in Column A or B the result should be Y.
If Columns A or B are empty the result should be N.
Example 1
Column A = 2
Column B = 0
Column C = Y
Example 2
Column = 0
Column = 0
Column = N
I have this formula: =(CONCATENATE(COUNT('Master Report'!R8:R34)," ",IF(COUNT('Master Report'!R8:R34)>0,"Departments are over Action OI 50%","")))
when there is nothing to count, it places a 0. I want to to be blank. How can I achieve this.
I created added conditional formatting to the Response Due column, to keep track of when response was due.
I only want the conditional formatting to be applied if the cell next to it is blank.
For example, the last one on my attachment is due 3/26/14 but I have already submitted a response (see column K)
What can I add so that only those dates next to a blank in column K get the conditional formatting?
Sample.jpg
The following formula is giving me the "" response even though I51 = (40.00) and L51 = 40.00. What am I missing?
View 4 Replies View RelatedI am trying to achieve a response from a msgbox which will either continue with the sub or exit. I think my code is right but it doesn't seem to work and just carrys on regardless.
View 6 Replies View RelatedI'm trying to find a formula I can use to figure response time. The working hours are 7:00 AM to 3:00 PM and I do not want to count the hours between 3:00 pm and 7:00 AM of the next day.
Example:
Cell A1 has the call in DATE 12/06/07 and cell B1 has the call in time of 10:00 AM.
Cell C1 has the response date of 12/07/07 and cell D1 has the time of 9:00 AM.
So what I need is a formula in cell E1 that would count from 10:00AM untill 3:00 PM on 12/6/07 then pickup the count on 12/07/07 at 7:00 AM and go untill 9:00 AM.
So in this Example the final response time would be 7 hours or 7:00.
Trying to accomplish a bit of data validation...
4 cells
A1 B1 C1 D1
The only acceptable input for these cells in a 'x'
If I place an 'x' in any one of the four cells than the other three must be blank.
if cell a24 has nothing in it how can i make sure a77 is blank at the moment it returns a zero
View 2 Replies View RelatedI'm working with a substantial amount of code (which, due to its size, has been spread out into different modules). Whenever I transition from one module to the other, I have placed a msg at the beginning of each module to prompt the user to simply insert an integer to signify which version of the automation that is run. The following is an excerpt of a sample msg
[CODE]
Dim Msg, Title As String
Dim MyInput As Integer
' Define message."
[Code].....
Where, whatever report is being run, will be the same throughout all of the modules (for example, if you run the "Day" Report" at the beginning, the rest of the automation should be run using the "Day Report" code, not the "Night Report").
Is there a way to do one of the following
1. Label the input of "Day Report" or "Night Report" at the beginning of the automation (where the above except of code is how that would be done), and that can then be the determinant through the rest of the modules
2. Set my code in a specific module to, when it completes, automatically go to the same style of report (Day or Night) in the next module.
I have an input box that should only have numeric input. I have arranged for it to start loop back to the beginning of the box if someone enters letters instead of number, simply hits enter instead of making an entry, exiting on cancel. Now I need to have it loop if someone hits the space bar one time or more. Here is what I have:
Delta: On Error GoTo ErrHandler1 Response = Application.InputBox(Prompt:=MyPrompt, Default:=MyPrompt2, _ Title:=MyTitle) If Response = 0 Then GoTo Delta Z = Response If Response = Cancel Then MsgBox ("You have chosen to exit the macro. Shutting down.") Exit Sub End If If Response = False Then GoTo Delta If Response = 0 Then GoTo Delta
Trying to Have a User Pick A response from the first drop down list before the second is activated and then after they choose from that list have an error message appear if the vlookup formula returns a #N/A. Here is the code in it's entirety the problem area is in the Elseif Pipe category Right now it doesn't bug but it also doesn't wait for a choice to be made it just spits out all the messages
Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim Add As String, Size As String, Sch As String
With Range("c11:c52"). Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=MaterialList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With
Private Sub CommandButton1_Click()
Dim hojaRES As Worksheet
Dim howMuch As Long
Set hojaRES = ThisWorkbook.Worksheets("Resultado")
howMuch = 0
If total.Value = "" Then
MsgBox "¿Are u sure u want a cero?", vbYesNo, "the total value is 0"
End If
End Sub
I need to set the value of howMuch to 0 if one selects YES and ask for a new entry if one selects no. How can I specify the behaviour in depending on what the user decides?
I am trying to get Excel to search A1 and B1 for specific information and return a result in C1.
E.g. If A1 contains the text "Jumping Castle"
and
If B1 contains the text "4"
Then
C1 will produce text of "170"
But the formula must also detect and return a different amount if the information in B1 changes.
E.g. If A1 contains the text "Jumping Castle"
and
If B1 contains the text "7"
Then
C1 will produce text of "250"
long time reader, 1st time poster:I have a spreadsheet which uses =F to give set responses i.e.
Cell H35= =IF((G7-G35)=0,"No Issue",IF((G7-G35)>=1,"System/Paperwork do not Match",IF((G7-G35)
Is there a way to search responses from a specific Person? I think it was before the Mr Excel format change. Domenic was the responder.
View 2 Replies View RelatedI have an Excel worksheet that when a user enters something into cell A1 I want to clear cells (b2:b20). If I use an object, I can attach a macro to it, but I don't want to use an object. I want to use an Excel cell and then when the user types into it, I want to perform an action.
View 9 Replies View RelatedI found the below code in one of the posts and it was working fine uptill few days back. now, i get the error Run Time error '-2147220975(80040211) The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available
Sub CDO_Send_Workbook()
Dim iMsg As Object
Dim iConf As Object
Dim wb As Workbook
Dim WBname As String
Application. ScreenUpdating = False
Set wb = ActiveWorkbook
WBname = wb. Name & " " & Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
wb.SaveCopyAs "C:/" & WBname
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults....................
I am trying to work out how to stop input into a document if a certain response is selected from a form in excel 2007. Basically, i need some programming so if a question is answered a certain way - the user cannot continue completing the form as it becomes "locked" and an error box comes up saying this is the case - and what action needs to occur. As the list is a data validation list - i cant just separate it out - so that if one response is chosen over another then the error message appears.
For example the question might be "Will you use contractors". If the answer is Yes, the cell turns red and an auto response fills the cell (VBA has already been written into the document for this to occur). What i need additionally is an error box to come up saying "This audit cannot continue as a Minor Plan needs to be completed" and the rest of the questions responses say something to the same effect.
I need a sumproduct to count response times in between 1-2 hours. The response times are calculated from an 'initial referral date and time' and a 'actual response date and time' (the difference between refererral and response time be across several days) and are in the format below:
Time between intital and contact
00:22:37
00:00:01
00:08:46
I have tried several different formula, but I can't get excel to recognise the time range I need. Its just the bit in red i need sorted.
=SUMPRODUCT((MONTH(Referral!$H$2:$H$1048576)=4)*(Referral!$E$2:$E$1048576="Initial")*(Referral!J2:J1048576>1)*(Referral!J2:J1048576
I am building a template for a report. The report resides on the first sheet of the workbook and the subsequent sheets is where the user would paste the appropriate reports so that the formulas on the first sheet can pull the appropriate data. I have all my formulas working fine with one exception.
The data in question needs to be pulled from the sheet "Paste Adcap Report Here" and the column is AA. The common piece of data between the two sheets is in column C of the Adcap sheet and column B of the main report sheet. What I need to do is conduct a vlookup between the report sheet and the Adcap sheet using the account id's and then return either the date value in row AA or the word ongoing which would be those accounts which do not have a preset end date. So far a co-worker had been able to come up with the following:
=If(VLOOKUP(B16, 'Paste Adcap Report Here'!C:AA,25,0) >0,"cap","ongoing")
The code is a bit screwy as she had to leave early. The problem we encountered was the entry in column AA for a non-ending campaign. It is represented by two hypens (--). This is what tied her up. we could not figure out why the formula was returning an #n/a instead of the term "ongoing" when it encountered the (--). Again, if the formula pulls a (--) then the word "ongoing" needs to be displayed and if the formula pulls an actual date value then it needs to display this date.
In which control of vba can I show output of my work to a user of the program? But there is a trick here. In the output form, I need to ask something to the user so that I can proceed. (I need to put a few labels and a text box in the output form)
View 9 Replies View RelatedI know how to create a basic dropdown list, but what I don't know (I don't even know if it is possible to do at all) is how to allow user type-in response if the "Other (please specify)" option is used.
Column A
Question 1. What type of report are you using?
Column B will contain
Type I Report
Type II Report
Other (please specify)
If Type I or II is chosen from the list, I don't want user to be able to change anything. But If the Other (please specify) option is selected I would like to give user an ability to type in type of the report he/she is using. Also, is there a way to have list options chosen based on another list response?
I need a macro to provide a message box with yes and no buttons - how do I get the macro to act in accordance with the button pressed by user?
View 2 Replies View RelatedEssentially what I want, is to type a medication in, lets say, A2, and for a macro to produce a response in B2 based off of grouping criteria from another worksheet. My workbook better explains what I would like to see happen. A macro would be best for this, right?
View 7 Replies View RelatedBelow is some code that I found on the internet some while ago. When Excel opens this code runs which brings up a message box prompting a user to select an option. If no option is selected within 15 seconds then the macro runs, else it depends on the selection entered.
The problem that I am experiencing is that sometimes when the user does not select an option the macro does not execute after the coded 15 seconds. The message box just sticks around until a selection is made.
Most of the time it works just fine... some of the time it doesn't. The problem would appear to have gotten worse since upgrading to Excel 2010.
So, my question is either:
1. Why the code does not execute as expected each time Excel is opened?
2. Is there a better / more robust way of executing the below code without using "shell"?
[Code] ......
I need to calculate average response time, the problem is that it should be based only on "core hours" and that's between 7am - 6pm.
How to create function that will exclude "out ot hours" time (6pm-7am) from the calculation.
I have a list of items in column A ("Questions"). Let's use 3 for this example.
I have seven respondent GROUPS, represented by a column.At the intersection of each QUESTION and each GROUP, a number appears, indicating the most popular answer to that question expressed as a number from 1-6, or BLANK if no response was the most popular answer.So the data looks like this:
Group1
Group2
Group3
Group4
Group5
Group6
Group7
Q1text
1
5
5
3
[Code] .....
Now... the part that will seem upside down...I want this sheet to allow the user to specify a GROUP and a RESPONSE ID (1-6) to produce a LIST of QUESTIONS that will be used as the contents of a dynamic dropdown.For example, if the user selected GROUP6, RESPONSE4, I'd like the dropdown to contain:
Q1Text
Q4Text
If the user selected GROUP1, RESPONSE1, I'd like the dropdown to contain:
Q2Text
Q4Text
It is not possible for the RESPONSE selection to be "Blank", though blanks do appear amongst responses.
I can't get solutions to switch dynamically based on the selected GROUP.
I have a function that works well for checking if a specified file exist within a specified path. I realized I do not know how to just check if the folder exist!
Example: I want to see if the following folder exist with boolean response...
"C:MyCompanyMyDeptMyFolder"
I have a few subs that use userforms. They all work ok except that when they are filled out and the final click to close them is done , e.g. command button "OK" or "Cancel", the button is slow to respond. I usually end up having to click twice, or click and hold for a moment before it will register.
It appears to me that the program is hanging around in the last highlighted box the cursor was in and its slow to notice a command button has been pushed.
I have been doing vlookups for ages and have never seen this error. The vlookup works in some columns but not in others.
View 9 Replies View Related