Using IF Commands To Create Status Column For Project Statuses?
May 21, 2014
I'm having difficulties setting up a spread sheet which needs to be reported to my management weekly.
We follow a 6-stage buy off procedure and within a spreadsheet I have smiley face icons in each of the stages as they're reached.
For example, someone could be at the second stage of the buy off and all going according to schedule, I've got 3 emoticons through Wingdings text - J, K & L (paste in windings to see icons if you wish). There are several projects with this 6 stage buy off procedure and as lots of people access this shared document to edit weekly there are lots of different statuses.
I'm wanting to have a custom view with all the 6 stages hidden and then a separate column with "Status" as the header - this cell needs to display the most up-to-date emoticon of each project but using a normal IF/OR combined command only gets me to control 1 column. I have attached a spread sheet example with most content removed due to beingTEST IF COMMAND.xls strictly confidential material.
View 3 Replies
ADVERTISEMENT
Nov 24, 2013
Uses excel 2010 to track the project in the form of project status report. Basically it has to look simple yet tracking should be in the form of progress bar etc.. Our aim is to track a 3month project.
View 1 Replies
View Related
Aug 28, 2007
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
View 4 Replies
View Related
May 12, 2014
I have some vba code that currently copies a status table out of a worksheet, into a temp file for an email. The status table in excel has a column D which can have different status. I want to select everything in the table except rows that are 'Descoped'. Currently my code looks like:
Set rng2 = Sheets("Execution Status").Range("B2:F420")
This picks up everything without looking at the status. I want to change it so it doesn't pick up the rows where Column D contains 'Descoped'.
In pseudo code I would describe it as:
Set rng2 = Sheets("Execution Status").Range("B2:F420") where value in D5:D420 is not equal to 'Descoped'.
I have tried:
Set rng2 = Sheets("Execution Status").Range("B2:F420").Value "Descoped" and nothing gets selected.
View 1 Replies
View Related
Feb 13, 2014
The code I have below is relevant to this spreadsheet Copy of Invoice test.xlsm
In the code below I marked the script that has issues in red. The code takes the last row and the last column and works from there. This works fine with single commands, however, when I take a cell (based off the last row and the last column) and try to give that cell multiple commands excel sends back an error. When I use this multiple command (see part of code that is marked in red) for a range that is not based off the last column it works fine.
[Code] .....
View 9 Replies
View Related
Aug 4, 2014
I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
View 5 Replies
View Related
Sep 5, 2013
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description
...
1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
View 4 Replies
View Related
Jul 30, 2013
I am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END
[code].....
View 9 Replies
View Related
Apr 27, 2007
I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.
View 9 Replies
View Related
Sep 29, 2011
I am putting together a worksheet with all my teams tasks. I have columns A:U and Status is column "M". What i want to do is when ever i select Complete from the list in Column "M" i want to the row to turn Green.
I tried the following:
Selected the Range
From Conditional Formatting menu selected Manage rules
Selected new rule then Use formula to determine which cells to format
formula i used =$M$2=complete and selected green from the format menu
BUT nothing is happening. What am I doing wrong.
BTW the Values in Column are Complete,Development, Not Started Research, QA/UAT. If I can conditionally format based on these selections that would be even better.
View 3 Replies
View Related
Apr 2, 2007
I wish to count the number of entries in a workbook based on the entries in two columns. The columns are for example H1:H100 ( date) and L1:L100 (status). I want to the number of times the date in H1:H100 is over 30 days old, as well as L1:L100 = 'Started' for example.
View 2 Replies
View Related
Apr 16, 2014
I've a table of historical members status list. with this table, I could track each member status history, since the beginning they become a member.
Here is the short of table
Name
Member Status
Date
Adel
New Member
1-Jan-14
[Code] ....
I expect to use Pivot table to show how many people which are still "New Member" and/or "Junior Member" up to now. From the table above, there should be: only one person who still as New Member. because it is only Smith, and only two people with Junior Member. they are Adel and Jhon.
I've search around and found the following useful link, viewing only the last date in a pivot table for each user
also A quick way to return the latest date in a subset in Excel
How to know the last status of each user (each member in my case).
View 3 Replies
View Related
Mar 5, 2014
I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).
What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.
Picture:
Column C Column D
111111 M
111111 M
111111 M
[Code]....
Currently calculates: M=9, F=2
Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.
Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))
View 9 Replies
View Related
Mar 21, 2014
This file contains 2 sheet Main M and Detail D.
In Sheet Detail D, When I choose Code, month of Project, it will be shown the result.
By the way, tell me how to delete the Auxiliary column in Main sheet.
View 3 Replies
View Related
Nov 21, 2007
Im trying to construct a nested Countif statement. I need to count the number of instances that "Project" appears in Column O AND "TS" in Column N. The range is in another in Sheet2. and the summary in Sheet 1 where I want to have the Countif(AND...??? statement Example Counif(Sheet 1 Column 0 contains "Project" AND if Column N Contains "TS"
View 2 Replies
View Related
Jul 24, 2009
I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
View 4 Replies
View Related
May 12, 2012
How 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
View 8 Replies
View Related
Jan 30, 2008
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 Related
Dec 4, 2009
I 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.
View 7 Replies
View Related
Dec 17, 2008
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 Related
Feb 24, 2006
Is 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?
View 14 Replies
View Related
Jan 9, 2014
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.
View 3 Replies
View Related
Oct 29, 2008
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.
View 9 Replies
View Related
Jan 31, 2009
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.
View 9 Replies
View Related
May 2, 2009
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........
View 9 Replies
View Related
Jul 24, 2009
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
View 9 Replies
View Related
Jun 1, 2007
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
View 2 Replies
View Related
May 13, 2014
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?
View 1 Replies
View Related
Mar 30, 2014
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‎
View 10 Replies
View Related
Jun 23, 2012
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‎
View 14 Replies
View Related
