Email:      Pass:    Pass?


Advertisements


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.

Thanks,
Perry



Similar Excel Video Tutorials

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
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
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
- Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allows yo

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!




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

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





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!

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




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


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


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


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 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 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
2011-07-17.....se.....123.....28.....1
2011-07-17.....se.....123.....28.....2
2011-07-18.....se.....123.....29.....5
2011-07-18.....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


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!


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.




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.

KR,
Jafs

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


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.