View attached file. I have a complexity rating that I have developed for a set of projects that I am working on. Management of my company have signed off on this matrix as they believe it is most appropriate. In my projects pipeline tab, I have created the risk and complexity ratings using logical commands.
projects.xlsx‎
I am trying to build a logical formula and it turns wrong values. I have four criteria in numbers, the codes are: 1 invoices, 3 credits, 7 Debits, 8 Returns, 9 Payments. All of these codes have positive numbers. I am trying to convert "credits", "returns", "payments" in negative . the rest "invoices" "debits" stay positive. In column A i have dollar values all positive, column B "codes" mentioned above, column C "i want to put the correct values.
View 9 Replies View RelatedI am trying to create a logical test with three possible results:
h2 <10.2 "needs improvement"
(if false) h2=10.2 "meets standard"
if false h2>10.2 "exceeds standard"
Basically, I am referring to a cell to determine if it meets, exceeds or does not meed a specific standard. In this case 10.2.
I have a set of data in two columns, i.e. 1) dates and 2) names. Now I would like to post in the third column the name "x" if there is more than 2 years (or 730 days) between the current and the previus observation for name "x".
More specifically:
I would like the code/ function to always include the first "new" name then: 1) check whether subsequent names are equal, if equal; 2) check whether the difference between each obervation and the first "same" name is more than 730 days. The code should also always consider every observation with more than a 730 days difference as a "new beginning" for that specific name.
I have tried to use IF and OR functions but the function becomes too long and it does not quite solve the whole problem. Problems with my function occurs when the names change in column two. That is, my function is not able to distinguish between dates that belong to the same name and dates that belong to different names, when performing the second logical test.
I have attached a short example of my data. The problem is solved manually in the example file, just to give a better picture of what I am actually trying to do (I have a few thousand rows in my real file).
Sometime it’s so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isn’t . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then it’s should equal to the value in the box C7. Here is the formula I wrote that doesn’t work.
View 4 Replies View RelatedHow I use the Private Subs for Workbook_AddInInstall and Workbook_AddInUninstall with my addins and confirm it is nececessary to use them?
For 2 weeks, I have not been able to get my addins to activate and load into the AddIns tab. I think these missing Subs are why. Ironically, Every example on how to create addins never mention this step or it being necessary. I've even looked in Macros book and it does not mention anything about needing these subs in the Creating AddIns section so they will show up in the AddIns Tab.
How I can get my Addins in the tab and not the Quick Launch menu
I lost my command bar and tool bars (excel 97) all I have is the blue bar on top then the column letters. How do I restore them?
View 9 Replies View RelatedI would like someone to explain the reason why when the program is run, the maximum value in column S successfully is selected by being colored in pink, but the minimum in column R between those pink max's (indicated by the entire row outlined in red) is not the minimum.
In other words, look at the numbers in column R. Only the 14.08 rows are supposed to be boxed in red. I can't understand why the 30.08 and the 46.08 are boxed in red??? And puzzling to me is why the 46.08 near the top are boxed, yet the 46.08 near the bottom are not boxed.
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I just want to have a column sorted by Z-A order and then A-Z by the same button, I recorded 2 macros on 2 buttons and tried to join codes with "if, elseif".
View 6 Replies View RelatedIs there any way that i could open and enter commands in ms dos
controlling from excel vba code?, like, say i wanted to change the current
directory in msdos from d: to c:, is there anyway that i could do it from
excel vba?
I have a template sheet where the only thing the user need to do is fill in all the appropriate cells and the click the button that says SAVE to save the file to the right folder and file name.
I want to Grey out the ribbon completely so that user can even change the font or any thing else for that matter. The sheet and workbook is protected. Need a way to stop any unwanted tinkering.
I have run into a problem with the Insert menu.
In the submenu
Cells
Rows
Columns
Worksheet
are "Greyed out" and therefore not available.
This happens when the application is first opened - no code is running it seems to be associated with the Personal workbook?
I have not encountered this previously.
IT help desk was its usual high standard - ZIP
I have searched this board without success.
The Microsoft Help functions were also silent.
Sub elaseval()
For each cell in Range(“B7:B50”)
Range("cell.Value").Select
Selection.Copy
Sheets("Input").Select
Range("$E$7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sensitivity").Select
Range("C” & cell.Row : ”T” & cell.Row").Select
Selection.Copy
Range("C" & Cell.Row).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next Cell
End Sub
I am trying to apply this set of commands to each cell in the range B7:B50. For each of these cells, I need "cell.Value" to refer to the value in the respective cell and "cell.Row" to represent the row of the cell that is currently being used.
I am trying to remove File Commands from this specific workbook and force save and send functions through command buttons.
This is the code I tried in the workbook module to remove file commands(Excel 2003):
__________
Sub Workbook_Open()
Dim a As CommandBar
For Each a In Application.CommandBars
Application.CommandBars(a.Name).Enabled = False
Next
Application.CommandBars("File").Enabled = False
Application.CommandBars("Edit").Enabled = False
Application.CommandBars("View").Enabled = False........
I have made an Excel sheet that uses ActiveX controls (Option Box and Check Box). I have included a couple of screen caps at the bottom of this post to better explain the layout. Here are the specifics:
There is one Option Box Group labeled "InputOptionGroup" with choices labeled: "FirstOption", "SecondOption", "RefundOption" and "NoneOption".
Adjacent to the Option Box is a series of Check Boxes that correspond to the choice made in the Option Box with the exception of "NoneOption". The Check Boxes are labeled "FirstCheck", "SecondCheck" and "RefundCheck". Each Check Box object belongs to the group labeled "OutputCheckGroup".
I do know that the value of the Option Box choices and Check Boxes can be either True or False depending on if it has been chosen or marked, but I do not know how to incorporate these values into the action script. With that being said, here's what I'm trying to accomplish:
...when "FirstOption" is chosen, clear values of all Check Boxes and place a check mark in "FirstCheck".
...when "SecondOption" is chosen, clear values of all Check Boxes and place a check mark in "SecondCheck".
...when "RefundOption" is chosen, clear values of all Check Boxes and place a check mark in "RefundCheck".
...when "NoneOption" is chosen, clear values of all Check Boxes (no furthur action).
Also, there are a couple more requests:Is there a way to lock the Check Boxes from user input? I want the Check Box values to be altered only by the choice in the Option Box.
Take the "GPIN" field, the one on the right is locked from user input. This particular cell references user input from the "GPIN" field on the left. Is there a way to return a blank value when the input field is blank insted of returning a "0"? If you notice, all fields on the right reference blank values as "0".
In the left "Amount" field, I need to display a "$" followed by the value in the input "Amount" field
I need to disable the "save as" file menu command when a user opens a shared document. Upon exit I want to enable the save as so that other opened worksheets are not affected. This code works: CommandBars("File").Controls("Save As...").Enabled = False
My problem is this. I can activate this command by selecting run in the VB editor.
But I need for this action to be automatic when a user opens the worksheet. I did this in the editor:
Under general
Sub dISABLESAVEAS()
CommandBars("File").Controls("Save As...").Enabled = False
End
Sub WORKSHEET_BEFORECLOSE()
CommandBars("File").Controls("Save As...").Enabled = True
End
I want to accomplish a few things in one cell. The basics are this:
I want to pull the lowest numbers from a given sample. The sample needs to have a time component (10 lowest out of the most recent 20 results). Those numbers are to be averaged. That average is to be multiplied by .96, then that final yield needs to be truncated to 1 decimal.
Now, I'm not sure this is possible or not. But, if it is, I want to also select the amount of numbers I pull from the sample to average to be based on the sample size itself. For example, if there are 10 results in the sample size, I only want to average the lowest 3 of the 10.
Is it possible to formulate all of these rules into one cell?
I have the code that accompanies the attached spreadsheet model that locks the copy, paste, cut, double click and so on, with the right mouse button and shortcut keys, however, the toolbar excel these functions still remain active. How well I block access to the toolbar.
Desable_Ribbon.xlsm‎
I need to transfer two commands to command prompt. The first one is always the same and it calls a command line of another application (BEAM). The second command is for the BEAM command line. It is taken from an Excel cell. The code I managed to write is transfering only the first command, probably because after execution of the first command command prompt is getting BEAM command line. I
[Code] .....
I have not been able to find any references to anywhere, so here I am....
I'm using Excel 2003 that has a menu bar added by another program that interfaces with it (via DDE, as I understand it). The DLL's from the other program have no documentation and/or ability for me to connect to the particular drop-down menu command I'm using. It is a refresh command that re-imports stock market trade data because DDE drops trade messages.
What are the different commands for pasting data from excel to word? The code I have below pastes excel cell data to word as a picture. What if I want to paste excel cell data as embedded in the word document (I don't want it linked, just want the user to be able to edit it as an Excel sheet within the Word document). Thinking that I need to change the "DataType:=wdPasteText" part, but I don't know the correct syntax. Tried wdPaste but that did not work.
sub mysub()
Dim WdObj As Object, fname As String
fname = "myfilename"[code]....
Is there a way (with macros, I''m assuming), to make my workbook always return to the Main tab and Save automatically --- every time somebody closes the workbook from any tab - so that the next person who opens the workbook will have it ready to go on the main tab?
View 5 Replies View RelatedIn the past when I copy and paste a cell, the new cell contains the formula from the old cell, with relationships between other cells maintained.
Recently my excel stopped copying formulas and copies only cell values from the old cell. It now acts as it used to act if I did a Copy-Paste Special -Values operation.
If I do select Paste Special, it grays out the formulas option and only lets me cut and paste values.
I have two subroutines. One subroutine updates some figures on spreadsheet 1, and then calls a subroutine that updates some figures on spreadsheet 2. Spreadsheet 2 is supposed to save and close, and return to spreadsheet 1, but it only gets as far as opening spreadsheet 2 and updating the figures. These are the two subroutines:
Code:
Sub UpdateLegalAndMeans()
Dim wBook As Workbook
Dim count As Integer ' counter for counting down backlog figures
[Code]....
I am trying to extract data all countries excluding Spain and Portugal and also exclude data for certain product classes (for example excluding Products X, Y and Z)
I have written the formula below which picks up data for certain criteria I have concatenated in tab "raw data" in column A:
=SUMPRODUCT(SUMIF('Raw Data'!$A:$A,B7&D3,'Raw Data'!$W:$W)=('Raw Data'!I:I"Spain")*('Raw Data'!I:I"Portugal"))
Notes: Cells B7 and D3 are used match criteria in Raw Data A:A
Column W is the data that I need to extract i.e the numbers
Raw data I:I is a list of all countries
However this does not exclude Products X, Y and Z, only excludes countries Spain and Portugal.
I wondered if there was an easier way to create a formula?
How do you add commands to a right mouse click?
I click on a cell, do a right mouse click and would like to be able to select a command that puts the current date and/or time in the selected cell.
Is it possible to have multiple VBA commands on a single line with a separator ( ; or , ) of some kind?
I am trying to keep my code as short as possible (from a scrolling perspective) and would like to have something like the following:
Dim MyArray(1 to 20, 1 to 3)
MyArray(1,1) = "Sheet1" ; MyArray(1,2) = "D8" ; MyArray(1,3) = "D9"
MyArray(2,1) = "Sheet1" ; MyArray(2,2) = "C49" ; MyArray(2,3) = "w3"
MyArray(3,1) = "Sheet1" ; MyArray(3,2) = "A23" ; MyArray(3,3) = "AB12"
MyArray(4,1) = "Sheet1" ; MyArray(4,2) = "Z19" ; MyArray(4,3) = "W12"
MyArray(5,1) = "Sheet1" ; MyArray(5,2) = "Q32" ; MyArray(5,3) = "Q23"
I would like to make an IF-formula in excel. The problem is that i would like to to it for 12 different commands. i.e "IF(A1=14;243;"") but for 12 commands I saw that the in-built IF function in excel only manage 7 commands, is this true?
And how do I write the formula,
I've created userforms, but don't know the commands to make them run.
View 4 Replies View Related