Earned Value Report in Project 2007
I'm using MS Project 2007, have input all my tasks, costs, set baseline so
all my numbers are populating great.
I'm using the visual report to generate the earned value over time report
(the pivot table and chart). Unfortunately while I am really good at most
things Excel, pivot tables is not one of them. Project is populating the
table with Quarters 1, 2 and 3. I only need Quarter 1 for my report. Is there
a way to hide or delete the other two quarters from the table?
Thanks for the help!
I am guessing that you have your "Time Weekly Calendar&q...Can I exclude the criteria Value from the query results?
I am using the [Dupe_Joiner_Title_ID] to match a list of records from a
duplicates table against an ID form field, but want to exclude the actual
value that the query uses as the criteria from being seen in the continous
form that is linked to the query, but shows all other matching records for
the form's current record.
This is the criteria code:
that is in the 'Dupe_Joiner_Title_ID: Joiner_Title_ID' column.
This is my SQL:
Tbl_Stree...Using SUMIFS with date range
I am trying to sum a column of cells if several criteria are met...1. equal
to specific customer name, 2. is greater than or equal to a beginning date
entered into a specific cell and 3. is less than or equal to an end date
entered into another specific cell. I can get the formula to work if I use
the beginning and end dates in the SUMIFS formula, but I don't want to change
the formula each time I run the spread. So I want to be able to data enter
the begin and end dates in certain cells (line in B1 and B2) so I only change
those dates to recalculate the totals.
Somethin...Changing line appearance for future values
Using Excel 2007. Is it possible to change the appearance of a line in a
chart for values in the future (like projections)? I would like to go from
solid to dashed and keep the same color and weight.
Either you format line pieces individually (manual work).
Either you use 2 series to plot in the chart. Use IF functions to see on
which series a given value should be shown. Use NA() for values that should
not be plotted.
http://www.wimgielis.be = Excel/VBA, soccer and music
> Using Excel 2007. Is it possible to change the appearance of a line...Conditional formating based on another cell value
Operating System: Mac OS X 10.5 (Leopard)
I've managed to use the solution described below, but I haven't been able to figure out how to use this for formating a cell based on a date entered into another cell. <br><br>Example: When 1/15/10 is entered into AG4, P4 is highlighted green. <br><br>Any help would be appreciated. <br>
"On 6/30/08 7:54 PM, in article email@example.comR9absDaxw, <br>
"Simon@officeformac.com" wrote:http://www.officeformac.com/macimages/btn_publish.gif &l...date format 01-02-10
How can I set a date format for any cell and user has to use that format
otherwise user is not allowed to enter any date.
you can only do it with code.
The following is some event code which would ensure that only dates can be
entered in column A, strating from row 2.
Adjust to suit your nededs.
also, date format has been set as dd mmm yyyy e.g. 02 Jan 2010, again change
to suit the format you want.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim edate As Date
If Target.Row < 2 Then Exit Sub ' change row number from 2 if required
If Target.Co...Function to format a value into percent with no decimal
I have about 50 rows of data, each with 2 columns. The first column
(A) holds a number; the format is General. The second column (B)
divides the number in A over 51, using this formula =A1/51 (and
=A2/51, =A3/51, etc.). The format for column B is Percentage with 0
decimal places, so the results look like 25%, 4%, etc.
I'd like to create a third column (C) which concatenates the results
of columns A & B to yield something like:
But instead I get:
The function I'm using is:
=B1 & " (" & C1 & ")"
I'm thinking I...Date "yyyymm"
I have a worksheet were the dates are displayed as 200901 (yyyymm --- no
slash) in column A.
I need to add a formula to cells in colum C that will only substract any
given number of months from this date, such as 200901 minus 13 months will
equal 200811. Now, on the same boat, I would like to show the new date
displayed as 11/01/2008.
On Tue, 15 Dec 2009 06:31:02 -0800, Memphis <firstname.lastname@example.org>
>I have a worksheet were the dates are displayed as 200901 (yyyymm --- no
>slash) in column A.
>I need to add a formula to cells in c...Pay Period From/To dates on Payroll check voids
When a payroll transaction is entered or a build is processed, pay period
from and to dates are entered. These dates are stored as 'trxbegdt' and'
trxenddt' on UPR30300. However, when a check is voided, the 'trxbegdt' and
trxenddt' on the UPR30300 table holds the actual check void date and not the
original pay period from and to dates. SmartList Builder, Crystal Reports
and Excel data query tools can be used to pull data based on the pay period
date ranges for performance based reporting, but voids cause inconsistencies
in the analysis since the dates are ...DIsplaying negative time values
I have two columns of time values. I need to determine the difference
between the two values and return either a positive or negative time
value. The table below is in hours and minutes
Time A Time B
In the above example First row should be "00:00:00", Second row
should be "00:03:00", and Third row should be "-00:02:00"
When subtracting Time B from Time A for the first two rows i get the
correct value, but the third row returns as "##########...Styles and Set Numbering Value problem
Running MS Office Pro 2007 with Windows XP Pro.
I have styles defined for numbered lists. These styles have simple
characteristics, like indent, space before, etc. - nothing special.
I want the numbered lists to appear throughout the document, with each
starting at the number 1. When I try to start a new list using the Set
Numbering Value dialog, I get a message saying "Changing the number format
for this list updates the style "NumList" and reapplies the style to each
paragraph. Do you want to continue?" If I select no, the starting number is
not chan...Date query 01-21-10
I have a table which contains a field called "DUEDATE", data type is
DATE/TIME, formatted as LONGDATE. I use it to enter in dates when projects
How can I write a query which returns "DUEDATE" items for the current date.
Example: Today is January 21, 2010 and I want to retrieve everything from
the table with "DUEDATE" of january 21,2010.
If you only store the date part and only want to return records where the
DueDate is the current date, set the criteria under DueDate to:
MS Access MVP
"Iago" <Iago@d...Returning last number(time) in a row of cells
Need some help with this one please;
Row A4 thru J4
A4 B4 C4 D4 E4 F4 G4 H4 I4 J4
12:00 15:00 16:00
I need to return to cell K4 the last number in the above row.
I do not know what formula to use. Any help would be greatly appreciated.
There are several ways to achieve that, - here are two:
> Need some help with this one please;
> Row A4 thru J4
> A4 B4 C4 D4 E4 F4 G4 H4 I4 J4
> 12:00 15:00 16:00
> ...How to suppress zero value check checks and remittance
Any one knows how to avoid printing the zero value remitances during the
checks run in PM. GP7.5
Thanks in Advance.
...vlookup help #2
i am providing you with my problem with attached file.
i will be very grateful to you if you can solve my probem
Attachment filename: help me.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46482
Message posted from http://www.ExcelForum.com
first: you should try to explain your problem in plain text. Most
people won't open attachments :-)
To your problems: Just enter the 4th parameter of the VLOOKUP function
to look for exact matches. So change
=VLOOKUP(A17,A25:C2...Trying to select a specific range based on the time value of user form input
Here is the cmdOK_Click event of my user form. What I need it to do is
based on the time input in the pckApptTime on my form to have it offset
to a different range of cells (ie: if time is 7:15, then the active
cell offset is range A1, looping for a blank cell. If the time is 8:30
am then the range begins at a10, 9:45 range a20, 1:15 range a30, 2:30
range a40, and 3:45 range a50)
Where in the Sub do I put this information? and what do I put to have
the OK_Click do this automatically?
Private Sub cmdOK_Click()
ActiveWorkbook.Save (this is to update the ...Task with overdue dates print in smaller fonts?
In Outlook 2003, if I have a task with an overdue due date, it appears on
screen just like
all the other Tasks ... but when I print out my Tasks, it shows up in a
If I delete the due date, it prints in the same size font as everything
else. Where do I set my preference for all tasks to be printed the same
regardless of whether they have due dates or not?
...Return cell characters after space
I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'.
As there are many other examples, I need a function that will seek out the
last ' ' in the cell content and then return all characters to the right
hand-side of this ' '.
e.g. 'Mr K Peters' would return 'Peters'
Please let me know if I can clarify!
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
Jacob (MVP - Excel)
> I have a cell with 'Mr J. Smith' and want to return in an...Maximum value
I have a table in the following format:
ID Diagnosis Fraction
1 X48 0.01
1 Y52 0.5
2 U78 0.85
What I need is only those records that have the highest fraction for each ID.
ID Diagnosis Fraction
1 Y52 0.5
2 U78 0.85
I have used the Max function in my query. However, that does not work
because the information is also grouped by diagnosis as well as ID and this
causes all of the lines to appear. Does anyone know how to get around this
problem? I can't just bring through Fraction ...Transferring option group value
I'm trying to create a form for adding new record only.
How to do that ?. In that from,I want to put an option
button ( group ) to shown or hide some text box ( control )
in my forms.But I also want to transferr the option group value
to a field once the form close or the Save_cmd is clicked.
Then in another qury I need to join the value transferred
from the option group with another table so that the data
appear as a text in another table.
Please advice how to do that since I have no idea
>I'm trying to create a form for adding new record only.
>...Copy formulas, format, validation to sheet with values
Environment: Excel 2000, Win 2000
I have a workbook with 12 sheets, one for every month of a year, all
the same size, same format, just different values.
I am still making changes to the workbook application, e.g. formulas,
formats, validation, conditional formatting.
So I change the sheet for one month, and then have to get the changes
duplicated for all the other 11 sheets in the workbook.
What is the best way to do that, especially for the formulas?
Paste Special with deleting the constants afterwards is not a solution
here, as some of the target sheets already have values entered manually...FREQUENCY and dates
I'm doing some performance analysis and have a large number of
timestamp (formatted as dd/mm/yyyy hh:mm:ss) / transaction duration
pairs in a spreadsheet. I'm trying to create a histogram showing
number of transactions each hour, but when I use the FREQUENCY function
I get very small numbers associated with each timestamp "bin".
Am I missing something fundamental about the way Excel handles dates?
After that I need a similar plot of average duration each hour or 30
minutes. I don't see any way to force FREQUENCY to do that.
Related to this, I tried selecting the...Why is date repeated in brackets in Outlook 2007 Calendar?
in day, week, month, views
"Buddie" <Buddie@discussions.microsoft.com> wrote in message
> in day, week, month, views
Do you have alternate calendars enabled? Tools>Options>Calendar Options is
where you find that setting. Otherwise, check the Date format(s) in the
Windows Time/Date settings.
Brian Tillman [MVP-Outlook]
Do you have a second calendar index enabled? look in tools, options,
Diane Poremsky [MVP - Outlook]
Outlook Tips: http://www.outlook-tips.ne...sum a cell value to a range
I need to sum value in A4 to a range B4:B12 every time I enter a new
value in A4.
How can I do it?
Thanks in advance
"Osmario.Avila@gmail.com" <email@example.com> wrote:
> I need to sum value in A4 to a range B4:B12 every time I
> enter a new value in A4. How can I do it?
> "Osmario.Avila@gmail.com" <firstname.lastname@example.org> wrote:
>> I need to sum value in A4 to a range B4:B12 every time I
>> enter a new value in A4. How can I do it?
Or did you mean that you want to a...Open file with variable date name based on current date
I have a folder containing different .xls report files with the name format
"YYYY MM text.xls", where YYYY stands for year, MM for month.
I need a macro which, when run from an excel file saved in a different
loacation, based on the current date, e.g. January 7, 2009, will open the
file "2009 01 text.xls".
Thanks for your help,
Workbooks.Open "C:\Foldername\" & Format(Now(),"yyyy mm") & " text.xls"
Except it will open 2010 01 text.xls ;-)
MS Excel MVP