Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Tracking Action Items

I am using excel to track action items. The form has a due date (G11:G186) and Date completed field (H11:G186). My first thought was to compare the two dates to categorize On Time and Past due. I used the formula below.

=IF(ISBLANK(H12),"Open",IF(G12>=H12,"On Time",IF(G12<H12,"Past Due")))

This works fine except since this is used as a general template I have a lot of lines for action items but most meetings will only have 10-20. The problem is on lines there are no action items "Open" is still displayed. I understand why but not how to fix it

So then I thought a formula to check if both the Due Date and Date Completed cell are blank nothing would display

=IF(OR(ISBLANK(G34),ISBLANK(H35)),"")

So how do I combind these two formulas to say if the fields are blank display nothing but if On Time or Past Due if applicable.
I tried this with no luck

=IF(OR(ISBLANK(G34),ISBLANK(H35)),""thenIF(ISBLANK(H12),
"Open",IF(G12>=H12,"On Time",IF(G12<H12,"Past Due")))


Post your answer or comment

comments powered by Disqus
Hi all,

At weekly meetings, a secretary of ours takes notes of what action
items are assigned to whom. She keeps track of these delegations on a
single worksheet per project at the meeting real-time, for the sake of
speed and accuracy. This workbook has one worksheet per project with
groups of two rows representing a task within that project being
assigned to some employee.

For instance, within ProjXYZ Kevin may have two "action items" he
needs to work on. However, in ProjPQR, another worksheet in the same
workbook, Kevin may have a third "action item." I would like to write
a macro that consolidates all action items for each person on to one
tab. This eliminates the need to attach a large workbook and scan
through various worksheets looking for one's name assigned to tasks.
One tab for Dan, one tab for Ted, one tab for Susan, etc.

Ideally, the macro takes entries in this form (the project
worksheets):

WKSHT: Othello
__________________________________________________ ____
| Dan | Issue: Resolve DSP errors on Othello3.4
Chipset |
-----------------------------------------------------------------------------------------------
| 2/27/2007 | Resolution: [HE FILLS THIS IN HIMSELF
LATER |
-----------------------------------------------------------------------------------------------

And will put it onto another worksheet like this:

WKSHT: Dan
__________________________________________________ _________
| *Project* | *Date* | *Issue* |
*Resolution* |
--------------------------------------------------------------------------------------------------------
| Othello | 2/27/2007 | Resolve... | [NOW I FILL THIS IS AS I
WORK] |
--------------------------------------------------------------------------------------------------------

I am completely aware that this may be more trouble than it's worth.
Frankly, in my opinion, it is, but since I'm so new to VBA and Excel
programming in general, I think it's a good project for me to take on
just to learn the ropes. Unfortunately, while I have extended
experience with PHP and Python, the specificity of VBA is daunting,
and I'm afraid I don't know exactly how to best approach this problem.

Any guidance would be greatly appreciated.

Thanks

I'm trying to move completed action items from one worksheet to another via a
command button on the excel page. The macro I have is...

Sub MoveCompletedTasks()

ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select
Sheets("Closed Action Items").Select
ActiveCell.Rows("1:1").EntireRow.Select
Rows("4:4").Select
Sheets("Action Items").Select
Selection.Cut
Sheets("Closed Action Items").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Action Items").Select
ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select
Selection.EntireRow.Delete

End Sub

I get "Error 400" after
Range("A1").End(xlDown).Offset(1, 0).Select

Any suggestions to move a completed task to another page is appreciated.
I'd like to avoid filtering...I'm trying to make it as 'manual' free as
possible.

Thanks,

I've got action items from various meetings each in their own 'meeting' tab.
The problem is, when a new meeting tab is created, or an old one is updated
with a new action, the person assigned to the actions won't know about it
unless they hunt for it (and most times they arent aware of it until it is
too late!)

Is there anyway to create something where each persons' actions (from the
entire workbook) can be sorted out and that would automatically update when a
new entry was made with their particular name assigned to the task?

I am using Office 2003 by the way...

Would it be possible to design a worksheet to build a list of 'action items' that would be stored as a locked cell? For example, with a series of dropdown lists (A1-JobNo., B1-Assignee, C1-Time to Complete):

=A1&" - "&B1&" - "&C1&" Day(s)" outputs to "555 - JohnDoe - 10 Day(s)"

What I would like is to have the 3 (or more) input fields with an "Add to List" function/button which adds the output to a separate "list field" elsewhere in sheet/workbook and then clear the original entry fields for more entry.

After that I plan to play around with representing those individual items visually somehow, but one thing at a time.

Thanks for any and all help.

Cheers.

I use an excel workbook with multiple worksheets to track action items across
several teams. I was graciously provided an event macro from someone in this
discussion group to track changes to due dates. A copy of the current macro
follows.

This macro looks for a change in Column J (which is the due date) and if
detected, it records the user name, date/time, and the entry into a tracking
worksheet (named DUEDATE-CONT COMPLIANCE). It also brings over the data in
another column (Column A – which is the action number).

This is working well and I am finding it would be useful to record
additional data in the tracking worksheet (such as REASON FOR DUE DATE CHANGE
– Column H), when a due date change is made.

So I have two questions.

1. Can someone kindly advise me how to record additional columns of data
into the tracking worksheet?

2. Is it possible to record the name of the ‘tracked’ worksheet with this
info, so I can have only one tracking worksheet for the entire workbook?
Currently, I track each worksheet by individual tracking sheets.

The macro is as follows.

Private Sub Worksheet_Change(ByVal Target As Range)
'Column to be watched
Const sWatch As String = "J"
'Column of reference data that will show on Track sheet
Const sRef As String = "A"

Dim rWatch As Range
Dim rCell As Range
Dim sUser As String
Dim lOffset As Long

Set rWatch = Intersect(Target, Columns(sWatch))
If rWatch Is Nothing Then Exit Sub
sUser = Environ("username")
lOffset = Columns(sRef).Column - Columns(sWatch).Column
With Worksheets("DUEDATE-CONT COMPLIANCE")
ActiveSheet.Unprotect
For Each rCell In rWatch
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = rCell.Offset(0, lOffset)
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = sUser
.Offset(0, 3).Value = rCell.Value
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingColumns:=True,
AllowDeletingRows:= _
True, AllowSorting:=True, AllowFiltering:=True
End With
Next rCell
End With

End Sub

Hi,
I need help on the following.
I need to write a macro to send out 2 types of email via excel 2000 for those (DEPENDING ON their Action_Item_Status
1)action items with Action_Item_Status='Over Due', (over due email)
1)action items with Action_Item_Status='Due Soon', (reminder email)

