Increment Cell Reference
(Alternate Topic Results)
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.
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.
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:
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.
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?
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!
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?
Hello. My Excel expert is away unexpectedly and my new boss needs something done right away (nice). I could really use some help! I hope this is clear.
Using Excel 2003. I would provide the 'real' workbook, but do not see how to attach a file in this posting. The problem is probably not that hard, but there is a lot of text here only to help make it clear.
The book has two sheets.
In the Salaries and Benefits sheet Columns A and B refer to the other sheet named 'GEA Sal Sch'
EX: from Salaries and Benefits
A5 ='GEA Sal Sch'!$B$77 (displays the value 10.0)
B5 ='GEA Sal Sch'!$E$77 (displays the value 68,887)
1) Salaries and Benefits! Column A always references Column B in GEA Sal Sch!, but the Row referenced will differ.
2) Each Row in Salaries and Benefits! Columns A and B always reference the Same Row in GEA Sal Sch!
***Here is the logic I need help incorporating:***
IF the value in Salaries and Benefits! column A = 10, then Salaries and Benefits! A and B remain unchanged - keep the same references
EX: Since A5 = 10.0, then Salaries and Benefits! Row 5: A5 will remain ='GEA Sal Sch'!$B$77
B5 ='GEA Sal Sch'!$E$77. No action taken.
IF the value in Salaries and Benefits! column A = 9.5, then Salaries and Benefits! A and B reference row increments by 1.
EX: Since A6 = 9.5, then Salaries and Benefits! A6 must increment one ROW from ='GEA Sal Sch'!$B$76 TO ='GEA Sal Sch'!$B$77.
AND Salaries and Benefits! B6 must increment one ROW from ='GEA Sal Sch'!$E$76 TO ='GEA Sal Sch'!$E$77.
Salaries and Benefits! A and B reference row increments by 2.
EX: Since A7 = 6.0, then Salaries and Benefits! A7 must increment one ROW from ='GEA Sal Sch'!$B$69 TO ='GEA Sal Sch'!$B$71.
AND Salaries and Benefits! B7 must increment one ROW from ='GEA Sal Sch'!$F$69 TO ='GEA Sal Sch'!$F$71.
I have programming experince, but no VBA and not much Excel. I know how to write OFFSET and INDEX functions, but I do not know if they are useful for what I need to do, nor do I know if an Excel IF statement can use functions in place of the
I am wide open to suggestions, but I want to avoid making the end-user copy/paste any function, etc. unless it is very simple for them. I am hoping to automate the logic and cell updates as much as possible.
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.
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.
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.
I am trying to compile data from a sheet which has information in different rows
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
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.
Long time lurker, first time poster. I've been able to find answers to most of my questions so far, but this one is a stumper for me and I'm hoping one of you can help.
How do I move rows of data on one sheet to another and preserve the formulas so that they pull data from the original sheet? I have a series of daily tabulations that feed a series of weekly tabulations, but I stupidly put them on one sheet. I'd like to move the weekly tabulations to a new sheet, but preserve the formulas so that the cells are filled from the tabulations on the original sheet.
Hopefully this isn't confusing...
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!!
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.
Parent Task: Child Task: Frequency: Owner:
Report: Defects: Daily: Mike
Issues: Weekly: Niall
Status: Monthly: Edel
Report: Risks: Daily: Noel
Budget: Weekly: Tom
Weekly = 4
Daily = 20
Monthly = 1
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))
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?
New to VBA, still learning, trying to pick it up as I go so I don't waste too time at work. My application function logic is strong, but I'm having translating that into effective VBA programming.
I need to create a named range in my macro that will select an array of data with an everchanging starting cell and ending cell. The columns will remain static but the rows will change de
I have a data range which contains a set of reference data that rarely changes and is combined with a weekly extract from a seperate source pasted below, which is different every week, a one line break of data in between.
Occasionally, the reference data range is increased by a row or two and the height of the weekly data is always different.
I would like to define the data range of the cells hightlight in grey in the attached picture. The starting poitn will always be in the row with the reference cell highlighted in yellow and the last cell will always be in the row of the last cell of data
First post! :D
I've got 2 workbooks with a linked cell reference for updating stock from a stock sheet to a combined catalog.
currently I'm simply using the formula [CODE] ='[PARTS 0206 2013 1400.xlsm]TPART'!$U$105 [CODE]
All i would like it to do is increment by one every time i drag down the cell. The same way excel does with worksheet references.
At the moment I'm inputing them by hand (Which is going to take rather a long time as there are nearly 9000 records...).
Any help would be massively appreciated!
EDIT: Sorry, just to clarify it is only the cell number that needs to increment in the example 105 would change to 106.
I have a series of dates in "Daily!" worksheet, cells C2:IV2, starting from
1-Jan-05 and running through till 11-Sep-05.
In cells A6:A62, I have various cost centres which can be allocated costs,
such as sales, rent, overheads, miscellaneous, etc.
So I therefore have a grid (C6:IV62) in which I can allocate costs against
the appropriate cost centre & date.
In "Weekly!" worksheet, I have the same list of Cost Centres again appearing
in cells A6:A62. However, instead of having daily dates in row 2, I have the
start of the week headings (i.e. in C2 is 02-Jan-05, in cell D2 is 09-Jan-05,
in cell E2 is 16-Jan-05).
In cell C6, I have the following formula:
which I can copy across and down and will pick up the weekly totals for each
cost centre (note it starts at "Daily!$D6 as this is the first Sunday in
However, I also have a "Monthly!" worksheet, which is set up in exactly the
same way, and in which I want to put a formula in C6 which I can copy across
and down to pick up the monthly totals for each cost centre. However, I
can't think how to do it as the number of days in each month is not constant
(because the number of days per week is always 7, the Cols and [width]
parameters in the OFFSET function above were relatively easy to write).
Any help greatly appreciated,
First time poster after hours of searching, be gentle please!
I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).
I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this?
Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used? Many many thanks in advance,
developed an excel spreadsheet over the last 3 years which we now use at work to generate and print weekly and daily rosters for our 52 drivers. It consists of a static data sheet whch never changes 1 weekly roster sheet and seven daily sheets. The data from the static sheet is loaded into an array which after date selection moves the data round and produces the names in the correct places for the current weekly roster. Then another routine prepares the daily sheets from the information on the weekly sheet. This works well however we then have to manually remove drivers who are sick or on leave from the daily sheets. Can anyone point me in the right direction as to a way of typing in a daily sick list and leave list which it can load up after the daily sheets are produced and highlight the drivers not in work? been baffled by this for a while.
I have data laid out as follows:
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
What Formula can I enter in C4 and copy across to the right, which will divide the total Weekly Demand by the number of Days in that week, populating each Cell with this figure?
Obviously, the key here is that when you move from one week to the next, the division is based exclusively on that week's total Weekly Demand.
Hope someone can help.
I am trying to make a spreadsheet to keep track of my daily food intake. I am basing this on the weight watchers point system. I get a daily number of points i can use and a weekly amount of "bonus" points i can use. If I go over my daily points it comes off of my weekly bonus point totals. how do i make a daily point total column and subtract my weekly bonus points as a running total for lack of better terminology.
Here is what i have so far
Daily Allowed Daily Used Daily Remaining Weekly Remaining Weekly Bonus Points Over
44 0 44 35 35 0
These columns start in "g". Column c is my daily point totals. I can only come up with a running total for daily use by then my daily used column is off. I hope this makes sense.
Thanks for any help anyone can give
Not sure if this should be posted here but trying!
I am new to arrays and found something to convert my daily data to weekly but now can't find it. I want to create a new series where it looks at my daily dates and gives me the date of every Friday(or another day I know I can change by using a different number).
I can then vlookup to match the data to the new date.
can anyone help out?
I want to reference a cell using some math:
$B(1+1) which I would hope equals $B2 and the cell would then contain a reference to $B2.
How does one perform math funtions to the row part of a reference?
I what to be able to reference a cell that contains a cell reference
So Cell A1 would contain the text B12
cell A2 would reference to cell A1 and show the contents of B12.
and in cell A3, I want to show the contents of B13... but I want to take the contents of cell A1 and increment it from B12 to B13...
How do I do that? I tried simple math like $B(1+1) which does not equal $B2.
Thanks for a newbie question,