I need a formula that will calculate if a Category is at Very High, High, Med, or Low level risk. I've attached a sample file for review. In the sample I use a countif and countblank formula to count the number of specific statuses used. On my summary page I want to use the counts to determine if a category is in one of the 4 risk levels. I've broken down additional information within the attached file.
View 9 RepliesI have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
Excel 2013
I am looking for Multiple IF formula that linked with options button.
If D10=True 16+(C12*2.10)
If D11=True 14+(C12*1.6)
If D12=True 12+(C12*1.3)
If D13=True 10+(C12*1.15)
If D14=True 16+(C12*2.10)
From D10 to D14, there is always one True and others are false.
When I try to insert an 'option' box in which either a check box or radio button are used, only one option can be used. For each option I have to create a new option. But then, each option can be selected, which defeats my purpose. I want to create an option box where there are two option in the box, but the user can only select one of the options, not both, or neither.
View 2 Replies View RelatedI am working on a grading sheet for our company. I would like users to choose from an option button choice (A, B, C, D, F) for each grading category and I would like to write that to a sheet that corresponds with the graded person. The form will have next and previous buttons to move through the grading process - when previous is chosen I would like the option buttons to populate what was stored in the data sheet. I think I can mottle thru the code individually but with 60 option buttons it may take quite some time.
Can I call an option button choice by "Group"? I started coding individually, but it will take a lot of code - surely there is a more efficient answer. A picture of my form is attached at the bottom of this post.
VB:
Private Sub OptionButton1_Click()Dim Grade As String
If Me.OptionButton1 = "A" Then
Grade = "A"
Else
Grade = ""
End If
untitled.jpg
I would like a VBA code that will display a large input box with multiple inputs boxes inside.
I have shown below the variables required (column C) and the default values (Column E). There is a combination of dates, percentage and numeric values....
I am using the following code to find a NAME in my work sheet and to enter their status. But this macro is not compatible if a duplicate or Similar name exist.What I need is I need this macro to activate the search NAME Cell and then a VB box with OK & NEXT button has to appear. if the activated NAME is exactly the one I am searching, then I will click the OK button then it should display the "Enter Employee Status" box and as continue in my code, But If I press NEXT, the macro should search the next similar NAME and activate it, This process should continue until I click the OK button. (Same like CTRL+F function with an additional Status entry button)
Code:
Sub Button1848_Click()
Application.ScreenUpdating =False
Dim BeginCol AsLong
[Code]....
I have a spreadsheet where there is an option to either select a pre-priced item from a drop down list or type in manual entries. If the data is entered manually, I have included a formula to obtain cost data. For example, column A has the number of labor hours and column B multiplies Column A by $500. However, if a person chooses to select a pre-priced item from the drop down menu, can the pre-entered data from the VLookup list overwrite the formula? I do want it to overwrite if it is possible for it to do so.
View 2 Replies View RelatedI am using countif formula to count number of times each name appears in a row, but when I use the filter opeion and select a specific name, and highlight the whole row, the 'count' at bottom right of excel screen does not show same count as the outcome of countif. why is this so?Shouldnt they both be the same?
View 4 Replies View RelatedIs it possible to use the userform when it is protected and shared.
I am able to access when it is protected. but unable to access when that protected worksheet is shared.runtime error message " unprotect method sheet class failed" appears it is shared and used.I have already raised this [b]issue in the forum.I understood from the answers that it is not possible.
" My requirement is multiple users should use the form and they should not have the access to edit the entires made by them"
Please give me a solution.
" Being a moderator myself i should only be able to edit or modify the entries made by the users."
Note:
I dont want to create multiple excel sheets for multiple users.i want to use a single excel sheet (Shared and protected)
Also provide me an alterante solutions if any
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.
This is as far as I have got but even this won't work:
=IF(I15="BUTT CUT",=VLOOKUP(J15,V13:Z22,2,FALSE))
I have listed data below for purposes of the formula:
HTML CustomerInvoice Invoice Amount
(Rep. Invoice Month)
Nike1 1,000
Reebok1 600
Adidas1 700
Puma1 600
Nike1 (250)
Reebok2 1,000
Adidas2 600
Puma2 700
How do I get the data above, into a spreadsheet like the one below? I have tried If/Than Statements with no luck. I need to total the amount of invoices during a specific month for a specific customer.
If you have 5 option boxes in the group at the top of a form and want to make sure the user of the form selects at least one box before continuing on, how do you do that?
View 9 Replies View RelatedI want to sum data in COLUMN C that is related to COLUMN B if the COLUMN F is blank.
View 10 Replies View RelatedI have the following formula =IF((F3=1)*(H3=1)*AND(J3=1),1,0) and I do need to add the option C1 besides 1 so it would be something like this...
=IF((F3=1orC1)*(H3=1orC1)*AND(J3=1orC1),1,0)
I lost my right click option in excel, all I get is a little gray box that does nothing. I know it is something I did today, it has worked perfect before. Has anyone seen this happen, the problem is only in excell 2003.
View 2 Replies View RelatedIn Excel 2003 VBA, what is the syntax for controling an Option Button or Checkbox by name or caption
I have tried the following but has an error, I am sure this is a simple one.
I am trying to have a msgbox come up if there is a path found it will pop up a msgbox for a few seconds then go away. Im trying to avoid the user from having to select anything just simpley telling them the connection is there then go away on its on. The code below is what Im currently using.
Code:
If Dir("H:") = "" Then
MsgBox "Error: Drive, path or file not found"
Else
MsgBox "OK: Drive, path or file found"
End If
I created a frame in a spreadsheet I put in 2 options buttons that I called Left the first one and Right the second one. I got 2 cells lets say C10 and G10 in these 2 cells I write the cell number values. When I click on button one or Left, I need the cell C10 be set focussed and write a number e.j 5. However, when click on the second butt to write a number e.j 7, the number in the cell C10 still there, so, what I need is a cero or an empty cell C10, and viceversa. Could you help to write a code for this problem.
View 9 Replies View Relatedi've used this a few times at the top of various modules, as suggested by various knowledgable people on here, but it might be a good idea to find out what it means, and what it actually does!
View 9 Replies View RelatedMy purpose is:
1) I have a macro workbook with multiple procedures (6 main tasks)
2) Some workbooks will use all procedures, and some will not use all procedures (most use either 4 or 6 of the procedures)
3) To hide the unwanted procedures as required, the menus will not appear for those choices (in code that creates custom toolbars on activate)
Solution (proposed):
1) send a paramarray argument with string elements that identify menus to use
2) send a “show all” true or false, to simplify identification of workbooks that use all procedures
Reason solution is chosen:
1) I can basically have one macro workbook this way – so simple implementation even where some workbooks won’t need all the macros (and should definitely not be making them menu options).
2) If I add more routines, I can just extend the ParamArray as needed and continue to identify specific toolbar options to use or not use.
Problem:
1) That paramarray is interesting! It can’t be passed and its initialized as an array with bounds 0 to -1. Also, it can’t be tested directly to see if its Empty. So I came up with checking for the -1 UBound, combined with loading the elements into another array (seems stilted to me).
I’m not sure what I’m asking, but…I need to determine if the paramarray is being used, and be able to pass it. Is there a simple way to do that? As I’m sitting here writing I’m realizing I could just pass a regular array and dispense with the ParamArray option -- well, I’ll post my thoughts anyway. Sorry this is so rambling…
Here’s the code I’m using (basically). Note that DoSomething actually checks each element of the array to see if it matches a potential menu item (by name), and if so, marks that menu item to be added - since there's 6 menu options, I placed it in a separate method to avoid have it in the routine 6 times.
Maybe I'm just curious if anyone ever successfully uses the ParamArray option - it seems to me to be somewhat of a bother that you can't easily tell if it's been passed in or not, or use it like a regular array (check if its empty, etc.).
Sub MyToolbar(ByVal blnShowAll, ParamArray args() As Variant)
Dim a() As Variant
Dim x As Variant
I made a msg box but i dont want any option to be displayed in it.. it only should display as an info to the user.
View 9 Replies View RelatedI have some VB code "For, next" that I have written with numerous variables. All the data appears to be totaling correctly. After reading some comments on this site I added Option Explicit to the top. Well, it stops on my first For Each Cell, with cell highlighted and an error message 'variable not defined'. I have searched and searched and can't find what it might be. Does this error message mean I am getting incorrect results? Is there an easy way to determine which variable is not assigned? A function key or something?
View 9 Replies View RelatedI am trying to simply the data input for someone who is not very experienced.
I have had some success but need to have an extra option in case of mistakes.
Ideally I need the following -
I want an inputbox that will appear when the user starts the macro and for example asks Enter Surname
Then I know what I want but I don't know how to do it.
I want a box to display the Surname that the user typed into the inputbox with a message asking Are Details Correct? with a Yes and No Button.
Clicking the Yes button moves onto the next inputbox clicking the No button cancels the whole macro before it enters data into cells.
I work on a worksheet that has 10,000 rows all in column A with members that have 353 different plans.
Here is an example:
Robert
James
Ted
Ronald
This is for plan 426-0
Amanda
Nikki
This is the total for plan 426-7000
I need to move the plan type for each member to column c. When moving the plan type I only want the number have the dash. For Robert/James/Ted in column C the plan type is 0. For Amanda and Nikki in column C the plan type is 7000. I have created a macro that can move the plan over but not the number after the dash.
Here is what I have come up with so far.
Option Explicit
Option Compare Text
Sub R007()
Dim i As Long
Dim sPlan As String
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Left(Cells(i, 1), 5) = "Plan " Then
sPlan = Cells(i, 1)
Rows(i).Delete
Else
Cells(i, 3) = sPlan
End If
Next
End Sub
is there a way to set all yes/no option butons to "no" when a worksheet is activated?
View 5 Replies View RelatedI have a COUNTIFS function that, among other criteria, only counts cells in which the value is "P". I now want to modify this function to count cells in which the value is "LA" in addition to cells in which the value is "P". I'm not sure how to work this into the argument. I tried using OR("P","LA"), but that's not working.
View 3 Replies View RelatedI'm trying to replace a bunch of checkboxes on a sheet with a bunch of Form Control Option Buttons. The problem I'm having is every option button I add seems to be grouped with all of the rest of my option buttons. If I add buttons 1 & 2, I need those 2 grouped together, and when I add 3 & 4, I need those grouped together. Currently it's grouping all 4 buttons together so 1 - 4 are linked, but I need 1 & 2 linked together, and 3 & 4 linked together. I've tried grouping, but it still doesn't function how it ought to.
View 13 Replies View RelatedAttached I have kind of a score card, when you open the tab named TEAM1 a userform pops up and I pick the partners that played together, then I can pick an option button labeled 0,1,2 or 3, when it unloads, is it possible to make it put the corresponding number by their names in the empty row (W) of the week they are playing?, and not just the 1st open row available?
For example, if I pick the names Ryan and Stu the number will go under week 10 (X3) and not find the empty cell in (J3)?, I have no clue how to make this happen, but I made a userform to use, to try and make it work.
POOL LEAGUE TEST.xls