My datasheet contains the following columns:-
Action_Item_No
Action_Party_Email
Action_Item_Status
Action_Due_Date

The 'Action_Party_Email' can be duplicated.(Ie. the same email address can be found in different 'Action_Item_No' records.

In each email, i need to also include the data found in 'Action_Item_No' & "Action_Due_Date" columns.

Please kindly help.Thanks alot in advanced

Back to top

Dave, the Calendar Control that you put in the December newsletter is just great. I am currently building a spreadsheet to track Action items and project issues, etc. Three of the columns are dates and thus have date formats imposed on them. I would like to know if there is someway that the calendar can be made to appear when a user clicks on a blank cell that has a data format imposed on it.

Any insight you can provide is greatly appreciated. Thanks.

Ron M

Hi

I have an Excel sheet that tracks Action Items
I wrote a formula that each time an Action Item is Over Due the status changes to "Over Due"

I need a macro that will recognize that automatically and will copy the specific row and send an email to the person responsible for this Action Item with a message "Your Action Item is Over Due….."

You can find the Excel sheet in ftp://dannyn:galit123@194.90.236.210/

Can you please help?

Thanks

I have a 2007 spreadsheet to track action items and want to embed ".msg"
files that are emails recording the satisfactory closure of the item in my
"CLOSED" column. I go to Insert>Object; select "Create From File" tab;
browse to my file; select "Display as Icon" checkbox; click on "OK"; and
Excel locks up. I do not want to link the files, I want to embed them
because I need to send the spreadsheet to people without access to our
network.

The Excel Help has proven useless - when I type in "Insert Object" I get
everything but help on how to insert an object. thanks for any ideas on what
I may be doing wrong. Perhaps some setting in options is set wrong?

Hi community,
I want to track action items: closed, open, overdue, and postponed
activities. For this I defined a tracking sheet holding all actin
items - defined as dynamic range and an associated pivot table with
the structure showed in http://www.michaeltarnowski.de/temp/pivot.jpg

This pivot table is fed by the data of the dynamic range and refreshes
automaticaly when the sheet is activated and charted. The problem with
that this table shows for each week the sum of closed, open, overdue,
and postponed items only. But, in real life all open issues of the
last week which are not closed in the meantime remain open in the
ongoing week, i.e. the open issues of the ongoing week are the sum of
all open items of this week plus the sum of open issues of the
foregoing week. This is valid for overdues and postponeds as well, but
not for closed items.

How can rearrange this table or define a new pivot table showing the
behaviour intended? - Do I need a pivot table of a pivot table?
Thanks for your assistance, have a nice day
Cheers Michael

Easy description:

Select cell D3
type Ctrl-B (to make it bold) and some text then enter
type some text in cell D4 then enter
type Ctrl-B and some text (now cell D5) then enter
type some text (now cell D6) then enter
type some text (now cell D7) then enter

The text in cell D7 is now bold. Excel noticed a pattern and 'auto-bolds' the text in D7. I don't want it to do this. Interestingly, if you were to start in cell A1 and follow a similar process as above, the auto-bold does not occur and A5 stays non-bold. Why the discrpenecy?

Regardless, I want Excel to stop the auto-bold. Unfortunately, when you type in the text, the cell is not identified as bold. The bold is added once you hit enter, so it is not like I can make the cell unbold and then add the text, because the cell is already not bold as you are adding the text. Looking through the auto-correct and auto-format options, I don't see anything relevant.

In depth description:
I am writing some macros which track action items. The first macro creates a header row to track the open date, owner, and description with all the cells in that row set to bold; it then sets the next row to an outline level of 2 (groups, with 'summary rows below details' unchecked for its settings) to track comments and the comment date (with this row not being in bold). Thus a header row and a comment row are created in one macro.

Another macro will insert another comment row within the current action item. It is smart enough to always add a comment row at the end of the current action item comment list. This row is not bold.

This is an example:
Action item 1 row (in bold)
Comment row 1 of action item 1
Comment row 2 of action item 1
Comment row 3 of action item 1
Action item 2 row (in bold)
Comment row 1 of action item 2
Action item 3 row (in bold)
Comment row 1 of action item 3

When I run my 'add comment' macro for Action item 3, the comment row is added, but when I type in the text, it is bold (because Excel seems to have picked up on the bold, not bold, bold, not bold, pattern of the rows above it). Oddly, and this may be a different issue, only some of the cells in that comment row get auto-bolded, some don't. This hints that there is something I can control, but I can't find it.

What I want is that the comment rows are always not bold (unless the user wants them to be). This auto formatting is ruining my list formatting. Any ideas?

Paul

Hi All,

How to track the value of last item in CountIf and work backwards? I am trying to do inventory valuation at the end of the month. For example I have 7 purchases in a month for 1000 units in each purchase order, my on hand inventory is 1500 units. from the purchase details I did a countif to tell me there are 7 PO's in a month for the item, but if i want to find how much each units costed starting from last PO in the CountIf is there a way to pull that number? I have total purchase amount by PO & quantity by PO also in separate columns, so I know cost per unit in each PO but can't figure out how to go backwards (starting from last in)

Thanks
SD

Hi,

Currently I have 2 workbook which are Tracker.xlsm and SystemDatabase.xls. If I want to select the item and click the submit button, how to make the cell highlighted in Tracker.xlsm file ?

SystemDatabase.xls, sheets ("Summary")
Location Items
B2 : MA ... C2 : ABC
B3 : MB ... C3 : BCD
B4 : MC ... C4 : ABC
B5 : MD ... C5 : XYZ
B6 : ME ... C6 : XYZ

Tracker.xlsm, sheets ("Track")
LOCATION ..... ITEMS ..... ITEMS
C6 : MA ..... C7 : ABC ..... D8 : BCD
F6 : MB ..... F7 : BCD ..... F8 : ABC
I6 : MC ..... I7 : ABC ..... I8 : BCD
M6 : MD ..... M7 : XYZ ..... M8 : BCD
R6 : ME ..... R7 : XYZ ..... R8 : BCD

In Tracker.xlsm, it was empty and did not has any value(ABC,BCD,XYZ) inside the row or column, its only show the location MA - MF.
Lets say i click a button and multiple select item ABC & XYZ, the cells in tracker.xlsm will highlight the same color for ABC and same color for XYZ.

In this case, how can I do for the coding or Do you have any example can let me have a look?

Hi,

I need help in building macro/s to perform the following:

I use a typical work book with some 10 odd worksheets containing details of different tasks / action items pertaining to multiple projects with time deadlines etc., for tracking. this is the workbook I share with the Managers of different groups who will directly populate / update the status of different items in the worksheet specific to their operations.

For clarity's sake let me call the sheet onto which we copy the data from multiple sheets as "SUMMARY-SHEET" Also the data contained in multiple sheets in the workbook is essentially relating to different tasks, action items and issues which typically have a "WEEK" or Date column referring to the timeline and a "STATE" column with HIGH/MEDIUM/LOW (OR) OPEN/CLOSED as the current "STATE" of each item.

My need warrants that we start off with copying all the issues and tasks contained in multiple sheets onto "SUMMARY-SHEET" and thereon everytime the macro / function is run.. the code needs to compare the content of each sheet with the "SUMMARY-SHEET" and copy the UNIQUE rows onto the "SUMMARY-SHEET" rather incrementally.

My Excel coding (macro building) skills are very poor and request the help of this forum in being able to use the real potential of Excel.. Please don't delete my posting.. Guide me..

I will be thankful for help in this regard..

Cheers
Venkat

First off... thanks so much for looking into this issue!! Here's my problem....

I have a spreadsheet wherein I have a contact name in column b, one status type in column c, another status type in column d, and I want an action in column e to be automatically populated based upon the values of the contents in columns c and d.

Here's the possible status and their associated action item:

(Col C | Col D | Col E)

Yes | Yes | No further follow-up required
Unknown | Yes | Contact to confirm
Unknown | Unknown | Still need to track down
Unknown | No | Contact for updates
No | No | No further follow-up required
No | Yes | No further follow-up required

So how would I go about setting this up? Thanks so much for all your inputs!!! I really appreciated it!!

NEED HELP ON RETURNING THE TEXT VALUES BASED ON CRITERIA.

BELOW I MARKED THE VALUES IN TWO COLUMNS 'ACTION ITEM' AND 'STATUS'.

NOW I WANT TO RETURN ALL THE 'ACTION ITEM' WITH THE STATUS 'PENDING' TO OTHER SHEET OR CELL, THIS QUERY WILL ME TO TRACK THE ACTION ITEMS DAY TO DAY.

ACTION ITEMS STATUS
------------------- --------------------
MEETING - PENDING
REPORT - DONE
METRICS - PENDING

I have a worksheet that contains lists of "action items", that is used to
keep track of jobs that need to be done. I want to be able to automatically
move the contents of a row of cells to another worksheet once the action item
has been completed. In other words, once I have changed the value in a cell
to "yes" (the action has been completed), the information is archived in a
separate worksheet.

Is this possible, and how?

I'm using an activeX container to embed an excel document into my app. It
has several menu items I need to use disabled though. Since I don't want to
try and figure out how the ~400k of c++ source works to fix the problem I'm
trying to do it in vba. The problem is with the change tracking menu item
group. I can enable the main item on the tools menu, but not the subitems
that popup when it's selected. I can't figure out how to access the subitems
to enable them. The code below includes how I've enabled the track changes
item as well as 3 unsucessful attempts to enable the Highlight CHanges
subitem.

Sub EnableTracking()
'This works but doesn't affect it's children
Application.CommandBars("Tools").Controls("Track Changes").Enabled = True
'this generates an runtime error
Application.CommandBars("Tools").Controls("Highlig ht
Changes...").Enabled = True
'As does this.
Application.CommandBars("Track Changes").Controls("Highlight
Changes...").Enabled = True
'This doesn't generate an error but doesn't work either.
Application.CommandBars("Tools").Controls("Track
Changes").Controls("Highlight Changes...").Enabled = True
End Sub

activeX container location
http://support.microsoft.com/default...b;en-us;311765

I have a spread sheet I am using to combine two reports from a DB. One is an
Incident tracking and the other is Action Item tracking. I want to combine
all Incidents with the action item that is associated to it. I have an
action item id on both data sheets. I am new to excel programming, but have
found some helpful information here. This is what I have so far:

Private Sub CommandButton1_Click()
On Error GoTo Abort
Dim wb1 As Workbook

Dim rng1 As Range, rng2 As Range
Dim c As Range, cc As Range, ccc As Range, cccc As Range

Set wb1 = ThisWorkbook

Set rng1 = wb1.Sheets("Sheet1").Range("A2:A1000")
Set rng2 = wb1.Sheets("Data2").Range("K2:K1000")

For Each c In rng1
For Each cc In rng2
If c.Value = cc.Value And Len(c) > 1 Then
Set ccc = c
Set cccc = wb1.Sheets("Data2").Range("A" & cc.Row & ":" & "J" &
cc.Row)
cccc.Copy
ccc.Offset(1, 6).PasteSpecial xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
Next
Next
Exit Sub
Abort:
msgbox "Error: " & Err.Description & ", " & Err.Source
End Sub

This works, but I need for it to insert a blank row. Now it is inserting
the information on the next line with other incident information. It also
seems to only paste the first AI associated with an incident. Any Ideas how
I can make this work?

Hi again!

I have a spreadsheet that tracks status of "Action Items" in an IT development project.

I would like rows to be greyed out once their status is Complete for a certain row. Perhaps this can be acheived with conditional formatting?

So basically, I want to affect OTHER cells on the same row, based on the value of in a specific cells

Very grateful for tips! Illustration below:

[image]http://img806.imageshack.us/img806/9202/blahww.jpg[/image]

Sorry..... The image doesn't work.... It's here http://imageshack.us/photo/my-images/806/blahww.jpg/

Hi

In a desperate rush to get a spreadsheet out of the door with limited VBA skills

Simple problem

The worksheet tracks new action items
When a user clicks on the next empty row, the cell in the ID column gets the value of the previous ID (row above) +1

Thanks

I'm using an activeX container to embed an excel document into my app. It
has several menu items I need to use disabled though. Since I don't want to
try and figure out how the ~400k of c++ source works to fix the problem I'm
trying to do it in vba. The problem is with the change tracking menu item
group. I can enable the main item on the tools menu, but not the subitems
that popup when it's selected. I can't figure out how to access the subitems
to enable them. The code below includes how I've enabled the track changes
item as well as 3 unsucessful attempts to enable the Highlight CHanges
subitem.

Sub EnableTracking()
'This works but doesn't affect it's children
Application.CommandBars("Tools").Controls("Track Changes").Enabled = True
'this generates an runtime error
Application.CommandBars("Tools").Controls("Highlight
Changes...").Enabled = True
'As does this.
Application.CommandBars("Track Changes").Controls("Highlight
Changes...").Enabled = True
'This doesn't generate an error but doesn't work either.
Application.CommandBars("Tools").Controls("Track
Changes").Controls("Highlight Changes...").Enabled = True
End Sub

activeX container location
http://support.microsoft.com/default...b;en-us;311765

Hi,

I need help in building macro/s to perform the following:

I use a typical work book with some 10 odd worksheets containing details of different tasks / action items pertaining to multiple projects with time deadlines etc., for tracking. this is the workbook I share with the Managers of different groups who will directly populate / update the status of different items in the worksheet specific to their operations.

A typical task / issue listed herein will have different "Priority" -(High, Medium, Low), "Status" - (Open, Closed) and also Date of 'Closure' of the item.

Now what I need to do is to be able to filter out the items that meet different conditions formed by combination of Priority, status and Date and be able to update incrementally into different sheet/s.

Can any one help me with this macro build.. Can it all be contained within one macro or do we need different macros for different combination/s.

I will be thankful for help in this regard..

Cheers
Venkat

Hi - Thanks in advance. I am using Office 2007.

I am working on a spreadsheet to track completion of action items for multiple topics. Each topic has it's own worksheet with the item listed, a due date and a completion date if completed (otherwise the field is blank). I need to calculate the number of days past due from the due date, and if the completion cell is blank, move the item to the first worksheet in the book, categorize as either 30 to 60 days late, 60 to 90 days late or more than 90 days (color coded as well). I need the action item to move into different categories as the number of past due days increases. Once the completion date is filled in, I need the item to drop off the hot list and return to original worksheet.

I hope I have made sense. I have spent quite a bit of time on this but am running up against a deadline. I have searched around the forums and am thinking I may need to use VBA? Which I never have. So I would be very grateful for some assistance. I attached a copy of the workbook I am working on - tab 1 is the Hot List and tab 2 has an action item list as a sample. Thanks!


No luck finding an answer? You could always try Google.