Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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


View Answers     

Similar Excel Tutorials

Get the Row or Column Number of a Cell in Excel
How to get the row or column number of the current cell or any other cell in Excel. This tutorial covers important ...
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 ...
MOD Function in Excel
The MOD function is very simple but it can be used to do wonderful things in Excel. It returns the remainder after ...
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 ...

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.

HELP!!!

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



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!




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.




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


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




Hi,

Could anyone help me please ? I've tried lots of ways to do this, but none successful so far.

I've got 2 worksheets in the same workbook.

Sheet 1 contains huge amounts of data - thousands of rows and multiple columns

Sheet 2 - I want to extract the data from sheet 1 column A into sheet 2 column A but only the data from every 21st row.

I want to be able to copy the formula automatically down, otherwise it will take hours to do it manually. So far I can only get the cell reference to increment by 1 each time after copying.

So what I'm trying to achieve is :-

Sheet 2 A1 = Sheet 1 A1
Sheet 2 A2 = Sheet 1 A22
Sheet 2 A3 = Sheet 1 A43

If anyone can help me out with this, I'd be most grateful.

Thanks,

M.

Is it possible to do the following
I have four columns, and how ever many rows.

Three of those columns reference data from other sheets, there is only one column that does not. The column that does not, is the daily hours that are put in there on a daily basis. The other three columns reference a master sheet. When I sort the master sheet everything moves appropriately on the sheet that references the master sheet.

So now I sort the master sheet everything on the other sheets move perfectly, is there any way to make that column that does not reference the master sheet move with the appropriate row. In other words can you pretty much group each of the tables rows so when I sort the master sheet the column that doesn't reference the master sheet stay locked with the appropriate row.


All,
I have weekly data on one tab, and daily on the other. On my weekly tab, I have a basic sum formula setup to grab the corresponding seven days. My challenge is how to create a sum formula on my weekly tab that I can drag down the other 51 rows.

Week 1 =sum(e3:39)
Day 1 Week 2 =sum(e10:e16)
Day 2 Week 3

Day 3 Week 4

Day 4 Week 5

Day 5 Week 6

Day 6 Week 7

Day 7 Week 8

Day 8 Week 9

Day 9 Week 10

Day 10 Week 11

Day 11 Week 12

Day 12 Week 13

Day 13 Week 14

Day 14 Week 15

Day 15 Week 16

Day 16

Thanks,
Chad


Problem Statement:
I am trying to compile data from a sheet which has information in different rows


Example.
In the 1st ROW I would like to have data from C4, C6, C10, C14, E4, G4
In the 2nd ROW I would like to have data from C16, C18, C22, C26, E16, G16

Question.
How do I increment or decrement the common difference in the num of row (here it is 12) so that I do not have to manually reference the cells.


Hi,

I have a spreadsheet which contains tasks and subtasks. A parent task can have 1 or more subtasks. Each task has to be completed several times a week, but only the parent task is reported on. I have a piece of code that extrapolates out the tasks based on their frequency and pastes the data to a new sheet. However, what I need to do is only paste the data where the parent task has not already been recorded.

In normal code I would try to create a temporary table as i traverse down through the list, filling it with the parent task names. With each row I would check to see if the parent task existed in that list, if not I would add it to the list and paste the line into the new sheet, if it did then I would skip the row.

Can someone tell me how this might be possible in VBA?

The scenario and code I have is below.

SHEET 1:
Parent Task: Child Task: Frequency: Owner:
Report: Defects: Daily: Mike
Issues: Weekly: Niall
Status: Monthly: Edel
Report: Risks: Daily: Noel
Budget: Weekly: Tom

SHEET 2:
Weekly = 4
Daily = 20
Monthly = 1

SHEET 3:
I now want the Daily task to be repeated 20 times, the 2 Weekly tasks 4 times each and the Monthly task only once. The order is not important, but in the case of Reports, this should only be repeated 20 times (as its Daily) even though it has 2 sub tasks.

The code so far is:

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim cell As Range
Dim freq As Range
Dim cr As Range
Dim rep As Long
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")
Set freq = sh1.Range("B2", sh1.Range("B2").End(xlDown))
For Each cell In freq
rep = sh2.Range("A:A").Find(cell.Value).Offset(0, 1).Value
Set cr = sh3.Range("A" & sh3.Rows.Count).End(xlUp).Offset(1, 0)
cell.EntireRow.copy sh3.Range(cr, cr.Offset(rep - 1, 0))

Next cell


I need to align multiple time series that have different date ranges and frequencies so I can analyze and compare. I am new to coding in excel and was trying to code a macro that would insert rows and line them up.

I've attached a workbook with a small subset. I think it would be best to get them all into a weekly series..most are 28days or 7day with one that is daily.

I thought about getting them all in daily then going to weekly for analysis but might just be easier to convert all to weekly. One problem is that some of the weekly time series are weekly on a Mon, tues or Friday...they are not consistent.

I've searched this site but nothing really covered this and would appreciate any help at all!! thanks in advance!!


Don't mean to double post.
I've tried to understand Pivot Table's and working with them. Just not working for me. I'm looking for example files that I can download. If you know of any, please post a link. I am having the hardest time with this.

Here is what I have.

Tues. Wed. Thurs. Fri. Sat. Sun. Mon. Average
----------------------------------------------------------
GC-653 : Daily Total Weekly Average
GC-608 : Daily Total Weekly Average
CHOPS : Daily Total Weekly Average
Caesar : Daily Total Weekly Average
Constitution : Daily Total Weekly Average
Seajack : Daily Total Weekly Average






Hello Forum Members,

I have to worksheet "Sales Data" and "Sales Summary" in my workbook. I put daily sales update in sales data sheet which has a column Salesman No. and Total Sales with other columns also. I am updating the Sales Summary sheet through a sub-routine to calculate daily sales of each salesman as I get only total sales figure for each salesman everyday.

In Sales Summary sheet from column E, cell E1 contains date when I update the sheet and calculated daily sales is populated for each salesman in column E from E2 to the last non empty cell in column E. Next day the daily sales will be calculated in column F and date will be place in cell F1. This process goes on and all the columns grow with the date and the daily sales data for each salesman.

What I want is to include a code either separately or within my code to calculate weekly total of sales for each salesman and populate a column with column header "Weekly Total" and the weekly total of sales for each salesman in that column and once its done, the normal calculation should start again to calculate daily sales with the help of my sub-routine and once another 7 days are passed, the column Weekly Total should appear again with weekly total of sales for that 7 days. Is is feasible with VBA?

If I had dates in rows rather than columns it could have been done with excel built in sub-total function itself. But the dates are placed in columns, so how can I calculate the weekly subtotal in columns.

I am exhausted to find out a solution to this. It will be great if you please tell me how can I do that?

Thanks.