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
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).
I am using excel 2010 and cant see the paste special options in any of the worksheets. If I restart my laptop then it shows up. I noticed that after opening Internet explorer, the options disappear once again and restarting the system brings them back.
View 1 Replies View RelatedIn Excel 2003, when choosing colors for cell patterns, it only gives me the option of 40 colors. How do I get it to offer me the "More colors..." that you see when coloring shapes that brings up the hexagonal color pallette?
View 9 Replies View RelatedI 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 RelatedHow can we "read" information from the VBE Error Trapping options (Break on All Errors/ Break in Class Module/ Break in Unhandled Errors) programmatically?
Background: I am developping a spreadsheet that needs to be sent around to distant users. Some of them might not have the default "Break on Unhandled Errors" setting), which would just make my programme fail or risk to fail...
This issue has been covered in the thread below, but the proposed solution doesn't work on my side (always returning the same output!): [URL] .....
Hint: it has been suggested to look into the registry, which the linked post does.
Ideally, I would love to be able to change the setting programatically, but that seem near to impossible in excel 2007 VBA (unless calling an external programme that closes and restarts excel after having changed the option?...)
I have 64 code ie: a1.c4.f6 etc and 64 corresponding value ie: $160,$180,$200 etc.
How can I get excel to check all the options to give the correct valuation.
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 RelatedWhen I copy a cell with CTRL-C and paste elsewhere, a small clipboard pop-up appears to give paste options. The pop-up is the size of an average 'starter' cell. I find it a nuisance as it always covers a cell I might want to paste into, but I cannot see that cell anymore because of the pop-up. How can I get rid of the pop-up?
View 2 Replies View RelatedI 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.
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 RelatedIs it possible to do a drop down list that allow user to select pre-defined selections but everytime when a selection is chosen, the list reduced?
Example,
I have can choose Apple, Orange, Durian and Mango.
The drop down can only select these 4 fruits.
So there are 4 cells in Column A1, A2, A3, A4.
A1 selects Mango and then A2 will only have 3 options to choose. So until the last cell, he can only choose the last fruit left.
A1, A2, A3, A4 are not selecting in descending order thus it could be A3 selecting first, then A2 and then A4.
I'm using excel 2007
I'm working on a decision matrix to evaluate different alternative production methods. Before that criteria have to be choosen and weighted against each other. The user would fill out the yellow/ blue cells with (0=less important; 1=equally; 2=more). Then values are added up and put into some kind of ranking.
The example was done by hand, which costs a lot of concentration. I would now like to extend the matrix - do you know how to fill out the grey cells more efficiently?
(Excel 2007, Win7)
I'm currently using Excel 2010. Before, when ctrl+v'ing a cell that wasn't a number (but was, for example, a function), I could immediately after press ctrl, bring up a paste menu, and press v to paste as a value.
So before, if I had a range of cells that were functions (say something simple like "=D3/E3" on F3 and dragged down), I could do the following to paste the range into another worksheet:
1. Ctrl+shift+down to select the entire range
2. Ctrl+C
3. Ctrl+V into a different worksheet (at which point everything shows up as either "#DIV/0!" or as the wrong number, depending on cells to the left)
4. Ctrl (brings up the paste menu)
5. V (selects "values")
6. The entire range is now pasted as values, and not as functions
Recently, however, clicking on "ctrl" after pasting brings up no paste menu. I haven't changed any settings. I will lovingly serenade the first (and second, and third) man (or woman) that figure out what settings I must change or what I must do differently.
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
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.
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 RelatedWe 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?
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.
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.
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 RelatedI 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 RelatedI 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?
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.
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 RelatedAt 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?
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 RelatedI 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 RelatedFor 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 RelatedI 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.