-->
|
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.
Thanks,
Perry
Similar Topics
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
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!
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.
Thank you!
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)
NOTE:
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.
ELSE
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
value_if_true, value_if_false
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.
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.
Hey Folks,
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...
Jack
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
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
Hey Everyone
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,
AR
Hi,
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:
=SUM(OFFSET(Daily!$D6,0,(COLUMN(C$2)-COLUMN($C$2))*7,1,7))
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
2005).
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,
Regards,
Gary T.
Hi Folks
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.
Hello All,
I have data laid out as follows:
Sheet2
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
1
Week 1
Week 2
2
1
2
3
4
5
6
7
1
2
3
4
5
6
7
3
Weekly Demand
0
0
0
0
0
0
100
0
0
0
0
0
0
100
4
Daily Demand
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.
Thanks,
Matty
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
Toby
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?
So my boss ask me to find the data for S&P 500 in different time frame and put it on separate sheets, he also want me to make a main page so that he can choose different time frame with a button, and the corresponding sheet will pop out. I have been trying to figure this out with nested IF but excel said my function exceeded the limit. So I decide to just using the array formula and show "daily" if he chooses "daily". Of course, I highlight the entire column but say for annual data, the length is much shorter and therefore there will be alot of #N/A s. Nobody know how to deal with that?I wanna leave it blank or get rid of it. I have copied my formula, any suggestions? (daily, weekly etc are the names for the data table)
={IF($B$1="daily",daily,IF($B$1="weekly",weekly,IF($B$1="monthly",monthly,IF($B$1="quarterly",quarterly,IF($B$1="annually",annually," ")))))}
you can see the snapshot on:
https://picasaweb.google.com/dennisl...28931035234978
I have a sheet with some data in it that. Each row has a date and some values for that particular date. I wanted to create a chart that would plot a summary of those values. I wanted a daily weekly and monthly chart. Someone on this forum suggested that i use a pivot table. While it seems to do some of what i am after here is the problems i have.
1. There is no way to have a weekly summary just daily and monthly. Is there any way to group the data weekly?
2. I need to plot the daily, weekly and monthly data on a chart and be able to view all 3 charts simultaneously. That however seems to be impossible to achieve with a pivot table. I tried creating multiple tables but the grouping seems to apply to all of them. So if i have daily grouping on one the other one will have it too, hence i can only have one chart at any one time. Am i missing something here? What workarounds are there?
Thanks!
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,
Chris
I am trying to create a formula for a budget.
example:
I have a daily budget of $20
I also have a weekly budget of $100
So If I spend more than $20 in a day I want the amount subtracted from the $100 weekly budget. But I don't want the remainder added to the weekly budget.
So if on Monday I spent $40, the weekly amount would show $80, On Tuesday I spent $10 the weekly would still show $80.
So the weekly amount never increases, it only decreases if the daily budget goes into negative numbers.
Hi Guys,
Please check out my sample.xls
This is a ongoing workbook in that data will be added daily and summed up weekly and monthly -
Is there a way to calculate this without adding more rows or columns? each calculation will be in a different worksheet but same workbook.
Any help/sample code would be wonderful
Thanks
Av
I have a Flow sheet, and adding daily and weekly total rows. Finished with the daily row, and now need to add weekly row. I use a 'counter' variable so i know how many rows above to add. (i have to do this several times - different groups).
For daily, I just used:
Selection.EntireRow.Insert
For Each cel In Selection
cel.FormulaR1C1 = "=SUM(R[-" & Counter & "]C:R[-2]C)"
Next
but for weekly, how do I tell it to merge every seven cells within the selection? Appreciate any help.
Thanks,
adam
I update daily series in a spreadsheet. I want to create a sheet presenting this data Monthly (sums) for the past year, weekly (sums) for the weeks since the end of last month and daily for the last few days (5-10). I want to create it so that the most recent data is picked automatically and the presntation sheet can just be printed after each daily update.
Hi,
With help from Paddy, I now understand how to use Offset to reference the last entry(s) in a column that updates daily. But I'm now having trouble using Offset to reference between sheets within a workbook. Below is the Offset formula Paddy supplied me with. It works if my data and the formula that references the data are both on the same sheet.
But what if my data were on sheet1 and my formula were on sheet2.
=OFFSET(INDIRECT("A"&MATCH(9.99999999999999E+307,Sheet1!A:A)-9),,,10)
Any help would be appreciated.
chaos
I'm new to the forum!! I am working on a spreadsheet for our timekeeper. I have the daily time functions done and everything seems to be working fine except for...I have to calculate the weekly time. I need to know the amount of time a person worked over or short for the week. Here is an example of my spreadsheet:
Thursday Friday Weekly Total
Time In 6:59 AM 7:11 AM
Time Out 5:39 PM 5:29 PM
Luch 0:30 0:30
Daily Total 10:10 9:48 #VALUE!
+\- Time 0:10 -0:12 #VAULE!
How to I get the weekly totals???
Thanks in advance!!
Carolyn
|
|
