If Formula With 3 Options
Nov 25, 2006
I am currently having trouble getting =IF forumla's to work, with more than 3 options AND an equation being taken into account. Attached, is an example of a spreadsheet, involving what I currently have, with Row 5 showing the formula's I've used, and rows 2-4 showing examples of 'Yes', 'No' and 'Equal' outcomes.
For Column E, I would like the value from the above cell (set to zero in the formula of the first cell) to:
-Go up 1 digit (from the above cell's value) for a Yes in that rows C cell
-Go down 1 digit (from the above cells value) for a No in that rows C cell
-Go up 2 digit (from the above cell's value) for a Equal in that rows C cell
As can also be seen from my example, I only have it working so that 'Yes' in C will take the value up by 1, and anything else (including 'No' and 'Equal') going down a point, which is only really using 2 options. I have tried numerous different possibilities for trying to get this to work, all of which gave me #VALUE error, or wouldn't even let me accept the formula. My current formula for what I have now (with only 2 options) is
=If(C5="Yes",E4+1, E1-1)
Basically, if possible, I would just like to make the E column change from 2 different ways (e.g +1 and -1) to 3+ different ways (e.g +1, +2 and -1).
View 3 Replies
ADVERTISEMENT
Jan 22, 2013
i have 2 columns i want a formula that will test both cells at the same time for different possibilities:
First Column Second Column
IN Place 1
Not In Place -1
Part In Place 0
I need to check for all these possibilities and return a grade for it
View 9 Replies
View Related
Sep 21, 2006
I will enter a figure into A1. I will also enter a figure into C1. In E1 I would like to enter the lower of: 0.2% of A1 or 75% of C1
View 5 Replies
View Related
Jun 25, 2014
formula for when i select from the drop-down boxes it returns a value that i have placed next to it on a separate sheet. I have attached an example of what i am trying to accomplish
View 3 Replies
View Related
Oct 1, 2013
I need to build a formula to have one cell display multiple options depending on a value in another box.
If Value is >x and <x display Y
Details:
Cell D5 Holds a dollar amount. E5 is where the calculation will happen. Logically i need it to do the following exactly:
If D5 is between 5000 and 9999, display 75. If D5 is between 10000 and 14999 display 150. If D5 is between 15000 and 19999 display 200. If D5 is >20000 display 250.
View 2 Replies
View Related
Oct 27, 2006
I have a table with rows that keep growing. But I have place formula in the whole of column F, i.e. F2:F66565. When I import information from MS Access into column A to E, the formula in F does not work, until I copy from F2 to the end manually. I have tried using Tools, Options, Calculation, Automatic. That doesn't work, I have also tried F9, that doesn't work and I have also tried checking Precision as Displayed under the calculation tax in Tools-Options, that doesn't work either.
View 3 Replies
View Related
Jun 11, 2014
I am trying to write a formula with the following:
-if I9=0, then write 0
-if I9=501, then write 1
-if I9=502, then write 2
What would be the best way to write this formula? I am sure this should be pretty basic, but everything I try comes up with an error.
View 10 Replies
View Related
Jul 7, 2007
i am currently designing a userform which has a drop-down box for user to choose from the options given. However, i would like to have in the box fields such as "--Subjects", "English", "Maths", "Science". In this way, the user will be shown a list of options of the different subjects for them to choose from as well as to clearly identify to them that these are the subjects available therefore the heading "--Subjects". I would also have to make sure that the user can only select "English", "Maths", "Science" and NOT "--Subjects".
View 11 Replies
View Related
Oct 12, 2009
We have developed an Excel workbook with hundreds of lines of VBA code written for a specific application, and we would like to protect it.
Could someone give me an idea of what security options we have over and above the below (if any)?
We have protected all sheets and the workbook itself with passwords. The VBA itself is protected with a passoword.
My understanding is that VBA can be hacked into quite easily.....is there any way of adding another layer of security/protection without having to move to another platform altogether?
View 6 Replies
View Related
Nov 26, 2008
numbers in one column need to be rounded to the nearest half decimal, with next conditions(ill took number 704,00 for example):
- if last two decimals are < 0,25 then my number has to be 704.00
- if last two decimals are >0,25<0,75 then my number has to be 704.50
- and finally if last two decimals are >0,75 then my number has to be 705.00
I tried with IF, CEILING, INT and ROUND functions but i didn't made it work with three options, that i need. I only made it work if i use only two options.
View 5 Replies
View Related
May 4, 2007
I would like to know how do I go about adding the following:
This is a test it counts out of 1250 points
But there are 3 fields which can be omitted from the grand total of 1250
Some students might not have all three fields. The fields totals are 30, 90 and 130. The students might have one or two of them in either order. Now what I would like to do is have 3 different fields where I can mark with a Y=Yes and N=No in a block. then it would change the Grand Total score accordingly.
View 9 Replies
View Related
Sep 5, 2008
How do I filter the options in column A to bring up results in Column B. I want to do a one-many filter where if the user selects an option in column A then the results/values in column B should be based upon the the user selection.
View 9 Replies
View Related
Sep 19, 2008
I recently added a macro in excel that stopped users from cutting and pasting and dragging etc. I got the macro from the web. I have delete the macro, but for some reason I cannot get it back to normal. If I right click on a cell the cut, copy, insert, delete, format cell menu is grayed out. The undo button also does not work? I have searched for the macro and deleted it but it seems to have changed something else. I have even tried to disable macros, but all menu items are still gone?
View 9 Replies
View Related
Jun 27, 2006
I use several different workbooks during the day, and sometimes I would prefer the " move selection after enter direction" option to = "Right" and sometimes "Down". It doesn't seem to be an option that is saved with the workbook.
what could be done to a workbooks code to change this option upon opening the book?
View 2 Replies
View Related
Aug 17, 2006
I have a userform that prints out pages using a autofilter, the code in the userform prints out 13 different criterias from the autofilter. But the problem is i have no control over what criteria i can print out. Theres the
ActiveSheet. Unprotect ("seasons")
Sheets("Day Handover").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Activities Pick Sheet").Select
Selection.AutoFilter Field:=1, Criteria1:="1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="2"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="3"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="4"......................
So i have design another userform that will able me to control what criteria can print out using checkboxes. But i don't know how to code the new userform so when i select a checkbox it removes that criteria from the code in the first userform called 'frmPrintOptions' and when deselect the checkbox it puts the criteria back in the code. Also the Second userform called 'frmAutoPrint' must remember what checkboxes have been selected and deselected.
View 5 Replies
View Related
Feb 9, 2007
I have a spreadsheet that I access over a citrix network, when I opened it at the start it gave me an option to enable or disable refresh external data, also enable or disable macros both of these are gone now and I have searched the net to find a way to get them back.
View 2 Replies
View Related
Jan 28, 2014
At the moment I have to click in the cell and then look at the cells used and look across to the title of the rows.
So for example, performance = D3*D4*D5*D6
I would like, performance = vehicles*availabliity*utilisation*TKM.
That is easy if I have just 1 option. But what if I have 3 options? Naming each cell would be a way to do it but pretty laborious, is there a 'smart' way to use named ranges here?
View 3 Replies
View Related
Feb 10, 2009
I have created a drop down list, but when selected the text is really small. I know this is because I am zoomed out, but if there any way for the text to be larger so that I can zoom out and still read what the options for the dropdown list are?
View 4 Replies
View Related
Nov 1, 2013
I have a table with a list of names of some products and their substances, firm, package and price.What I need to do is this. When I type a certain name in another sheet, I want it to show me a cell with the substances, a cell with the firm (that are fixed and cannot change them) and then in the next cell to give me options (if I type "package 1kg" to show me the price of this package, if I type "package 0,5kg" the price of this one, etc.).
View 6 Replies
View Related
Nov 10, 2008
For my school project, I must create a spreadsheet with lists. I am not allowed to have the data from the lists displayed anywhere on the sheet except for the results which will be hidden by the list (Combo box) in question. I have 3 such combo boxes and I need the options inside them to be hard coded into the spreadsheet. How do I do this?
View 4 Replies
View Related
Apr 18, 2009
I have a workbook (VBE.xls) that creates menu options in the VBE when it opens.
Several of them modify the code in the active codepane.
When the codepane is in VBE.xls, everything gets reset and my macros don't run anymore. I have to re-run the macro (menu_setup()) that creates the menu options.
I don't think there is anything I can do about it resetting when I modify the project that created the menus.
Is there some way to have each macro re-run menu_setup() AFTER it has ended (after the reset has cleared everything)?
I've tried having each macro open the immediate window and paste in "menu_setup" then tried to use "sendkeys" command to execute it, but I can't seem to get it work.
View 5 Replies
View Related
Dec 6, 2009
Having great success at using Countif and Sumif in totaling values across two sheets, but at the risk of sounding like a dummy I would like a suggestion as to which formula I should be using for this basic little function;
I have a column on a sheet with dropdowns offering 4 options; Active, Cancelled, Completed, and Suspended. I'm using Countif to total the number of "Active" but I also need to total the number of "Canceled", "Completed", and "Suspended" under the one heading, as if they all meant the same thing.
View 6 Replies
View Related
Jan 31, 2010
I would really like to know if there is a way that when my workbook opens if the following can happen?
- Not display the tabs (NOT "HIDE" the sheets, just not be able to view the tabs themselves)
- Go to full screen (not display ribbon)
View 6 Replies
View Related
May 4, 2012
I know I can figure out the number of possible criteria using FACT(#) if it is just a number. But what if i want to figure out the number of passwords that can be used if the password must be 6 characters and have at least 1 number and at least 1 letter (so it could be any combination of up to 5 letters and 1 number, or 5 numbers and 1 letter). To make it more complicated, passwords are case sensitive. Just wondering about this since whenever I have to reset a password I always wonder how many possible passwords there are to choose from.
View 9 Replies
View Related
Apr 10, 2014
I followed a youtube video on setting up a survey in excel, and it works great, but it was only for two options. I wanted to make it 5 options per question, but when I started adding to the code ( which I thought was correct, but was not ) I kept getting errors. It probably is a simple addition. How to make the survey options more than two?
There are three tabs. The first tab (Start) only has a button that leads to the QA Survey. The Second tab has the questions and answers. The third tab as three columns in it as well for the name of the person, question number, and answer choice.
Here is the code inside the form (QASurvey):
Code:
Private Sub button_next_Click()
''confirm there is a name
If TextBox1.Value = "" Then
MsgBox ("Please enter your name")
[Code] ...
Here is the code in Module 1:
Code:
''global variables
Public info() As Variant
Public results() As Variant
Public questionnumber As Integer
Here is the code on the sheet that has the button to start the Survey Form:
Code:
Private Sub Start_Button_Click()
QASurvey.Show
End Sub
View 5 Replies
View Related
Dec 13, 2006
I have a custom command bar & and I have several buttons in it.
I only want some buttons availble when a certain worksheet is active.
What code would I use to only make certain button available.
ie if Budget worksheet was active then the botton below would be active?
But if Overages worksheet was active the button would be inactive?
Private Sub AddMenuToCommandBar(cb As CommandBar, blnBeginGroup As Boolean)
Dim m As CommandBarPopup, mi As CommandBarButton
If cb Is Nothing Then Exit Sub
Set m = cb.Controls.Add(msoControlPopup, , , , True)
With m
.BeginGroup = blnBeginGroup
.Caption = "Page Go"
.TooltipText = "Page Select"
End With
View 9 Replies
View Related
Jan 31, 2007
I'm trying to make a spreadsheet where in a column (Column B) you enter a value from 1 to 5 and then depending on the value the cell would turn red (1), yellow (2), green(3), blue (4) or grey(5). I had used conditional formatting for this type of function before but Excel 2003 only gives the option of three possible conditions on any given cell. I could give up one color (grey) and limit the number to four options, but it still is one more option than allowed.
View 9 Replies
View Related
Jul 31, 2007
I have the following VB macro that runs after user presses OK on a userform. I don't think it's written correctly. I would like a prompt that gives two options (either yes to return a null value if they've left a field blank, or return user to a reset userform1 if they select no.)
Private Sub CommandButton1_Click()
If Me.TextBox1.Value = "" Then
MsgBox ("You Must Enter a Part Number (eg. 1007821-12)")
Unload UserForm1
Calculate
UserForm1.Show
End If
If Me.TextBox2.Value = "" Then
MsgBox ("You Must Enter a Lot Number (eg. 6020631)")............
View 9 Replies
View Related
Dec 18, 2008
When the user enters a date (A1), that date is cross-referenced with a database to determine 1 of 3 results, in which the results are displayed in A3 as follows - Date out of range. (value="DOR"), No records exist. (Value="NRE"), Records exist. (Value="RE").
Once the date is entered and the corresponding value determined, I would like to have a ppo-up message appear with options:
Date out of Range.
Message: "Date falls out of range of operating season. Please re-enter." ** I think I can use validation to take care of this. **
Message "Would you like to import data?"
Yes - open external application
No - default A1 to current date and await user input
Records Exist.
Message " x Diamond Records; y Field Records; z Court Records " where x y z are numeric values based on lookup from the referenced database
Message "Proceed to data"
Yes - takes used to next new worksheep
No - default A1 to current date and await user input
How / where do I code this to activate after the user enters a valid date?
View 9 Replies
View Related
Oct 20, 2009
I want a MsgBox to pop-up to the user with 3 Options.
Option A, Option B, Cancel.
If user presses Option A GOTO 1:
If user Pressed Option B GOTO 2:
And if Cancel End Sub, not sure if this is doable, please let me know. Basically I am writing this VBA to archive files from the network drive and there are two different kinds and depending on what Option the user chooses, the Destination variable needs to take on a different value, that's why I need this, unless there is a better way to do this.
Basically so far my code is like this:
Sub Archive_GTP()
Dim Nlog As Worksheet
Dim DestinationFile, SourceFile
Dim lr As Long
Dim c As Range
Set Nlog = Worksheets("Log")..........................
View 9 Replies
View Related
