Free Microsoft Excel 2013 Quick Reference

Progress tracking sheet


How can I possibly ensure that in cells K6:K25, it displays "delayed" for activities that start late and "on time" for activities that started on the time as planned and end within the the planned duration limits.
I'd like to design it in such a way that Monday is the first working day and Friday the last.

The file is attached for your assistance.

Post your answer or comment

comments powered by Disqus

Ive attached a workbook with an example and a code I created to:
- Filter/Delete.Rows according to criteria (Delete if date is not NOW())
- Copy Selection and Insert to History Tracking Sheet

But, I am having a hard time pasting/inserting data selected from 1 single row or none.

Anyone help?

Heres the code just in case you dont wanna open the wbook.

Sub Module1() 
    Dim myCount As Integer 
     'Array to Sheet1 and Filter/Delete Criteria
    Application.ScreenUpdating = False 
    myCount = Range("A1").Value 
    Selection.AutoFill Destination:=Range("C10:R11"), Type:=xlFillDefault 
    Selection.AutoFill Destination:=Range("C13:R" & myCount), Type:=xlFillDefault 
    Range("C13:R" & myCount).Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     'Delete Criteria
    Do While myCount > 12 
        If Range("C" & myCount).Value = "Not Equity" Then Range("C" & myCount).EntireRow.Delete 
        myCount = myCount - 1 
     'For myCount = 250 To 7 Step -1
     'If Range("C" & myCount).Value = "XXX" Then Range("C" & myCount).EntireRow.Delete
    `Copy Selection Here Is where I am having a hard time.. 
    Range(Selection, Selection.End(xlToRight)).Select 
    Range(Selection, Selection.End(xlUp)).Select 
    On Error Resume Next 
    Insert To Sheet2 myhistory... 
    Selection.Insert Shift:=xlDown 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am trying to create a tracking sheet that will be used by several departments to track production of large documents. Since the activities may not happen in a strict order, the tasks are listed in a drop down list. When the user selects the activity in column A from a drop down list, I would like their user name to appear in column C and the date and time to appear in column D. I have attached a sample worksheet.

Thank you for any help possible.

Hello, I have created a daily budget tracking sheet and need some help to automate it fully. I have a column called "Category" that lists out the various categories to budget for (Mortgage, electricity, gas, etc). I have another column called "Budget" where I have listed out how much I want to budget for each category. Then I have a column called "Expense" where I list out how much I spent in a given category. Finally, there is a column called "Amount Left (Category)" where I want to be displayed how much is left for that category.

I cannot seem to figure out how to keep a running balance in the "Amount Left (Category)" column. When I make a purchase in a category I would like for this column to automatically stay updated with the amount left in that category.

Does anyone know how I could accomplish this? Any help would be greatly appreciated!

Hello all,

Hopefully I can get some help with this. I am trying to turn this call tracking sheet into an electronic one. Is is possible to have check boxes in the empty cells that will allow someone to just click them as they go along and it would create a total at the bottom to avoid countless hours of counting these sheets. Or even turn "Trans CM" into a button that you can click and it would keep track of how many times that specific button was clicked. Any and all help would be much appreciated.

Thank you,

Okay guys I need some serious help!!
I am a sales manager and have a sheet that allows me to track both monthly targets as well as monthly performance. Lets see if I can vividly paint this picture???

At the bottom of the sheet (outside of the print area) I have two tables,
B91:I105 this is the performance table. The second table is:B108:I121, this is the target table. The tables consist of 6 metrics and 12 months, they each give me a monthly total as well as a year to date total.

At the top of the sheet, (inside the print area) is a series of cells that correspond to the numbers, in the tables below, this shows my sales reps their targets vs. performance based on the month I have selected in cell B10.

My question is this. I also have another sheet completely free of this sheet that demonstrates weekly performance. (This is where I need so desperate assistance) This sheet contains the same 6 metrics and calculates and estimates what the performance will be at the end of each month.

So my question is this, how would one recommend, building a sheet that will track weekly performance for lets say 100 sales reps, that will then caculate a monthly average that will then dump into the monthly table in the original sheet that is printed and given to each sales rep?????

The printable area is basically a form with comments as well as a table at the top with all of the information in the tables below the form.

My questions are this, how would you first divide a workbook with all the different reps and different months??Remember Each tab would have the reps information for that month with 4 or 5 weeks depending on the month and a total at the bottom? With only 10 reps thats like 120 tabs?? Or would you combine that into one sheet?? If so how? Would a pivot table maybe come into play? Next I need the total from each month to go to the orginal sheet with the "printable form" this dosen't seem so daunting as its just the total from our weekly snapshot falling into the table at the bottom of the original sheet with the "printable form". So how do we organize the data in the weekly snapshot so that is dosen't get lost going to our monthly wrap up?
I would be glad to post it, but how?

Sorry if this is confusing, but I think we can work through this, I would be forever indebt to all of you guys!!!! Thank you in advance for any one may offer.

Thanks again.

Hoping I can get some direction here...

We've got a job tracking sheet, which I'm looking to reference key bits of information IF the job meets certain criteria (based on a code). So, IF (and when) any number of, say 5 codes, is input into a field, I would like the 2nd sheet to immediately copy/reference about 5 other fields from that SAME row (on the 1st sheet) and also spit out codes representing publications which we should be planning to include for that particular job.

The goal is to have a rolling/real-time inventory planning module that we can utilize in maintaining proper inventory levels. As of now, we're "guessing" and experiencing unprecendented growth, and having trouble gauging how much we should print and/or keep on hand for the next several months.

Hope that all makes sense - thanks in advance!

Hi everyone.

First of all I'll try to explain what I'm trying to do.

I have a sheet which I want to track and send an e-mail with the changes. History seemed a good idea but it's not quite what I wanted. I already figured out the e-mail part of the problem.

History seems to track changes after saving the file but it always seems to be one save off. I could macro before it to save the file twice so that's solved.

However, here comes the tricky part.

- If I change a cell from a row, I want the whole row to be copied to the history in both initial and modified state. History only manages to track a single cell, not a row.

Can I make any modifications in VBA to make it track the whole row instead of the single changed cell?

Code looks really basic:
    With ActiveWorkbook
        .HighlightChangesOptions When:=xlAllChanges
        .ListChangesOnNewSheet = True
        .HighlightChangesOnScreen = True
    End With
End Sub
So can someone elaborate the ListChangesOnNewSheet item so I can modify it to get what I want? Or better, if you could guide me to some problem specific tutorial or even the whole problem solved


I have attached an Excel sheet here. I want a formula which would highlight student’s name, comparing his last three scores, if the score goes from low to high and them back to low (Low-High-Low). The Excel basically has 3 different columns, Students, Score, and Date of Entry. The formula should also check if the student’s score has fallen in the latest date of entry. If one of the columns indicates if the student meets these specific criteria by saying Yes that would help me a great deal. Thank you in advance for your help!

Hi All,

I think my problem is pretty straight forward, I have attached my workbooks so you can take a look. This is what I am trying to do.

Open "orders.xls" and Copy everything except for header (row 1) to "tracking.xls" sheet "Input" destination A2 as values.

Fill down formulas I have from "H2:AD2" until last row with data found in Column A.

Refresh Pivot Table on sheet "Summary"


Also since I will never change the formulas, is there a way to store them in VBA and with Worksheet_change each time column A is changed to call a sub that fills down the formulas I have written in worksheet "input"? That way I dont have to worry if I by mistake delete them or alter them, and the sub can paste my formulas as values!

Hi Excel Programmers:

Described here is functionality that a small school would like added to an Excel app for tracking student grades and progress. The gradebook computes both objective, academic grades as well as more subjective, behavioral grades, and is used to track the progress of kids with severe behavioral, emotional, and learning disorders.

In short--teachers would like Excel to create new sheets patterned after “Progress Report Template” for all students marked “y” and, for a certain date range, copy the students’ comments entered in the “Period” sheets to each student’s newly made progress report sheet. Any coding help would be greatly appreciated.

The current gradebook application started 6 years ago and has ballooned into the attached. Thanks to the number of forum participants/programmers for making it work. Any additional help would be greatly appreciated.

Here are worksheets involved:

Sheet named “Students” in which last and first names of students are imported into cols A and B. On this sheet are also two Microsoft Date Time Picker controls for selecting starting and ending report dates.

7 worksheets named Period 1, Period 2,…, Period 7 (for 7 different school classes). A student may be in 1 or more period. If a student is in multiple periods, he/she is Not necessarily entered on the same row.

Worksheet named “Progress Report Template” to pattern reports after for students in a teacher’s caseload.

Here is the setup of the 7 worksheets named Period 1, Period 2,…, Period 7:

Last and first names of students are in columns B and C on every ODD row, starting on row 13.

Dates are inserted in row 12; each day’s date is in a separate column. There are other headings along this row, not just dates, but the date columns are entered consecutively.

Comments about a student’s work that period are typed in the date columns, but 1 row Below which the student’s names have been inserted (so the comments are on every EVEN row).

Here is the desired functionality:

Teachers would like to go to the sheet named “Students,” select starting and ending dates from the Microsoft Date Time Picker controls (E3 and E5), click a blank cell next to a student’s name (to select that student to report on), say, a cell in col D, and then have Excel do the following:

1. Store the last and first names of the student from cols A and B as variables.

2. Store the starting and ending dates from E3 and E5.

3. Create a copy of the worksheet named “Progress Report Template” in the workbook.

4. Rename the copy of “Progress Report Template” with the student’s first and last names.

5. In the student’s new report worksheet, input his/her last name in cell B1 and his/her first name in B2.

6. In the student’s new report worksheet, insert the starting date in B6 (or a row below any existing data).

7. Here’s the crazy part – scan Period 1 for the student’s last and first names (would be in cols B and C on the "Period" sheets). If found, do the following:

8. Copy the typed comment (if any) in the cell at the intersection of 1 row below the student’s names (cols B and C) and the column with the date heading in row 12 that matches the starting date.

9. Return to the student’s report worksheet and insert the starting date in cell B6 and the corresponding comment in cell C6 (or a row below existing data).

10. Then, go to and scan Period 2 and do the same if the student is present—copy the comment at the intersection of 1 row below the student’s names and the column with the matching date in row 12, return to the student’s report worksheet and insert the date and comment [a row below the previously used row].

11. Repeat this for each Period’s sheet.

12. AND—for a range of dates (from the Microsoft Date and Time Pickers in the Student’s sheet)—repeat this for the next consecutive date—scanning the Period worksheets, copying the comments for the students on those days, then copying and listing them on the student’s report sheet.

13. Lastly, set the newly inserted dates and corresponding comments as the new print range (everything above row 5 will be the page’s heading).

Don’t want very much do I? I realize this is very complex. Again, any help on this would be super appreciated. Thanks.

I am using the following code, based on help received in this forum. The purpose is to progressively unhide sheets as they are completed by the user selecting from the nominated cell. This process must be foolproof and either be automatic or involve no more than selecting a cell value (Yes)

I am now trying to get the progression from 'property details 1' to 'property details 2' which is more difficult. The user can select the number of property sheets that will be reuired in cell 'service details'!D15

Within the sheet the user can fill in up to 49 rows (65-113) of property details, each line is revealed through conditional formatting on completion of the previous, subject to a maximum determined by the user entering the number of properties in cell E7. This can be any number 1 -49.

Once the maximum is reached the next line indicates no more information is required.

At this stage I would like the next property details sheet to be unhidden - the problem is I'm not capable of working out how to get the information from within this variable range to trigger the event.

Any ideas?


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_Range As String = "C13:C13" 'change on each sheet as required
Application.EnableEvents = False
Application.ScreenUpdating = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_Range)) Is Nothing Then
Dim ws As Worksheet
Dim sWord As String
sWord = Sheets("Notes - read me").Range("c13").Value 'Change as required
For Each ws In Worksheets
Select Case sWord
Case "yes" 'Adjust True or False according to sheet
Sheets("service details").Visible = True
Sheets("property details 1").Visible = False
Sheets("property details 2").Visible = False
Sheets("property details 3").Visible = False
Sheets("property details 4").Visible = False
Sheets("property details 5").Visible = False
Sheets("property details 6").Visible = False
Sheets("property details 7").Visible = False
Sheets("Data 1").Visible = False
Case "no", "" 'Adjust True or False according to sheet
Sheets("service details").Visible = False
Sheets("property details 1").Visible = False
Sheets("property details 2").Visible = False
Sheets("property details 3").Visible = False
Sheets("property details 4").Visible = False
Sheets("property details 5").Visible = False
Sheets("property details 6").Visible = False
Sheets("property details 7").Visible = False
Sheets("Data 1").Visible = False
End Select
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub ReSet()
Application.EnableEvents = True
End Sub

Hey guys,

I am trying to set up a tracking sheet for project progress assuming basing on planned activities as by the work plan against how progress of project activities will be done during the life of a project.

So guys, help a brother out.Workplan 24 10 11 8 am - Copy.xls

I made a work order in Excel for my company. Most of the fields are pretty straight forward: name, address, status, problem description, and so on. I also made another workbook to keep track of all of the open work orders. Right now we are creating references manually by clicking on the cells that they're watching, and also hyper-linking it to the original file. Once the work order has been filled out from the template and saved with a different name, we want to be able to track the progress of the work order as we change the original file and have it update automatically on the tracking sheet. What I would like to do is create a macro or a user defined function so it creates all of the references and hyper-links automatically or by the push of a button. I have some limited experience with macros and even some programming so I'm not clueless, but I don't really know VBA. I've attached the two files for reference. Thank you in advance for any suggestions you may have!

What I am trying to do is make a tracking sheet that has a couple of different "Buttons" that increase/decrease values (which I have already set up with the spin buttons) and then takes the information and places it in another sheet and stores it automatically and then the next day it starts the counter at zero again and at the end of the day saves it in a different cell on the second sheet. (IE sheet 1 A1 will display the increases from the button pushes. Sheet 2 D5 will display and save day one's button presses. Sheet 2 E5 will display day 2 (etc...). I am trying to make this as user friendly and hands off as possible. The people I'm wanting to be able to the tracking already have a lot on their plate and this tracking would help everyone I'm just trying to keep it "mindless" if you know what I mean. Thanks for any and all help.

Morning all

I am looking to put togther a small fleet truck mileage tracking spreadsheet, new, but manually backfitted with data from an old, really ugly, spreadsheet. The current person doing the entry is not detail oriented so I would like to lock down most information except for the data entry. The ones that maintained the old sheet would just copy and paste the info and bring all the baggage and formatting and extracting the information is like pulling teeth. There a couple dozen sheets (not in the same format) roughly based on date.

The current daily entries would be truck, driver, odometer start, odometer end, date, date, and hours but most of the older the data (a year to two years old) contains just dates and miles periodically. There are about 40+ trucks in the fleet and they come and go depending on age, mileage, and condition.

What would be the best way to arrange the data to be able to provide the most useable information to satisfy a PR hungry manager? ...minimize or ease data entry?

They seem to like things like -- "the fleet has reached the 7 million miles" or this truck has put in 100,000 miles this year, or this one has 160,000 miles and is ready for a rebuild, or this truck averages 200 miles/day. Each truck runs an average of 60,000 miles per year depending on work load. I'm not used to working with running or accumulating data and dates and I am not sure of the best way to present the information (i.e. pivottable, dashboard, or basic report).

I have a workbook with multiple sheets designed to track sales activity. Currently it is set-up so only one person could realistically use it to track their progress. I would like to have individual workbooks for the Sales Associates to track their own sales activity but then have a Master Sheet that would automatically pull the raw data so I can analyze the company activities as a whole.

For example, There are two sheets below that are examples of what my Sales Associates would use to track sales, there is also a master list. They have the same layout with one exception, the master has an additional column to note which Sales Associate sheet the info came from. I would like this master to pull data from each Sales Associate Sheet (only need one sheet, not the whole book) and also add which sheet the info came from in a column. If it isn't possible to add the column on which sheet the info came from I suppose I could do a work around of adding the SA's name to every row of column A on their individual books and then have the master pull that info too.

I will be keeping each Workbook in the same folder on our internal server (Microsoft Home Server) The Location would be something like TheVault>Sales>Sales Tracking>Beard, Frank Tracking Sheet.xls.

I am using Excel 2007.

Is this possible? I appreciate any insights on how to do this.

Create a tracking list for something similar to raffle tickets.Random numbers such as 654,000 through 655,000 in one group and 656,000 through 657,000 in another group, etc. Track what group a specific ticket came from.

Hi, I just joined as I implemented the code found here:

I need a tweak to it though, if someone could help me.

The column which records the "NEW VALUE" outputs dates as numbers. I would like it if the code could monitor the cell which a user is changing. If that cell has a date entered (in any format, whether it be year first, day first, etc), then the sheet which the log is on (Sheet1, which I actually changed to Sheet4 in the code because I already had 3 preexisting sheets in my workbook) will also have the "NEW VALUE" cell be in date format.

I do not want the whole "NEW VALUE" column changed to date format, because I am using numbers and dates on my tracked sheet, and thus the log (Sheet4) will only change formats cell by cell depending on the input format.

Thanks for any help you can provide.


Since I can't seem to find a answer for this anywhere and I'am extremly new to code I was hoping to get some help.

My question is:
I have a material requsition sheet on sheet1 where I input data into that I would like to extract and make a tracking sheet on sheet2.

so in other words I would like to take data from sheet1 say cells D10,D12,D14,D16 and input it into the next avalible row in column A on sheet2 in order.
Also need to take C12,C12,C14,C16 and in put into the next avalible row in Column b in order.
I would also like the req number and date requested to show up for all items inputed.

This is way over my head so if you could also explain a bit of the code so I can try and learn a bit that would be great.


I have a spreadsheet with several worksheets Day 1, Day 2, Day 3 (up to Day 31) and Work in Progress.

Day 1 has the following colums:

Form Description Product Extension Quantity Manhole Number Customer Project Benching Priority Notes Ready

What I want to do is copy the any Rows from Day 1 if Quantity = 1 and Ready is blank. I then want to have it paste the data into the next empty row in the "Work In Progress" Work Sheet. Once the date is entered into the Ready column in the Work in Progress spreadsheet I want it to copy that data back to the original row in the original worksheet.

I'm sure there is a macro that can do this but can't figure it out.

Any help would be great.

Hello all,

I would really appreciate your help with a question that is very similar to the question on this other thread. It may be possible to modify the code provided on that thread for my project, described below, but I don't know how to do this.

I have a Master sheet called Open with data in Columns A through M. Column M is titled Extend Offer? with 2 possible responses to be selected from a dropdown menu: Yes or Not Yet.

Can someone suggest a code to cut and transfer Columns A through K on the Master sheet to a second sheet in the same workbook, called Offer Tracking when I write Yes under Column M of the Master sheet?

To rephrase my question, once I select Yes under Column M on the Master sheet, I would like the entire row to be deleted from the Master sheet, but only Columns A through K of that row transferred to the second sheet called Offer Tracking.

And if possible, I would like the rows on the Offer Tracking sheet to be organized by date under Column L, which is titled Date Offer Extended.

Thank you in advance to anyone who might be able to help me with a code for this!!

Hi All,

I have looked and found tidbits of code but can't seem to get anything to work.

I have two sheets, for simplicity they are Sheet1 and Sheet2
In Column B I have numbers, not sequencial, but sorted from lowest to highest.

Sheet two is populated via a database that exports to excel
Column B is the numbers again, but there might be more of them.. as the database is used, the numbers increase..

From Say.. Jan 1 - Jan 26 there could be 50 numbers, on the 27th 5 more entries, then nothing until the 30th when 10 more are put in.. from the last export, on the 26 where there were 50 entries, now on the 30th when I run the "report" if you will, there are 15 more. However the report will pull all 65 reports.

What I am trying to figure out how to do, is loop through Sheet2, read in the first number in Column B, then look in Column B of Sheet 1 to see if it's already on the sheet, that is the tracking sheet. If it matches, then I would like it to delete the row entirely on Sheet 2 (the imported sheet from the database) then loop to the next number, on Sheet2 Column B, look at sheet 1, Column B and see if that one is there.. yes... delete that row from Sheet2 etc until the end.

I have tried to combine find, with delete and just about deleted my whole project

I need it in excel VBA, I found a vlookup but it was sheet based.

Thanks in advance.


This problem is driving me crazy. I tried searching for a solution in past threads but couldn't come up with any. Logically, it works perfectly but I'm not well versed in excel and can't get it to work.

I have a very large spread sheet of staff candidates applying for a job with the same global organization on different continents. In a seperate workbook I've been asked to create dynamic reports that generates a list of each canididate in each continent. Needless to say, each continent report grows daily.

I have two worksheets "Candidate Progress Tracking" where all data is keyed in and "Diamond view" where the specific continent lists are generated. In "Candidate Progress Tracking", I have the names of the candidates (G4:G3994) and the respective continent where they are applying for work (J4:J3994).

In worksheet "Diamond view", I have a couple tables set up to report the info (africa, asia, europe, etc.). In the Africa report table, I'm able to find and report the first occurence of a candidate from "Candidate Progress Tracking" applying in Africa with the following formula: =INDEX('Candidate Progress Tracking'!$B$1:$B$3994,MATCH("africa",'Candidate Progress Tracking'!$U$1:$U$3994,0),1). The problem is that I can't come up with a formula that I can fill down to report the next occurence of a candidate that has applied for a job in Africa.

Logical Solution:
I have been able to come up with a way to do this successfully manually by changing the ranges that define the above INDEX/MATCH search. (I'm keeping my fingers crossed hoping that excel can do this automatically)

In worksheet "Diamond View" were the reports are generated, I use the formula = MATCH("africa",'Candidate Progress Tracking'!$U$1:$U$3996,0) to return the row where a candidate who applied in africa appears in worksheet "Candidate Progress Tracking". If I can somehow incorporate the row position returned by this formula in =INDEX('Candidate Progress Tracking'!$B$(returned row position):$B$3994,MATCH("africa",'Candidate Progress Tracking'!$U$(returned row position):$U$3994,0),1) my problem would be solved.

Right now I have to manually run find/replace from the edit menu to change the ranges in the formulas based on the returned row position. Is there any way to write this formula so the ranges will change automically based on the row position of the previous occurence?

Hi All!

I have a simple question, for which i thanked your aid!

Aaron Blood has created an excellent DDE Change Tracking.

The file is in the download area of

The macro, in the xls file, archive the data from top (a1) to the End of the worksheet.

I want invert the way to file/archive the data… copying all the cells already filled, a cell down and filing the top ( A1...) with the last DDE value (In the TracK sheets).

How can I do that?

The primary code is:

    Dim NextCell As Range, r As Double, c As Double 
    With Worksheets(TrackOn) 
        r = .Rows.Count 
        Set NextCell = .Cells(r, 1).End(xlUp).Offset(1, 0) 
    End With 
    r = Range(Watch).Rows.Count 
    c = Range(Watch).Columns.Count 
     'Track the watched range
    NextCell.Resize(r, c) = Range(Watch).Value 
     'What if you wanted the macro to also insert a date & time?
    NextCell.Offset(0, 12).Value = Now() 
     'What if you wanted the macro to insert a formula to measure change?
    NextCell.Offset(0, 13).FormulaR1C1 = "=RC[-12]-R[-1]C[-12]" 
     'What if you wanted to track which user had the workbook open?
    NextCell.Offset(0, 14).Value = Application.UserName 
     'You get the idea.  It's very easy to use the offset to have the macro
     'add things to your tracked output
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Im a newbie in VBA, so someone can example the changes in the macro code?

Thanks for the help!

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