ie. This value M4 needs to change based on the week.
Range("M4").Select
If it's week 1, then it can be say M4, but week two will need data to go to N4.
I have found out how to get the week number from the user. ie.
' Select Week Number
Dim NumSheets As Integer
Dim Prompt As String
Dim Caption As String
Dim DefValue As Integer
Prompt = "What week do you want to get data for?"
Caption = "Week Number"
DefValue = 1
NumSheets = Val(InputBox(Prompt, Caption, DefValue))
If NumSheets > 52 Then MsgBox "Week Number too high"
If NumSheets < 1 Then MsgBox "Week Number too low"
I tried then linking this value saved as NumSheets by:
Dim Rng As Range
If NumSheets = 1 Then Rng = Range("D3")
If NumSheets = 2 Then Rng = Range("E3")
What i would like to happen is after inputing a number into say column B have that number update a formula in cell D2.... so for example... lets say i have
is there a way to make it so when i enter a new number in B4 it will automatically update the formula to display the value for (B4-B3)/B3 and then continue on to repeat this process when i enter values into B5, B6, B7 ect?
I am working on a macro and this section has me stumped. The worksheet columns are A:AE. Occasionally, the procedure needs to select a varying number of cells in a row which don't always fall in the same column, count the number of cells in the selection and then paste the data on the row above it, in column ("AE" minus the Selection Count).
Here is what I have so far:
[Code] .....
The red-bold bracketed code is the syntax I'm not able to figure out how to phrase.
This is a lottery challenge that I am facing at work. 6 numbers are chosen from 45 on a weekly basis. Using about 20 years worth of information, I have code that provides a dynamic frequency list in descending order.
I'd like to now have the user pick a number from the above list and see what other numbers have come up with these frequencies. I guess ultimately I'd like 2 or 3 "favorites" with the corresponding frequencies. Trouble is I've been going around in circles with nothing working
My finacial year starts on the 1st of October of each year and ends 30th of September each year.
My week is from Monday to Saturday - though for calculation ease: lets say that my week is from Monday to Sunday.
I need to calculate the week numbers of a date based on the above two criteria.
I understand that I need to ascertain the day of the start date first i.e: did 01/10/07 fall on Monday, Tuesday etc. - In fact it was Monday!
so for my year 07/08:Week 1 was 01/10/07 to 07/10/07Week 2 was 08/10/07 to 14/10/07ETCSo in the attached worksheet - what formula can I use to populate column D - by using the data in column E?
I.e. All I want to do, is input date into E and B, C, D will be calculated automatically. It would be great of excel could populate A - aswell ;-)
I have a drop down box that chooses the week number of the year (This is based off of a series of data from another sheet).
I need some kind of formula that calculates the following Friday based on a week number. Say for this year (2012) The following Friday for "week 1" is 1/13/12.
(This is for payroll information and I'm trying to calculate the pay date based of of data from that week)
on a macro i use to open, update a file and then save it in an archive.
Opening and updating the file is no problem, but i want to save under a dynamic name in a folder structure. This is a reoccurring task, and this way I update the same file each period but save a copy with the current data in an archive under a different name in the right place of a directory (archive).
My idea is to have a hidden cell in this workbook, where I can have the name calculated by simpe excel-formula, i.e. ="Filename_"&WEEK(TODAY())&"_"&YEAR(TODAY())&".xls".
The file should go automatically into an archive-directory, lets say C:data....archive2007 (2008, 2009, etc) I want to add the last folder to my filename, so my macro knows the first part of the path and has to go look up the actual name it gets in order to be saved.
So I end up with a cell containting the filename: 2007Filename_35_2007.xls
Now I only need the macro which looks up this name, adds it to the hard programmed path: C:data...archive so that the file gets saved under as: C:data....archive2007Filename_35_2007.xls
this is how i start:
Workbooks.Open Filename:= _ "C:data....Filename.xls" ' here the file is updated... Windows("Filename.xls").Activate ActiveWorkbook.SaveAs Filename:= _ "C:data....archive'(how do i do this?)'", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close
I am trying to fill a table of the last 12 values for the purposes of creating dynamic charts. I remember last time i used named ranges, offsets etc etc but been too long to remember how.
Ive attached a worksheet to explain it better.
I should probably mention, I want to be able to change cells C1 and C2 to update the values. Everything else wil be rather static.
I would like to calculate the week number of the month based on a date.
Now my days would only include working weeks (Monday - Friday).
Supposed the date is 12/31/2012:
M 31-Dec T 1-Jan W 2-Jan TH 3-Jan F 4-Jan
Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:
M 28-Jan T 29-Jan W 30-Jan TH 31-Jan F 1-Feb
It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:
M 29-Apr T 30-Apr W 1-May TH 2-May F 3-May
It will be considered as Week 1 of May since it occupies only 2 days of the working week.
Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.
Column A = Date Column B = Reservations made per day
For ex:
A B 1 3/1/2011 5 2 4/5/2011 10 3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2 718-Feb-1124-Feb-11 825-Feb-1103-Mar-11 904-Mar-1110-Mar-11 1011-Mar-1117-Mar-11 1118-Mar-1124-Mar-11 1225-Mar-1131-Mar-11 1301-Apr-1107-Apr-11 1408-Apr-1114-Apr-11 1515-Apr-1121-Apr-11 1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems Having many multiple day / date entries are the same date x 7 days Monday –Sunday (Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours 01/03/10, 9.5 hours labor, 3 hours travel 01/03/10, 9.5 hours labor, 3 hours travel 01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
was looking if you could change a row of cells background colour if certain word is inputted elsewhere. in b2:f2 is data- was looking if, could these cells background colour change when yes is entered in G2? does not matter which is used, VBA or con formatting just cant work it out.
I have a column A with dates eg: 2012/04/02 and I want to add a value into column B 120 but on a Saturday it must return value 80. So Monday to Friday must return 120 and Saturdays must return 80.
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
I am trying to paste data into cells that are offset from specific data in my excel sheet.
The code I've got so far is:
[Code] ....
What I am aiming for is to search my Columns G and H that contain the values 1 & 1. Then from the cells that contain those variables, move 2 row up and 7 columns to the left and then paste on that active cell.
I am completely new to VBA so im sure there plenty wrong with this VBA script but so far ive managed to paste the data but it just pastes across the whole row instead of just the cells ive copied.. now i just get errors on the script.
I'm trying to make a simple chart with VBA based on a row with values that will color the offset cell interior red and also give it a value of 1. (look a the example sheet.)
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.
I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.
------ UPDATE
In response to using CountIFS I have also tried-
=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.
X-Axis = Week1:Week13 Y-Axis =Revenue for each week.
I have a line graph. If "Week9:Week13" have no value, my graphs line drops to 0. How do I make my line graph show no value when no value is inputed for "Week9:Week13"? I think I need to incorporate the =NA() in the original formula but I'm not sure how to do this.
I need to calculate and compare the extra number of hours worked less lunch time (0.5h per day) "per day sum by week" and total "per week" :
- number of hours above 40h per week; - number of hours above 9h per day sum by week; and keep the higher.
I've nearly found the formula to have extra hours for total week =SUMPRODUCT(($B$3:$AK$3),--ISNUMBER($B$3:$AK$3),--(WEEKNUM($B$2:$AK$2+0,2)=AN1))-(SUMPRODUCT(--(WEEKNUM($B$2:$AK$2+0,2)=AN1),--ISNUMBER($B$3:$AK$3))*0.5)-40
But for total week I don't know how to sum per week hours above 9+0,5 per day
as such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?
I am using the following formula, could someone explain this formula in english?
I am interested in modifying the below code so that instead of a text output of "Assignment A (20), Assignment B (5)" it would output only the sum of the numbers "25" for the corresponding week. I believe that this will be more clear with an example, so I'm attaching a small sample which shows what my current output is and what I'd like it to be.
Private Sub RestructureHrs() 'Start of Restructure Dim l As Long ' loop through rows Dim m As Long 'loop through rows to find last occurrance of person Dim lLastRow As Long Dim sPerson As String Dim sOffice As String Dim j As Long 'loop through person Dim k As Long 'loop through weeks Dim sOutput As String Dim sOutputPerPerson As String Dim lNextRow As Long lLastRow = Range("A" & Rows.Count).End(xlUp).Row lNextRow = 2 For l = 2 To lLastRow sPerson = Range("B" & l).Value sOffice = Range("A" & l).Value sOutputPerPerson = ""......................
I have a list of random dates between 09/01/05 and 07/01/06. I would like to assign a week number to each date. For example, I would like it to be week 1 if the date is between the dates of 09/01/05 and 09/07/05. Is there any simple way of going about this?
