I have a worksheet with 13 tabs, each tab represents sales for each week of a given quarter, i.e. 13 weeks.
Each sheet breaks down sales by dept (20 Dept's) and compares This Week v Last Week.
Having completed Week 1's figures, what is the simplest way to copy Week 1's "This Week" into Week 2's "Last Week" Figures and so on for ultimately, all 13 weeks, without manually copying basic formula from one sheet to the next, 13 times?
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
I have a (required) time sheet on which I'm tracking comp time hours. There are 52 worksheets in the workbook for the year (of course), each of which contains daily and weekly hours.
On the summary page, I have the following columns:
2009 Carryover
2010 Wks Worked
Wks x Std. Hrs (Col 2 x 40)
Actual
"Diff (Comp/ OT)"
Comp time taken-2010
Comp time remaining
What I'd like to do is have the 2010 wks worked column (#2 in the list above) automatically add +1 each calendar week, but I can't figure out how to do it.
For example, as of 12:00 AM on Sun, Jan, 10th, the value in 2010 wks worked should be 1. On Jan 17th at 12:00 AM, the value should be 2, and so-on throughout the year.
Is there some script or formula that can be run that would tie in to the calendar date/time to accomplish this task? I'd also like to have a more elegant solution for calculating actual hours than adding each separate "weekly hours" cell from 52 spreadsheets - works fine.
I need a formula on Cell C3 on the attached Sheet1.
This should add numbers from the Actual columns as they are updated; i.e., as soon as I populate 'Actual' columns such as F, I, L, O, R... Cell C3 should add up the numbers automatically. This way I don't have to update the Cell C3 manually each week I populate the Acutal columns.
I currently am trying to refine some spreadsheets at work (hospital setting). The type of files im working with are medication sheets where on the left it states the medication and to the right of it, the cells have the days of the month(1-31) but I need them to change depending on the day they come into our facility. Above the numbers i would also like it to say the day of week with the first initial (M, T, W, T, F, S, S) in the cells are the top. It is something that we have to make for each day it it gets really annoying and is a waste of time moving the dates over for every day. find a way where I can open the file and the numbers and letters are all in the right place without having to change it for the day that the patients are coming in.
View 3 Replies View RelatedI believe it should be quite a simple thing...and probably has something to do with the OFFSET function...but I cannot seem to put my fingers to it.
All details are mentioned in the attached ZIP file...so I won't repeat myself here.
I need to use Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting-limit.htm" target="_blank">conditional formatting with more than 3 conditions. I have found a result for this when the formatting is being done to the cell containing the number but I need a different cell to be formatted. For example:
am pm
xx
xx
xx
xx
xx
xx
66
I need the cells marked by an x to go different colours depending on what number is in the final row of each column.
Is there a macro that automatically saves a backup of your spreadsheet every week?
View 3 Replies View Relatedi got a problem with date range.actually i wanna to insert date range automatically which is referring current week.
View 2 Replies View RelatedI am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
What im trying to do is have a macro that will open a file called Blue 1.xls in location "I:SchedulesBlue" and then copy data from cells N13:034. Then paste it into another excel file (Press.xls)on worksheet "Press Break" cell G14.
The data that is copied not always fills up the cells N13:O34 so I need it to go to the next blank cell in column N and paste the information from file Blue 2, and so on for Yellow 1, Yellow 2, YellNR, and Green.
So to summarise I need a macro to open 6 files copy data from the same location on each of the files(N13:O34) then close and paste it into a master document(Press.xls) worksheet "Press Break".
I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
Total Work per day
Total Travel per day
Daily....................
I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
I have to download a report every week and have been manually checking the report weekly for changes.
View 5 Replies View RelatedI have a column where I am convering the Date into a Fiscal week number.
For example 10/6/2009 is Work week 41
Now I want to show October Week 41
I need to add the month and the text "Week" before the week number. what is the formula I use.
I have a worksheet in which i have many sheets(around 150). Each sheet is allocated for a measurement device and has values at least once per day. What i want to do in another Excel worksheet is to take, automatically, the highest value and the medium from each separate measuring device. Now here is the catch: each device has at least one sheet for it`s values. The good thing is that a sheet will only contain measurements from one device.
View 3 Replies View RelatedI make hundreds of ads on a daily basis, and to automate the process if possible.
I have attached an example where I'm trying to make the following:
I have an ad and a link:
A1= Nice cars "London"
B2= www.com/"111"
I have a list of hundreds of cities (as the example above "London") and their page number ( as the example above "111"), and I want to be able to paste the cities&page number (in different rows) in a second sheet. Where the value of "city" and the "page number" would be placed in the ad and the pagelink in a new row in the first sheet.
In the Excel file that I have attached you can see in sheet 1&2 "exemple - 1A/1B" as a simple ad and pagelink, in sheet 3&4 you can see "example - 2A/2B" an example of how the output should look like.
example.xlsx
I have around 50,000 rows of data down each sheet.
These are made up of approx 4000 areas of data. (the areas of data are from columns A:N and between 2 and 30 rows deep)
Seperately I have a cells contain formula covering combinations of 2-30 cells deep.
I want to afix these formula to the 4000 areas.
Thus, if the first 10 rows of the sheet constituted Area 1 I would want to refer to the complimentary 10 row formula range and afix it in the adjoining column to the Area 1 (columnO). If the next range, Area 2, was 6 rows deep I would want to search for the 6 row formula range and afix that to Area 2 (columnO), and so on.
Manually, over 6 sheets, I would have to do around 24,000 copy/pastes and this isn't practical.
Does anyone know how it can be set up to run automatically?
I have information been imported from another spread sheet into cell C1. but it will only display if i put data in to cell A1.
BUT, when i want to put data in A2, i want C2 to gather to formula info automatically.. I could drag it all down the screen but i dont want #N/A to be all over the page. Is there anyway i can get the formulas in the cells, without the #N/A all over it.
What can i do?
I have a dynamic range that is 19 columns wide with the height defined by the last filled cell in Column B.
Columns A, O, P, Q, and R contain formulas that need to be autofilled every time a new record is added to the list (i.e. a new row is added to the range) - this seems like it should be so simple, but I can't seem to find out how to do it?
Some extra details in case they are needed -
- Column B will always contain data for every row that I need A, O, P, Q and R to have their respective formulas copied into
- I'd like the formulas to appear as soon as the data goes into Column B - regardless of what else is entered into the row
- Multiple identical records will sometimes be entered at the same time (i.e. by highlighting multiple rows and using Ctrl-Enter) - I need the formulas to autofill all of the affected rows.
I need to indicate if the figures for this week has gone up or down compared to last weeks figures.
So looking at the attached, I need to add a column (I5) to sheet Admitted Patients WE 20081005, so it compares the % treated in <18 wks figure by specialty to previous weeks figures on sheet Admitted Patients WE 20080921.
Ideally I would love an arrow either point up if the figures has increased or down if they have reduced.
I need to produce three different formats of accounts data monthly. I attached a file with each accounts description in Columns A, E and G. Is there a way to match the data with those accounts name in a faster way than manual linking?
I would like the last 6 figure in cell a1 to be highlighted cut and pasted into B1 - the amount to be cut will vary
View 4 Replies View RelatedI am trying to make an inventory cover page - that shows data of separate products on different sheets with the following;
Product
Date
Previous Total
Amount Taken
New Total
and occasionally there is a Re-issue of stock to the previous total.
This is at the moment an historical record as anyone can see the usage across the dates for any particular product by looking at the sheet. Each time there is a change to the inventory it is recorded on a new row. But what I am trying to do is to copy the last row of data from each sheet to the cover page to show an overall inventory.
The part where I get stuck is making the selection of the last entered row of data [from each sheet] automatically updating the coverpage, when each sheet will get new data added frequently.
I thought about somehow making a duplicate of the last row and locking it in place and linking that to the coverpage - but I still do not have a clue on how to get it to automatically select the last row of the data.
Dim figure from the cell b4,b5,c4,c5 to accumulate add total b11,b12,c11,c12, respectively.
View 5 Replies View RelatedHow to make space between the figures ex:11480831525 i want like: 11480 831525
because i want to do it for thousand of transactions in one time by using only one formula.
I have the following ...
.Offset(3, 0).Value = "For " & P & " numbers there are " & Format(tly, "###,###,##0") & " x different values of " & cmb & " numbers. "
... which includes figures upto and including millions.
The thing is that the above code ONLY produces figures that are relevent.
I need code automatically copy and paste column A to column B every very three seconds
View 8 Replies View Related