
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
I have a worksheet that starts with daily data, a date, then a dollar value,
then there is weekly data that needs to reference the daily data and
increment 7 rows each time. Every way I ahve come up with wants to increment
by 1, I used OFFSET to move down 7 rows, but then the next weekly cell OFFSET
increments the original reference by 1.
The daily data references the previous day and then adds any changes. So
for the weekly data, I need to just move my reference down 7 rows each week.
Thanks,
Perry
Similar Excel Video Tutorials
Cell References Relative & Absolute
 The Excel Basics Series shows a systematic description of what Excel can do from beginning to end. #8 Video topics: 1)Cell references in for ...
Incrementing Numbers In Formulas
 Learn about the ROWS, COLUMNS, MOD, ROUND, and WEEKDAY functions :to: 1)Increment from small to big 2)Increment from big to small 3)Inc ...
Similar Topics
Hi all
New to ozgrid  great site.
I am trying to create a formula to extract the summary values from a daily worksheet into a weekly spreadsheet.
The daily worksheet has the days of the week running down and then a summary and then the next week and a summary eg a1 Jan 1, a2 Jan 2.. a7 Jan 7, a8 summary week 1, a10, Jan 8, a11, Jan 9,..., a17 summary week 2. In column B the various values and the sum for the week.
I am trying to do this with the offset formula in the weekly summary sheet but am stuck.
The formula I am trying is =+OFFSET('Weekly Record'!B8,9,0).
I somehow need to reference the new cell reference in the next formula.
HELP!!!
Hi everyone
I am slightly stumped here, I have a cell on my first worksheet referring to a cell on another worksheet within the same workbook.
Here is what it looks like: ='DEPARTMENT WEEKLY PERFORMANCE'!$E5
Now I want to use that reference as the beginning of a fill . based on what I read re absolute references the column (E) should remain fixed where the the row reference (5) should increase ( by 1 per cell I assume )
So I expected to see a fill across cell like:
='DEPARTMENT WEEKLY PERFORMANCE'!$E5
='DEPARTMENT WEEKLY PERFORMANCE'!$E6
='DEPARTMENT WEEKLY PERFORMANCE'!$E7
...
but it doesnt. Instead if fills the entire fill range with the original ='DEPARTMENT WEEKLY PERFORMANCE'!$E5 no increment in the row number.
Am I doing something wrong here? Is it a bug ?
Is there a symbol to force Excel to accept the cell number as a relative reference?
I have daily sales data in one worksheet, and in another worksheet I keep weekly data  some aggregate, some collected separately. I want a less tedious way of filling in the formulas that are aggregates of the information in the daily worksheet.
So  in worksheet 1 (we'll call it W1) I might have
Date / Sales
January 2 / 12
January 3 / 13
January 4 / 17
January 5 / 11
...
January 27 / 19
January 28 / 17
January 29 / 14
In Worksheet 2 (W2) I have
Week / Sales
1/2  1/8 / sum(W1!A2:A8)
1/9  1/15 / sum(W1!A9:A15)
1/16  1/22 / sum(W1!A16:A22)
1/23  1/29 / sum(W1!A23:A29)
easy enough to write the formula for the weekly sales number, but my problem is that I'd dearly love to be able to drag and autofill the formula, and I think there must be a way.
I basically want the formula cell references to increment themselves by 7 instead of by 1. So instead of dragging sum(W1!A2:A8) and getting sum(W1!A2:A9), I want to get the next week's numbers, i.e., sum(W1!A9:A15).
The Offset function seems like it should help me, and so does the Address function, but I can't figure out how to make them work together  any help much appreciated!
Dear all,
sorry for probably a trivial question:
I am creating a spreadsheet converting daily values into weekly ones in the next tab and monthly values in another.
Formula for the daily ones, converting daily cash values into weekly, created by hand looks like:
=SUM('Daily Sales'!B35:B41)
Now, there's several different amounts (as forms of payment), adding up to 4 different totals. (net, taxed and so on..)
Same situation with monthly one:
=SUM('Daily'!B32:B62)
Once I would like to change to the next column (as a different payment form) it obviously goes:
=SUM('Daily'!F32:F62)
My problem is:
once I have created the weekly formulas (three rows of them) I cannot just copy and intelligently paste to all the below weeks, because excel starts counting from the next cell below, not from 7 cells down (as of 7 days in the week). Intelligent filling in doesnt work either.
Row by row it looks like:
=SUM('Daily Sales'!B35:B41) so its week 1
=SUM('Daily Sales'!B42:B48) week 2...
=SUM('Daily Sales'!B49:B55) week 3..
coming to week 4, when copied or intelligently filled by excel it starts:
=SUM('Daily Sales'!B38:B44) as of 3 cells down from the original one.
Al I want it to do is to count the cells from 1st tab in the rows of 7 as weeks and 30 or 31 as of months respectively.
can Someone please help me?
thanks in advance.
Hi,
Here's my situation:
I have a workbook in which I'd like to reference a cell in another
worksheet, but would like to increment the reference as I move down rows
in the original worksheet.
For example, on Worksheet 1, cell A1 contains "=Worksheet2!A192". In
cell A2, I would like to reference "=A1 + [whatever I'd put in here that
would result in the reference actually being "=Worksheet2!A196"]"
The whole point is to be able to copy the cell down without having to go
in and enter the exact references manually.
Hope this makes sense...
Thanks!
Stefan
Excel 2003 question 
I am trying to use a cell reference to another sheet. My hope was to take the initial reference 
='Weekly Data'!C39
And change it to a relative reference 
='Weekly Data'!C$39
And just drag it down and have it change like
='Weekly Data'!C$39
='Weekly Data'!D$39
='Weekly Data'!E$39
='Weekly Data'!F$39
... unfortunately that does not seem to working. Can anyone provide some assistance with this?
Thanks!
I have the following worksheet
in cell A19  =A18
in cell A20  =A19
in cell A21  =A20
in cell A22  =A21
if I insert a new row in between A19 and A20, I get
in cell A19  =A18
in cell A20 
in cell A21  =A19
in cell A22  =A21
in cell A23  =A22
Previous Cell A20, which becomes A21 does not increment the cell reference to A20  it stays at A19. The other cells increment the cell reference as expected.
How do I get the reference to update?
TIA
Dave
Hello all
I am struggling to figure out what is likely a simple problem.
I have a workbook in which I keep track of daily numbers on one worksheet, however, I compile reports for those numbers in a separate worksheet which only needs to reference 1 day per week. I created a formula to that points at the first day I need to reference, and I would like to autofill the formula into the subsequent rows on the worksheet, but need to skip rows.
Here's an example of the formula I created in the worksheet titled "weekly report":
Cell C5: ='DailyNumbers!P5'
I need cell C6 on the weekly report worksheet to say ='DailyNumbers!P10', and then cell C7 to say ='DailyNumbers!P15' etc etc etc for many hundreds of rows. Of course, when I try to autofill the formula, however, the relative referencing just makes the cell reference 1 row higher each time.
Is there a way for me to force autofill to skip rows so I can quickly create all of the formulas I need, or am I looking at hours of formula typing to get it all plugged in?
Thanks in advance!
I have 2 worksheets. One named Daily, one named weekly. The A columns of both sheets have dates. Daily has every business day (including holidays) and weekly has every Friday(including holidays). The B columns contain volume that coorelates with the dates. What I would like to do is take the Average values between 2 Fridays and put them into the weekly worksheet. I had success with the first week because I was only searching on one criteria, but when I tried to search between dates, no luck. This is the formula that worked for the first cell:
{=AVERAGE(IF(Daily!$A$4:$A$604<=Weekly!A4,Daily!$B$4:$B$604))}
Here is the formula I attempted the DID NOT work in calculating the daily average of one weeks voume.
{=AVERAGE(IF(AND(Daily!$A$4:$A$604<=Weekly!A5,Daily!$A$4:$A$604>Weekly!A4),Daily!$B$4:$B$604))}
Any ideas? FYI, I want to skip the blank cells(holidays) in my calculations.
Thanks,
Gimi
I have a sheet with many charts on it that have to be updated weekly. I need to increment the ending cell reference in each chart every week.
Is there ANY way I can automate this or make it easier? It's about 25 charts and each one, I have to update 4 or 5 ranges.
Hi there!
I have been looking for a solution to the follwing issue for a while now:
I use an excel sheet with daily data and need to get a summary on a weekly basis. I am trying to find a way to copy the cells from one week to the other without having to retype anything. here's what the spreadsheet looks like:
(The daily data goes on for several months)
Daily Monday Tuesday Wedn. Thursday Friday Saturday Sunday 31/01 01/02 02/02 03/02 04/02 05/02 06/02
Client A 150
Client B 85
Client C 15 68
Weekly 31/0106/02 07/0213/02
Client A 150
Client B 85
Client C 83
for the date range in row 7 I used the following formula =TEXT(B2;"dd/mm")&""&TEXT(H2;"dd/mm") . As for the weekly sums a used a simple SUM function.
Anybody got a simple solution for this?
Thanks in advance
Hello.
I need to copy a chart to several different pages of an Excel presentation
spreadsheet, and would like for the data source cell references to increment
by 50 each time. I'm copying the chart 50 rows down each time, but the data
source references do not increment. When I change the data source references
from "$A$25" to "A25", they get changed back to the "$A$25" absolute
reference when I click Ok.
I expect that the chart objects aren't tied to specific cells on the
presentation spreadsheet  so Excel doesn't increment the references when
they get copied.
But ........... is there any easy to increment the data source cell
references when I copy so I don't have to manually change them on each new
copy of the chart?
Thanks in advance  Dave
Hello!
I have an issue with Excel for Mac and have had to admit defeat  despite it surely having a really simple solution..!
I have an Excel document with multiple sheets  two of which are for daily figures and weekly figures. The sheet for daily figures is filled in manually, with each row corresponding to a day. I need the weekly sheet to add up the daily figures automatically (in blocks of 7 days/rows).
The current formula I'm using looks like this in the first three rows:
=SUM(Daily!C1:C7)
=SUM(Daily!C8:C14)
=SUM(Daily!C15:C21)
But When I select all of these and try to autofill the +7 trend down the column it goes to:
=SUM(Daily!C4:C10)
Instead of:
=SUM(Daily!C22:C38)
Can anyone help?
Hello.
I need to copy a chart to several different pages of an Excel presentation
spreadsheet, and would like for the data source cell references to increment
by 50 each time. I'm copying the chart 50 rows down each time, but the data
source references do not increment. When I change the data source references
from "$A$25" to "A25", they get changed back to the "$A$25" absolute
reference when I click Ok.
I expect that the chart objects aren't tied to specific cells on the
presentation spreadsheet  so Excel doesn't increment the references when
they get copied.
But ........... is there any easy to increment the data source cell
references when I copy so I don't have to manually change them on each new
copy of the chart?
Thanks in advance  Dave
Hello!
I have a 200 000 row data set with daily data that I want to shrink down to a more manageable data set with weekly data.
It looks a little like this:
Date............Client.....id..Week...Orders
20110717.....se.....123.....28.....1
20110717.....se.....123.....28.....2
20110718.....se.....123.....29.....5
20110718.....se.....123.....29.....3
There are 4 different clients, and 300 different ids. The report will contain new ids every now and then, so I can't create anything too static.
I want the end result to be a something like this instead:
Client.....id..Week...Orders
...se.....123.....28.....3
...se.....123.....29.....8
A new and slimmer table where the 7 daily order values have been summed up into a weekly one.
Do you guys have any good ideas for that, or a link to a good thread?
Thanks,
Jon
Hi  I need to automate some Excel work with use of VBA but I have never done anything this complex before. Could you perhaps show me where to start?
Firstly, I need some kind of "count" function that I can increment. This will help me keep track of a quantitative process. E.g. an integer that increments each time I an IF statement is successful. I will also need to reset the count to 0 every now and then.
Secondly, I need to somehow refer to cell references. E.g. copy cell A5 and paste into [cell reference  1]. I will also need to increment this cell reference.
Thanks!
Hi,
I have no idea how to do this but i have a spreadsheet which all the relevant data is by columns. I have another page with vlookups that when i choose a reference say SWW/001 it completes a A4 form with all the data from the lookups.
What i want to do is to input a reference and then print the document automatically and repeat but with an increment of 1 i.e. SWW/001 then SWW/002 and so on.
I have 500 references to print i.e. SWW/001 SWW/500
Any simple idea's ? if the reference was a number i would just do a simple 001+1 and repeat.
Thanks
After my first post about this failed attract a single response, I'm going to try again but this time with some more information.
This is my spreadsheet.
******** ******************** ************************************************************************>
Microsoft Excel  Book1
___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F2
F3
F4
F5
F6
=
A
B
C
D
E
F
1
Include
Reference
Name
Value
Mailing*List
Custom*String
2
TRUE
10001
Customer*A
High
Weekly
Reference*="10001"*Name="Customer*A"*Value*="High"*Mailing*List"*Weekly"
3
TRUE
10002
Customer*B
Low
Weekly
Reference*="10002"*Name="Customer*B"*Value*="Low"*Mailing*List"*Weekly"
4
FALSE
10003
Customer*C
Low
Monthly
Reference*="10003"*Name="Customer*C"*Value*="Low"*Mailing*List"*Monthly"
5
FALSE
10004
Customer*D
High
Daily
Reference*="10004"*Name="Customer*D"*Value*="High"*Mailing*List"*Daily"
6
TRUE
10005
Customer*E
Low
Monthly
Reference*="10005"*Name="Customer*E"*Value*="Low"*Mailing*List"*Monthly"
Sheet1
*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Whe
Columns B & C are unique values
Columns A, D and E are locked to list validation
Column F is a concatenation of the table headers and rows
The formula in Column F is: =$B$1&" ="""&B2&""" "&$C$1&"="""&C2&""" "&$D$1&" ="""&D2&""" "&$E$1&""" "&E2&""""
Using VBA on a button, I would like to sort and then export just the data in Column F to a text file, as text however I need the data to be filtered in three ways.
Filter One: I only want the values from the row in column F when the row in column A is 'True'
Filter Two: I would like my two customer values in column D to reflect on my exported file.
Filter Three: When exported, the rows should be in ID order under the relevent heading,
When Exported, I would expect the file to show something along the lines of:
Text file export
High Value
Reference ="10001" Name="Customer A" Value ="High" Mailing List" Weekly"
Low Value
Reference ="10002" Name="Customer B" Value ="Low" Mailing List" Weekly"
Reference ="10005" Name="Customer E" Value ="Low" Mailing List" Monthly"
End of export
Note:
With 'Low Value' the references are in ID order
Rows four and five where missed out because they where set to false in column A
As before, I'm sure that this is possible however I haven't a clue where to start with the vba to do this so any help would be gratefully received.
Hi,
I am trying to make a macro cell reference move along with the spreadsheet as new rows are added. At this time if I add a new row and then run my macro the cell reference in one above my target. How can I make the cell reference move with the addition of new lines?
My current macro is:
sub Dateup1()
Range("b5").Activate
Range("B5") = 1 + Range("B5")
End Sub
A very simple maco to make my date increment by 1 when the macro is ran.
Thanks in advance!
I would like to know how I can reference a range of cells in a closed
workbook, A, that may be updated daily, to cells in an open workbook, B, that
would be opened weekly. Would the weekly workbook, B, be updated daily
(while closed) from the daily workbook, A, or would (can) it update only when
opened each week?
I am referencing a yearly calander of vacation days, A, to a weekly work
schedule, B.
I am running a weekly report for some data which is collected on a daily basis. Col A contains the date (every calendar day) and Col B has data corresponding to it.
Col E is the week for summry and col F is the summation of the data in col B. When I use Sum formula and stretch it on rows below, it does not sum the next seven rows. So now I am manually selecting the data for every week sum.
Is there a better and easier way to do this?
Thanks
Hello All,
I am new here and couldn't find what I am looking for and could be that I am not sure what it is called.
In col A, I have data (hundreds of numbers).
In col B, my formula. i.e =SUM(A7:A13) so sum of 7 cells.
Basically I am summing the data in col A in a group of 7 cells and repeat until the end of the list in col A.
How do I make the formula automatically increments the range references? I dragged the formula cell down to next rows but it doesn't increment corrently.
It should be like this.
=SUM(A7:A13)
=SUM(A14:A20)
=SUM(A21:A27)
=SUM(A28:A34)
and so on....
Thanks in advance for your help.
Paul
Hi there,
I am working on an excel workbook that contains a daily report and a weekly report of some numbers. The weekly report (obviously) gets its data from the daily report.
The problem I am having is that when I create the weekly report via copy and pasting the previous week's report, the addressing to the daily reports do not change.
To better illustrate (literally) my point, the attached is a picture of the addressing.
Simply put, the addressing should change automatically to use the 7 days (labelled in the format of M#, where # is the day number) of that particular week.
Is there a simple way to tell excel to simply use the 7 worksheets to the left of the worksheet in question (weekly report)?
THANKS
Hello. I am trying to use a formula or function that will allow me to increment the row number across columns. I am using multiple worksheets with a reference to a main worksheet.
So, for example, in cell B2, I have this:
=Mainsked!$B9&" "&Mainsked!$D9
Then, in cell C2, I want that to be Mainsked!$b10&" "&Mainsked!d10
And so on and so forth through cell h2.
I have this same procedure that I want to follow on multiple work sheets.
Each worksheet will start 7 rows down from where they did on the previous sheet. This is for a weekly work schedule.
Each sheet references the master schedule. The goal is that the manager can simply update the mastersked and then the weekly schedules will be updated and can be displayed in a readonly fashion on an intranet for the employees to see.
If anyone can offer any suggestions, I'm all ears.
Thanks much.
Cheryl
Hi there,
I have data showing me past data by week and this quarter I have the data on a daily basis.
is it possible to show the daily data & the weekly data on the same chart?
Rgds,
Shirley

