Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Increment Cell Reference

Forum Register
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.


View Answers     

Similar Excel Tutorials

Increment a Value Every X Number of Rows in Excel
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...
OFFSET Function in Excel
The OFFSET function in Excel returns a cell or range reference that is a specified number of rows and columns away ...

Helpful Excel Macros

Sort Data With Headers in Descending Order in Excel
- This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1 - o
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru

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.


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:


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!

First post. Hope you can help as kindly as the community always does!

Im trying to change some data from a daily basis to a weekly basis , but I realized that the amount of data is so big that doing it mannualy is going to take forever. I attach here an example of how i am doing it right now. As you can see the in the Weekly Sheet, the Sum(B1:B5) and the Sum(B6:B10) and so forth represent the addition of the week BEFORE so the green highlighted days are Mondays that are NOT included in the sum.
Is there any way i can automate this process? It is very time consuming. I want the sum to add all the value of the past week (M-F) and show it in a cell that represents the date as monday of the following week.To make it clear:

Date Daily Value / Weekly Transformation
Mo1 2 / Mo2 2+3+4+4+7
Tu1 3 / Mo3 9+5+2+3+4
We1 4
Th1 4
Fr1 7
Mo2 9
Tu2 5
We2 2
Th2 3
Fr2 4
Mo3 1

Any ideas? I attach the spreadsheet if I did not make my point clear, its pretty hard for me to explain it.
Thanks in advance guys!
Sample Daily to Weekly.xlsx


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



I have two workbooks, one containing weekly schedules, and the other containing daily schedules. We will be populating the weekly schedules (indicated by the bordered cells in the weekly worksheet) manually, and want the data to be automatically linked to the daily schedules from the weekly schedules.

As per the examples, the bold cells are generated by the name of the worksheet. We will be CTRL-dragging existing worksheets to create the new weekly and daily schedules. Each daily worksheet will need to know which weekly worksheet to get its data from, and from which section of the weekly worksheet.

Any help appreciated!

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?


Hi guys

I have an excel workbook which collects daily hours from several worksheets and totals them into every 8th column for a weekly total

I then need to take these weekly totals into another worksheet containing only weekly totals

At the moment I am creating a link to several sheets to collate this data into my weekly summary sheet as I need the data from every 8th column (ie the weekly totals only)

I have looked at OFFSET but cannot get this to work although I am sure it should

Can anyone help me out please?

Thanks in advance!!

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:

Here is the formula I attempted the DID NOT work in calculating the daily average of one weeks voume.

Any ideas? FYI, I want to skip the blank cells(holidays) in my calculations.



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/01-06/02 07/02-13/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


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


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

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.



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.



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") = 1 + Range("B5")
End Sub

A very simple maco to make my date increment by 1 when the macro is ran.

Thanks in advance!

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

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

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.

Hello guys, I have a sheet where I have some daily data and certain tasks with the daily data (Task 1, 2 etc.) Is there someway I can calculate this daily data by matching the task and dates into weekly data? Attached a sample file to make it easier see sheet2 on how I want it.

Tried using match formula but only got it to add all data on certain date and not seperatly by task.


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.
and so on....

Thanks in advance for your help.

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.

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 read-only fashion on an intranet for the employees to see.
If anyone can offer any suggestions, I'm all ears.
Thanks much.

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?


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


This is my 1st post so forgive me for going on!

I have written some VBA code to bring in a text file that is ran daily/weekly and have filtered the data into columns/rows as I want it to lok but cannot work out how to move a block of data in cells A5 to I5 across to B5 to J5 and then repeat this function going down the rows until there is not more data, the amount of rows are not the same every time. I have tried to cut & paste it but keep getting an error and it says to do it individually (maybe the filter setting?)

Any help would be great!

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?