Monthly Task List
I am trying to create a task list from a master matrix of items to be completed. When fully populated the maters matrix should have approximately 200 items to be completed over the year. I want to create a monthly to do list in EXCEL based on the master matrix. Some reports will be due on various timeframes. The reports will also be assigned to different workers. I would also like to be able to provided brief instruction for each task. Items will be added and subtracted from the master list based on changing requirements. I would like each month to be a different tab in the workbook. Attached is a spreadsheet that better explains what I am looking to create.
View Complete Thread with Replies
Related Forum Messages:
Each Row In The List Has A Task And Assign Email Address
The above is basically an action list showing who needs to do what. Each row in the list has a task and an assignee and the assignee's email address
I want a macro (that will be attached to a button or text in column H) that will copy that entire row (as well as the headers in ROW 4) and email that info via the default email application being used on the users PC (most likely to be MS outlook). i.e if i click on the button or text in column H for Task A (Row 5), an email will be sent to JB@excel.com (column G), for Task E (Row 9), an email will be sent to TB@excel.com,...etc.
I would prefer it if the info was sent as the actual message in the email instead of an excel attachment.
Calculating Task Time
I have a sheet to calculate Time Elapsed based on StartDT & EndDt taking into consideration the actual working hours in any working day and the function exclude the weekend. I am not a guru in excel but i found this formula in this website .
the formula works perfectly except for the fact that in our part of the world we have "Friday & Saturday" as the weekend days instead of Saturday and Sunday.
is there a way to get this formula or any similar formula to take different weekend days and do exactly what i need?
Cannot Complete This Task With Available Resources.....
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
Task Bar Buttons Moving
I am using code that creates a new workbook and then copies a number of sheets to the new workbook. I am using Application. ScreenUpdating = False but as the sheets are being copied over, the Excel task bar button jumps around.
Run Macros In Scheduled Task
I've used Excel 2003 to write a macro to analyse some date from our internal proprietary database. I use some Perl to generate a bunch of csv files from the data, and the run the macro to generate graphs and statistics from that. What I would like to do now is run this entire operation as a scheduled task, so that, every week or so, the data is downloaded, the processing is done on the csv files, and the results are saved to a location to be viewed on our internal site.
I'm assuming that the best way to do this would be somehow run something from the command line (to be embedded in a script) which basically says "1) Open this csv file 2) run this macro 3) save the output to this location", but I can't figure out how this can be done.
Dependency Formulas For Task Worksheets
I am trying to build a worksheet that is sort of a Project Plan. Why am I not using Project - simple - not everyone in my company has Project, and exporting in Project 2007 is just nasty!
The issue can be described with a simple example:
Imagine the sheet with the following columns
Task Ref - Task Description - Dependency - Start Time - Duration - End Time
Duration is in Minutes, End time is calculated as a formula adding the Duration to the start time using the TIME function.
Dependency contains the Task Ref of the task that must complete before the task in question can start. I would like to have a formula for start time, that pulls in the value for end time of the task identified in the Task Ref column -
How Many Seconds Has My Little Girl Been Alive Task
I'm trying to construct a gimmick which will represent how many day’s my little girl has been alive and am having some trouble trying to figure out which route to take in trying of constructing my timers
I'm trying to represent how many seconds my daughter has been alive in real time,
She was born on 01/06/2005 and I want to be able to see the ticker ticking away on my screen rather than it being static
In fact I would like to illustrate how many years, months, weeks, days, and seconds Loren has been on this earth,
If successful I would like to take it one step further and do this with words as well as figures
Perform Same Task On All Sheets In A Workbook
I am trying to make certain changes to the page setup on all sheets in a workbook. Here is my code but something is not working. I think I need to somehow select the next sheet because the macro is only performing the task on the active worksheet. My Visual Basic skills are somewhat limited so I'm sure this is not the most efficient way.
2 Of Same Workbook Open In Task Manager
I am running into a problem with my code. It sounds almost exactly like a previously posted problem 2 Copies of The Same Workbook Open in Task Manager but I never saw a fix.
I am also getting two copies of a workbook in task manager. It appears when I do a Workbooks.Open filename.
1 Command Button To Do Different Task When Clicked A 2nd Time
Currently I have 5 identical command buttons which do something similar but in a different cell.
What i want to do is sum up the 5 different task using a single command button.
When cmdaddsp1 is clicked the first time cell "B4" is populated with a value from a table.
When cmdaddsp1 is clicked a second time cell "B5" is to be populated with a value from a table.
When cmdaddsp1 is clicked a third time cell "B6" is to be populated from the table,
and so forth.
Is there a loop or anything that can be used for this? I only want other cells to be populated when the command button is pressed.
Show Multiple Open Files On Task Bar
I do'nt know really my problem is regarding excel or with windows, actually stange thing starts happening, I open multiple excel files but only one general excel instance is showing on the task bar. If I need to switch between the file I have to minimise the one I am using and then select other to open it. Even shortcut Alt+Tab switch is not working to switch between the excel files.
Unable To Get The Task Pane To Display On Startup
I am working in Excel 2003 and have recently lost the Task Pane when I start Excel. The box in Tools Options to display the task pane is checked. I have modified the registry to include a DoNotDismissFileNewTaskPane value and have even added the following code to the Personal.xls file:
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Task Pane").Visible = True
If I remove the On Error Resume Next line I get the following error:
Runtime error '-2147467259 (80004005)' Method 'Visible' of object 'commandBar' failed
I did add the Analysis Toolpak last week but am not sure that this had anything to do with it.
I can go into the View menu and show the Task Pane and do have the CTRL+F1 option to display it.
2 Copies Of The Same Workbook Open In Task Manager
I have had some problems closing down a workbook that I have. During a save and close.
A duplicate file appears and causes considerable time delay...sometimes even causes a windows crash. I can't find the source of the problem, but I have noticed that in the Windows Task Manager, the file is there as well, yet not visible on the screen.
Calculating Task Completion Time In Working Hours
The below is for Excel 2003.
I want to set up a spreadsheet that can show my work is completed within a service level agreement, based on working hours of 9-5 Monday-Saturday.
I want to be able, for example, to log that a piece of work is reported at 09:00 on a Monday and completed at 10:00 on Monday and for excel to calculate that as 1 hour until completion. Fine so far. But what if that job was closed at 10:00 two days later? Based on an 8 hour working day, that should be 17 hours.
And what if a bit of work comes in at 17:00 on the Saturday. No one works the Sunday. Say the job is completed at 10:00 on the Monday. that should calculate as 1 hour to completion.
The SLA I'm setting is 4 hours.
User Has To Click Button To Mark Task As Complete....
I have a spreadsheet that users update which I then want them to perform a number of checks on. At the bottom will be a button for them to click stating "Click here to confirm checks completed". When they click it I want it to put a flag somewhere that I can then use to prevent them from saving if they haven't clicked the button. I know how to do the saving but, but how do I get the button to put a flag somewhere?
Task To Automatically Move To Another Worksheet For The Relevant Month
I have created a spreadsheet for internal use in my workplace that logs task by members of staff, What I am trying to achieve is when someone selects from a drop down box that a task has been complete I want the task to automatically move to another worksheet for the relevant month, as I will have worksheets for every month logging all the past tasks that have been assigned. I believe an IF statement should do the trick, but I am having no luck so far.
Repeated Task, Moving Informatin Between Works Books
I have string of tasks I need to automate so that they can repeat hundreds, maybe more than a thousand times. I have no experience with VBA, but I was looking through the VBA/Excel book published by this site and have been creating and adjusting enough macros for the last few days trying to figure this out. I have figured out the basic language and can follow simple instructions (big words scare me). Here are the tasks I am trying to automate:
1. I need to take a six digit string of numbers from one cell in what I have named The Primary work book.
2. Take those numbers and tack it on to the end of a file name (i.e. c:FileOtherStuffThisIsWhereOurNumberGoes).
3. Open the file, open the excel file within the file (which will also have a name using the same six digit number as before and maybe one other word/phrase, like ‘rating’ or ‘rating benefits’).
4. Then in that workbook I need to go to the second page and take the result of one formula from a cell which will probably be the same cells (i.e. E8) in every file and paste that information in the appropriate columns in The Primary work book.
5. Repeat Step 4 in the same file, on the same page of the same work book, except one cell down (i.e. E9), which is also the result of a formula.
6. Back in the Primary Work book, we go back to the place where we started and go down one cell and the process repeats and repeats and repeats.
Count IF And IF Then Subtract Two Times In 24 Hr Format To Recieve Time On Task
I get a spreadsheet downloaded to excel with a variable of 15 names.
I want to create a formula that if the name Cleardale appears to take the time responding in cell BD and subtract it from the time available in cell AV so that I have the time on task and then to add up all those times in a total time on task for all cleardale listings for that day. The times are listed in the 24 hour format. I keep getting errors. Can anyone help me out on this one? The range of the cells is from AV7 to AV500 for available time and BD7 to BD500 for alert time and the range of the names is cell f7 to f500.
I have a two column A and B at Sheet1. Col A is a Date wise column and Col B is of the Values. I need formula for getting sum monthwise in sheet2. i.e the sum of jan, feb, mar etc
Vlookup-Sum By Monthly
I wish to create a Vlookup & summing qty by month.
In sheet1 is date
Column A = Date
Column B = Code
Column C = Qty
In Sheet2 I wish to create Vlookup.
I have the code in Column A4 going down AND wish to create Vlookup in column C4 for month Apr-09(c3) display total qty.
In sort look up code - for the month and sum qty .
code Apr-09 May-09 Jun-09 etc
SW L 60 15 55
SW M 10 35 45
DD L 30 30 25.
Run A Macro Monthly
I have the following situation. I work off-site most of the time in another office. I would like to have a macro on my main office PC to run on a monthly basis at a set time.
For instance on the 15th of every month at 9:00 am the macro would go into a shared drive and pull an updated copy of a report, redo the report and send it out to various offices. I have VB code that performs all the tasks except for setting a timer to tell it too run on the 15th of every month at 9:00 am.
VBA Autosave Workbook Monthly
I wonder if anyone could help with some VBA code to automatically save a copy of a workbook on a monthly basis (Say the 1st of each month, or the nearest date to that if it has not been saved on the 1st.)
I would like this to be done without user input, and to be saved with the current workbook name + the current date. It can also be saved in the current workbook folder. I am using Win XP Pro and Excel 2003. This would also obviously need to check if the file currently exists, we can say the filename is Cars and the directory is C:Data. Finally I do not wish to stop the users from saving their own version with save or save as.
Calculating Monthly Returns
I have a list of dates with respective balances. I am wondering how to get Excel to determine if the end of the month has passed after a certain date and then calculate the return for that month. What is the most efficient way in your opinion?
Here is a small data sample:
27/12/2005 06:23 0.3%
27/12/2005 05:47 -0.6%
29/12/2005 06:53 1.3%
04/01/2006 17:55 -0.1%
09/01/2006 15:35 3.99%
09/01/2006 15:46 2.54%
09/01/2006 17:07 1.8%
12/01/2006 07:12 -2%
12/01/2006 13:37 1.5%
12/01/2006 13:39 0.8%
12/01/2006 13:58 0.01%
Formula For Monthly Totals
I need to create a running monthly total formula for a worksheet. This is something i need in the data, so i can manipulate it without using a pivot table.
I think I would use the SUM, MONTH, and IF formulas, and maybe EOMONTH.
I guess where I'm stuck is figuring out how to make conditions for my SUM formula. So as the dates go down the page, I would like a running total in a column to the right, that will also restart with every new month.
Attached is my example.
Formula - Monthly Budget
I am looking for a formula to sort out a budget problem I am currently working on... I am guessing that the formula is pretty simple but my mind is blank...
What I am trying to do is:
Cell A1 is my cash budget
Cell A3 is actual cash used
Cell A5 is The Difference between A1 & A3 (sum A1-A3)
What I want in Cell A7 is a formula to tell me if my Budget is over 50% either way..
For instance if A1 = 100 and A3 = 155 then I am 55% over and would like a note to appear saying "Explanation required".
If A1 = 100 and A3 = 45 then I am 55% under my Budget and would like a note to appear saying "Explanation required".
Monthly Sheet Copy
there is probably is shorter code too
Dim sLastMonth As String
Dim sNextMonth As String
Dim iNextMonth As Integer
sLastMonth = Worksheets(Worksheets.Count).Name
iNextMonth = Month("1 " & sLastMonth) + 1 ' Need any valid day of month
sNextMonth = Format(DateSerial(2000, iNextMonth, 1), "mmm") ' Use any valid year & day
Calculating Monthly Totals
best formula for adding figures in a column that correspond to each month of the year?
I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet.
Autofill Macro Monthly
I want to do is set up a macro to autofill into a new column every month. My data is linked to another excel document so that is where it is pulling the data from. Currently I have data in columns monthly from Dec 2001 until June 2008. I just want a macro that will automatically add in the next month, so in this case July then August the following month ect.
Update A Cell Value Monthly
I would like to add avalue of 100 to cell E1 on th 19th of every month.
I currently have this code which I realize will just update every new month instead of the 19th but does not seem to work right.
It only works if I leave cell A1 blank and then it inserts 1/19/1900 into cell.
Private Sub Workbook_Open()
'Compare today's month against value in A1
If Month(Now) > Sheets(1).Range("A1") Then
Sheets(1).Range("E1") = Sheets(1).Range("E1") + 100
Sheets(1).Range("A1") = Month(Now)
Compare Monthly Worksheets
I am trying to compare almost 2 identical spreadsheets. Please see the attachment below. This is used by our HR department to identify people that are active or inactive, department, title, who are their supervisors, etc. This spreadsheet will be updated monthly to reflect all the changes (new employees, people move from one dept/home location) to another, come back from temporary leave, promoted, or terminated). I saw some macro code application to highlight the differences of 2 spreadsheets but the spreadsheets are so much simpler than mine. We have approximately 1000 employees.
Generate A Monthly Job Chart
I want to have my monthly job chart automated. I got a leave chart marked with workers' leaves on a month basis. Each month, I got to consolidate a job roster containing names of workers who will be working at the counter. It takes me almost 2-3 hours to have the roster completed. I attached here with an excel file for your information. Can it be done automatically by vba if data of workers' leaves are available? The names assigned will be at random in seqence each month(i.e. Peter will be at counter work on 3/10/2006 if not on leave but may be on 08/11/2006). That's why it takes me so long to compile the roster.
Automatic Monthly Update
I have a workseeht that consist of 13 tabs. This is what i am trying to do:
Each tab is for a month in 2007. The main tab is formated to show a recap of each month. So when the Janruary tab has been completed the main recap page should pull data from its tab. Then once the february tab has been completed the main recap tab should now show the data from this tab, and so on and so on.
I am currently updating the data manually by refrencing the new tab in the main tab formulas. I hope there is an easier way for me to get this data without manually inputing formulas every month.
First Entry On New Monthly Sheet
I have a daily nutrition log that is made up of monthly sheets. New entries are placed below the last entry, into the proper sheet by date, automatically.
The problem I am having is to find a way to put my formulas and formats into row 1 of each new monthly sheet so that when the entry is for the next month, it will add the new entry into row 2 of the new month, seemlessly, without manual intervention.
The formula I am using works from row 2 and down. It doesn't work on row 1.
Here is one of formula's that works on row 2:
When I copy/paste it into row1 (to get the starting reference row), I get this:
The code copies the last row (in the case of a new sheet that would be row 1) and pastes' it into the next row down and then overwrites the first five cells. The problem formulas are NOT in the first five cells.
I tried to using $A65536, but it also #REF! error's out.
Monthly Analysis On Data
I am trying to do some analysis on montly bank account data. To do this I need to take the bank statement information for the month and put it into a table that shows every day of the month (see attached example).
I am currently doing this manually but I'm sure there must be a smarter way of doing this.
Update Monthly Reports
My workbook contains 12 monthly spreadsheets (one for each month of the year). It will also contain an additional spreadsheet, I’ll call “Update”.
At the beginning of each month I will copy and paste new account information into the monthly spreadsheet (for whatever particular month we are currently in). The columns (J-N) will be blank. I will then copy and paste account information into my “Update” spreadsheet (this is done twice a week per month, from 8 to 10 times per month). This spreadsheet will contain data that needs to update my monthly spreadsheet (for whatever particular month we are currently in). The “Update” spreadsheet column B needs to populate columns J-N on my monthly spreadsheet and this will depend on column C of the “Update” spreadsheet.
Sorry to be confusing . . . .
Column B – Payment Amount (dollar amount)
Column C – Commission Type (Low, Medium, High, Super, Duper)
these columns need to be populated with the dollar amount
Column J – Low
Column K – Medium
Column L – High
Column M – Super
Column N – Duper
I forgot to mention that all data is compared via the account number!
However, to make it even more confusing if I am updating my monthly spreadsheet and during the 8 to 10 times I update it during the month I find that I already have a dollar amount posted for a particular account for a particular commission type (Low, Medium, High, etc.) I do not want it to keep adding up the dollar amounts. So, if I have an account that has $25 for “Low” and then next time I update my monthly spreadsheet I have an additional $75 for “Low”, for the same account, I do not want it to calculate it to $100. I need it to either color code that entry on the “Update” spreadsheet or toss it onto another spreadsheet . . . just so it stands out and I know that I have to handle it differently.
Calculating Monthly Investment
If I want to obtain a future value of $500,000 at the end of 20 years, how much do I need to save each year at an interest rate of 10% per annum? I ended up using the following formula: =PMT(10%,20,-PV(10%,20,,-500000,1),,1). Let's define (Insert/ Name/Define) the answer to this function as Pmt1.
By then using =FV(10%,20,Pmt1,,1) to confirm that Pmt1 will end up providing $500,000 after 20 years I get the answer I am looking for but have absolutely NO IDEA why it works. Worse, I do not know whether it is the correct answer. I have the following function (courtesy of someone) that I use to determine the expected future value of a series of annual payments at a fixed interest rate but also with fixed annual increases in the payments. (Example: $1000 per annum is invested for 20 years. The interest earned on the $1000 is 10% per annum. The $1000 increases by 5% each year - i.e. 19 increases)
Assuming the payment does not increase, I simply replace the 5% with 0%. When I run this function and use Pmt1 as the annual payment the answer differs from the one that I get from the PMT function that I quoted above until I change the ",,1" in the function to ",,0". What do I not understand about these functions!? Which is correct or are both provided I learn to know what they do? This is the vaguest question I've ever been able to devise simply because I can see that something is amiss and I do not know what - or how to start figuring out what it is that I "know not"!
Resource Error Message "cannot Complete This Task With Available Resources" When Changing Charts Thru VBA
I have the following code for a sheet in my workbook that has 3 charts:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
but whenever the sub runs, I get this error message: "Excel cannot complete this task with available resources. Choose less data or close other applications." Does anyone have an idea what's going on?
Data Finding On Monthly Update
What I've got is 2 worksheets. One is updated every month with new data and therefore the style of the new worksheet is likely to change.
The other one is static and needs to take the data from the updated one. I can't just use a basic =sheet1!A1 because the data will get moved around.
I need to be able to tell it to: look in the new sheet, based off the column heading and row heading find the cell and copy it to the same place on the static sheet. e.g.
In G14, I need it to find on the second sheet "Apr 08" as the column heading and "Yell.com" as the row heading and put it in G14.
It would be easy to do it manually, but as the spreadsheet grows, it'll be harder and harder.
Total Monthly Values For Individuals
I have monthly a list of 2,500 people with an allowance value for each individual shown.
Problem I got is that sometimes there is more than one allowance entry for the individual in each month. I just need the total for the individual in that month. Each month is on a separate worksheet. I have been racking my head, but I can’t figure how I can look at the list of people (who have a unique number) and just total it.
Overtime In Semi Monthly Timesheet
I’m trying to take an existing employee time sheet in Excel (Office XP) that has no formulae whatsoever, and add the appropriate formulae so that all an employee needs to do is enter the daily start and end times and the time sheet will calculate daily, weekly, and overtime hours worked. Among others, some of the problems I’m having are:
I need to keep the original format (though I've added a few columns).
Overtime in the State of Texas does not apply until after 40hrs have been worked. Then any daily hours over 8 can be applied retroactively. So I need a timesheet that shows overtime as regular hours worked until 40 hours have been reached, then separates the daily overtime from the regular column and places it in a daily overtime column. Shouldn't be too hard to find...Right?... Actually, that’s been quite easyexcept for weekends. Saturdays and Sundays are usually overtime but not always.
The real problem is the beginning day of the pay period, if a pay period begins on any day other than Monday (Wednesday, for example,) then weeks one and sometimes three can never equal 40 hours each unless the assumption is that the days worked in the same week but prior or subsequent period are worked at 8 hours each. The formulae must make this assumption. How do I write a formula that assumes an empty cell actually has a value? :o
I know that it’s difficult (if not impossible) to offer any suggestions without seeing the time sheet itself, so, If it would be helpful, and anyone has any suggestions. I’ve uploaded the week one of the timesheet as it stands now.
If you'd like to see the entire worksheet I've uploaded it to ....
Pivot Table & Monthly Worksheets
I want to create a summary pivot table report + chart. What I have is 12 worksheets, one for each month, each with total amount invoiced and gross profit by client.
I want a pivot table that shows me the clients and amounts each month (this is the bit stumping me). Basically I want a drop down box to select the month on the pivot table, which then shows the data from the relevant worksheet (Aug-09, Sep-09 or whatever).
I've tried using multiple consolidation ranges but this just aggregates all the data; it doesn't allow me to limit by the worksheet the data has come from.
I'm wondering if I have to consolidate all my months into one big sheet, then add a field for the month. I would rather avoid this but accept it might be the only way.
Final thing; can't use VBA at all (too much hassle for IT apparently).
Modify Macro To Use File That Changes Name-monthly
I have this macro that I am using but want to automate the file it uses. This file has the last 5 text/digits change each month, e.g.
Clearly the next file would be Apr07. I would like the macro to identify this automatically and use the correct file without having to go into the macro every month and change the last 5 text/digits. If you have any ideas I would be very grateful to hear from you. The Macro is below:
[/code]strPath = "c:Internet"
strFilename = "INTERNET_GB_FEB07"
strThisWkb = ActiveWorkbook.Name
intReports = Workbooks(strThisWkb).Sheets("Lookups").Cells(1, 2)
Red(-ve)/Green(+ve) Text For Under/over Monthly Budget
I've devised a monthly savings sheet to allow me to track my savings towards a deposit on a house. My target is £xxx a month, but as I could contribute loose amounts of money I'd like to be able to display a field next to each month of how much over I've contributed or how much under I've contributed.
I'd like this to be represented as:- If I contribute more than £xxx for a month then display it as green text and calculate how much over has been contributed.
If I contribute less than £xxx for a month then display it as red text and calculator how much under the contribution has been. I'm really not sure how to do this, I would do it manually normally but surely there is a way of automatically representing it.