# Offset From Cell Based On Inputed Week Number

May 20, 2008

I want to have a variable range.

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")

ADVERTISEMENT

## Update A Formula Based On A Newly Inputed Number?

Oct 10, 2008

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

B1 = 1000
B2 = 2000
B3 = 1400
B4 = ???
D2 = (B3-B2)/B2

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?

## Cell Offset Based On Number Of Cells In Selection

Apr 15, 2014

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.

## Search Row For Inputed Value, Count Each Non Inputed Value

Feb 13, 2007

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

## Week Number Based On Year Start 01/10

Jan 9, 2008

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 ;-)

## Calculate Friday Based On Week Number

Jan 9, 2012

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)

## Name File Based On Date & Week Number

Aug 25, 2007

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

## Find Last 12 Values Based On Week Number / Year

Jul 9, 2014

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.

Attached File : Test.xlsx‎

## How To Calculate The Week Number Of The Month Based On A Date

Dec 30, 2013

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.

## Formula To Identify Week Number Based On Date Ranges And Add Values

Feb 18, 2014

I have the following data:

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.

## Week Number Caluclating Hours For Week

Mar 6, 2010

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 ?

Total Work per day

Total Travel per day

Daily....................

## Change Cell Colours When Inputed In Another

May 29, 2009

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.

## If Statement To Highlight Cell Once Data Is Inputed

May 15, 2009

i am going to be making other columns with data. i want it to highlight the cell from the new columns if the value is higher than the standard.

not quite sure how to do this? do i make an if statement and put it in the cell, then go and input my data over it?

## Format Date Cell With Week Number

Jan 8, 2009

just wondering if its possible to format a cell to display date and week number and if so how to go about it

eg

04/01/09 week 1

## Cell Value Based On Day Of Week

Apr 4, 2012

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.

## Getting The False Out Of The Cell And Have A Blank Cell If There Is Nothing Inputed

Jul 28, 2008

=IF(C22="BLUM MOTION",(VLOOKUP(G22,Info!B14:C22,2,FALSE)),IF(C22="ACCURIDE",(VLOOKUP(G22,Info!E13:F31,2,FALSE))))

Can anyone take alook @ the formula above and help me out w. getting the false out of the cell and have a blank cell if there is nothing inputed

## Formatting Date: Add The Month And The Text "Week" Before The Week Number

Nov 24, 2009

I have a column where I am convering the Date into a Fiscal week number.

For example 10/6/2009 is Work week 41
Now I want to show October Week 41

I need to add the month and the text "Week" before the week number. what is the formula I use.

## Find Last Cell In Range, And Changing Another Cell Based On Offset Text

May 4, 2009

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"

and also t = time()

## Pasting Cells Using Offset Command Based On Variable Cell Data?

Mar 3, 2014

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.

## 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

Feb 15, 2010

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.)

## Using OFFSET Function To Return The Value From Another Cell With Row And Column Offset

May 31, 2014

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.

## Counting The Number Of Occurrences Before The Current Week Number?

Jul 10, 2014

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.

## Converting Number To Week Number

Nov 25, 2013

I have in column A the frequency in weeks which I need an inspection to be done.

I have in Column B the last week in which the inspection was done.

I want to produce in column C the next week required to perform inspection.

So lets say:

A B C
26 28 54

If I add the 2 columns I get week 54. Is there any way I can convert that to read week 2?

## Line Graph Show No Value When No Value Is Inputed

Dec 29, 2008

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.

## Sum By Week Number Using Array?

Sep 15, 2014

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

test extra hour.xlsx

## Change Week Number

Jun 18, 2009

How can i change week number to start from 1 if my year starts 1/07/2009 if i use function = weeknum() it returns the value 27.

## Wrong Week Number

May 4, 2006

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?

=CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))

## Number Of The Week In Month

Mar 26, 2008

I want to use a formula to calculate what week number in the month it is (i.e 1 to 6) from a particular date.

I know how to calculate this on an annual basis (i.e. 1 to 52) but not within the month.

This is what I have so far...

=IF(OR(D58=1, D58>=D57), ROUNDUP(DAY(D61)/7,0),ROUNDUP(DAY(D61)/7,0)+1)

d58 is a Weeday formula looking at d61 which is the date i want to look at. d57 is the weekday number of the first day of the month in cell d61.

## VBA Macro To Sum By Number For Corresponding Week

Nov 26, 2008

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 = ""......................

## Assign Week Number

Jul 12, 2006

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?

Copyrights 2005-15 www.BigResource.com, All rights reserved