I have a sheet (sheet2) that this week has the formula
Cell B2
=Sheet1!D2
Cell B3
=Sheet1!B2
Next week I want the formula to be
Cell B2
=Sheet1!F2
Cell B3
=Sheet1!D2
And so forth.
Each Column has the Week ending date (a sunday) in Row 1. So D2 represents this week and B2 Last week, until next week when D2 becomes the 'last week' and F2 becomes the this week.
The inbetween letters contain another set of data for those weeks so i will apply the same formula to these.
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
So I have this sheet template to copy for various other sheets, and on A3-A1000, they contain dates from August 1st 2013 to whenever. Starting with A3 as August 1st. The problem is now, it's September 10th, and I would like A3 to be a week before September 10th. Then A4 would be September 11th. And so on and so forth. The dates also must only consist of weekdays and the dates must be hard coded. Is this possible? I have the simple code for copying over the spreadsheet, but I don't know the rest.
How can I edit a cell's value without changing the formula within?
for example: cell A2=$B$2 cell B2=$A$2
-I allowed for this circular reference and now I want to be able to change the value in one cell and have it appear in both cells without removing formulas.
I currently have some code in VBA that for each line creates a vlookup and a second one that takes the left part of a cell, depending on where a "/" is located in the cell as below:
For i = 2 To FinalRow Cells(i, FinalCol + 1).Formula = "=VLOOKUP($A" & i & ",LookupList,3,FALSE)" Cells(i, FinalCol + 2).Formula = "=LEFT($F" & i & ",FIND(""/""," & "$F" & i & ",1)-1)" Next i The issue I am finding is that running the 2 formulas for 4000 rows is taking approx 5 mins. The end user doesn't need to see a lookup, the value stored from the result would be fine.
is this possible. So that the value of the cell is a result of a formula I have created?
explain or point me the right direction for the problem below: I need to find a way to vlookup a spreadsheet that has changing dates in the file name such as filename_xx.xx.xx. This is a piece to a macro I'm currently working on.
I want to copy =d8*k10 into several cells, but the references keep changing. I've tried several things that I've found on the internet, but nothing seems to work and the I can't seem to copy to a columnof cells.
I think this should be easier than I am making it out to be, but the answer is escaping me....
Among other things, I have a workbook with these worksheets in it: Hours, Cost, Profit, Revenue.
Columns A, B, C & D should be exactly the same on each worksheet. So, I have all the data for these columns entered into Hours, and then reference that worksheet on the other ones.
That works fine until I sort it differently and then instead of having row 2 reference row 2, it will be in row 9, etc.
Now I know I can use =+Hours!$A$2 for the absolute reference, but then i would manually have to change the reference on each cell.
SO - (finally the question) Is there a way to use the absolute reference without having to manually enter it into each cell?
I have a function Sum('1st Qtr:4th Qty'!AW1) in row 1 and dragged down 129 rows so the last reads Sum('1st Qtr:4th Qty'!AW129). If I insert a row the cell reference does not increment automatically below the insert location. How do I get it to do so. The insertion occurs when I run a macro.
I've got an issue where Formulas Referencing Cells on another sheet are getting changed automatically to refer the the moved cell data. The formulas on a sheet (2) refer to specific cells on sheet (1) within my workbook. I update Sheet(1) data with a macro. When sheet (1) updates, the present data gets shifted to the right.
In other words, I need to prevent the formulas from changing to reference the moved data values and keep referencing the initial cell with the new values. xample of change that occurs with update:
how I can have a formula repeat down a column five times before it changes to another formula? For example. Say on tab 1 I have a list of products. On tab two I have five codes that repeat down the page over and over again. On tab two next to the repeating codes I need to repeat product one 5 times and then skip to product 2 on the 6th row and repeat five times and then skip to product 3 on the 11th row and repeat 5 times and so on?
I have one sheet with all the formulas for the entire workbook and would like to copy and paste the formulas from Sheet 1 to Sheet 2...Sheet 1 to Sheet 3 etc. without changing the cell referening in the original formula. I am not too sure how .formula works.
Sub CopyAndPaste() ' To copy formulas from Summary sheet to their respective sheets
With Worksheets("Summary") .Range("R3").Copy Worksheets("2").Range("X3").Formula = Worksheets("Summary").Range("R3")
I am trying to cut/paste a row of formulas in my spreadsheet. The problem is that I want some of the referenced cells in the formula to stay CONSTANT and not increment on each new row. In this example, I want the references to row 17 (F17,E17) to increment (E18,E19,...), but I want the "G7" reference to remain hard coded (as G7) on each row I paste (because that is where my master value is). (The formula is working fine) =IF((F17>0),IF((F17<=G7),E17,0),0)
I have a list of pricing and I want to be able to change the text of cell, say "D1", which says "Standard" to say "Premium" and all the formulas would change in accordance. The "Standard" pricing would be, Starting D2, "=C2*.3" and I want them all to go to "Premium" pricing which would be formula "=C2*.35".
What would be the best method of creating this function or formula?
need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?
For example (Sunday being the first day of the week): January 5th 2008 = Week 1 of January January 6th 2008 = Week 2 of January February 2nd 2008 = Week 1 of February February 3rd 2008 = Week 2 of February
WEEKNUM perfectly calculates this, but it is applicable for the whole year.
Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.
The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).
So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).
I need to be able to keep a running count of how many gallons of gas i put in my car each week. Each week is one column. Column A is where i want the total to show. column B,C,D,E...etc is where i put the numbers in this is all in row 1 for now.
currently i have =sum(B1:BB1). But something is not right because it is not adding the numbers together it will only add what is already there not any numbers that i put in after the formula is made. Do i have the wrong formula or something else wrong. My goal is to see how many gallons i put in at the end of the year, month, quarter, and so i have several other reason for this info.
I need a formula on Cell C3 on the attached Sheet1.
This should add numbers from the Actual columns as they are updated; i.e., as soon as I populate 'Actual' columns such as F, I, L, O, R... Cell C3 should add up the numbers automatically. This way I don't have to update the Cell C3 manually each week I populate the Acutal columns.
I am trying to create a week ending formula in excel. I have a list of dates and in a new column I would like to display the week ending of these dates. I want the weeks to end on Sunday. For example, if the date column has the date 7/24/12 in it, I would like the week ending formula to spit out 7/29/12. What formula would achieve this?
On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:
(this is not the actual formula but simplified, that's not really important).
It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):
I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula
(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be: (0.2*6)+(0.3*6)+(0.6*6)+(0.9*6)/(6).
Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective
I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.
formula that will count the number of times the name 'SIMON' appears in column C:C but here is the catch: I only want to know how many times that name has appeared over the course of the previous week. IE NOW - 7days
I have a formula that displays the 1st monday of a month, I need the formula to take into account which day is selected and then display the first of the selected days date for the month.
In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.
