Copy Individual Hours And Put Onto Daily Record Sheet?
Aug 18, 2012
I have the day of the week in B1 Emp Nbr in B2 Hours worked on B11
What I need a macro to do is match B1(day of the week) with the corresponding offset column in F1-R1, Pos and Post the Emp NBR(b2) and Hours(b12) in the corresponding column (F4 and G4)
I then will key in a new Emp and the hours and click the macro to execute the move over to the correct column and down one row.
Do this until I post Emp 999 at which time it will exit the macroOzgrid weekly hours.xlsx
The next day I will change the day in B1 to 2, corresponding to TUE, and it will move over to the correct column (H4 and I4) and post the daily data until I key in EMP 999
Then continue for the rest of the week.
View 3 Replies
ADVERTISEMENT
May 26, 2009
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
View 6 Replies
View Related
May 29, 2014
I have a spreadsheet that is being updated every month with new project information. The spreadsheet is a log of all the hours worked by employees on each project. Due to many employees working on the same projects and each weekly log per engineer having an individual row to display these hours, there are many duplicated cells.
I am wondering if there is a way for me to display in another table, just a list of the projects that exist.
For example, the original table may look something like this:
WEEK MONTH PROJECT HOURS
1 Jan A 5
1 Jan B 4
1 Jan A 7
5 Feb C 5
5 Feb C 3
5 Feb B 8
I would like to display a table that looks like:
PROJECTS
A
B
C
In March there could be a project D and I would like that to be automatically added on to the second table, if possible?
View 6 Replies
View Related
May 23, 2006
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.
Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.
I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?
Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?
attached is book.xls and sales summary.xls for reference.
View 3 Replies
View Related
Jun 3, 2013
I have a 5 major categories of products e.g. Ketchup, Hot sauce ,mayonnaise , pizza sauce etc.
Now each of these individual categories are further divided into number of of subcategories based upon the the sizes and brands.
I want to create a sheet (One sheet) where I have to record production of each product on daily basis.
Like for example if I am producing ketchup, I select ketchup from drop-down list,Now in next column I have to select production size from predefined sizes i.e subcategories. But it should also be from drop-down menu and it should only show me the subcategories of my selected product like ketchup .I think this has something to do with data validation but I am not sure how this model will work.
View 3 Replies
View Related
Jan 11, 2010
I have SQL data that I'm extracting and populating two sheets accordingly on daily basis. (This is done manually and sheets are refreshed with new data)
Name of tabs: (Peaked & Confirmed)
Each sheet consist obviously of different data but table extract contains the same name & number of columns.
Name & Number of Columns :Org_province
Port_Of_Load
Port_Of_Discharge
BK_Client_Name
FF_BK_Name
VesselVoyage
GrossWeight
Export_Closing_Date_Yard................
View 4 Replies
View Related
Nov 3, 2009
a macro or code to copy row B10 To another sheet as a record but paste it down a row as i keep overwriting previous paste, tried offset etc but still stuck. I HAVE THIS SAME PROBLEM ON 2 SPREADSHEETS NOW. All i need it to clone the top row everytime data is put there, but where the data goes to paste it down a row so creating a record. Also i need another macro to search the G10 cell of the same row for the word replace and if so copy that row to another sheet as well.
View 9 Replies
View Related
Dec 24, 2011
I have a spreadsheet to record profit or loss on a daily basis. The figure for each day can therefore be positive, negative or zero.
I want to add a column to display the total for the last 7 days (NOT the last 7 calendar days), in which either a profit or a loss was recorded (so excluding any cell that is zero).
I would prefer to add (insert), the column for each day as it comes and the range would obviously vary if the new day's figure was not zero.
View 9 Replies
View Related
Feb 13, 2014
calculate overtime hours from daily time entries.
Normal hours are 7.6 per day
Time 1/2 is hours over 7.6 but no more than 2 hours
Double Time is all hours over that.
I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.
WedThurFriSatSunMonTuesTotal Normal HoursTime 1/2Double Time 10101068
View 5 Replies
View Related
Sep 14, 2006
I'm creating an log to track the total number of hours an employee works in one day and calculate regular and overtime hours worked based on the following criteria: overtime will be >8 hours in one day and >40 hours in one week.
Right now I have it 'mostly' figured out, but under certain conditions, it calculates more than 8 hours in one day for straight time. Is there a way to set a maximum value for the straight time cell and have the difference be added to the overtime cell?
View 6 Replies
View Related
Aug 20, 2014
I need to expand on this formula.
I require a formula that will break down daily hours worked into rate categories eg Normal Time, Time & Half, Double Time.
eg. Column E = Total time worked
Column F = Normal time
Column G = Time & Half
Column H = Double Time
What I would like to do is enter hours into Column A and a formula in Column B will split of hours to a maximum amount of 7.6hrs then the remainder of the hours be placed in Column C to a maximum of 2 hours and Column D, no maximum.
These are the formula I am currently using
column B
=MIN(E2*1,7.6)
column C
=MAX(MIN(E2-7.6,2),0)
column D
=MAX(E2-9.6,0)
I would now like to be able to split the hours over 3 rows x 3 columns
Example 1
Row A B C D
1 3 3 0 0
2 5 4.6 .4 0
3 2 0 1.6 .4
Example 2
Row A B C D
1 6 6 0 0
2 21.6 0.4 0
3 0 0 0
The total hours in Column B cannot exceed 7.6 hrs
The total hours in Column C cannot exceed 2 hrs
There is not maximum for hours in Column D
View 2 Replies
View Related
Jul 4, 2006
I need to acrue hours worked for each individual at the bottom of the page, we have 4 security sites covered with 6 personel, but all are working either of the 4 sites.
Figures in red indicate the hours that are covered for that day. ie. two names in the day share the hours, so for SAT/SUN each would accrue 12 hours each. I would like to be able to mix and match the names to any given site, but give me a total hours worked at the bottom of the page next to there initials, can anyone tell me which formula to look for and advise accordingly. I need to cover all cells from
B4:H4 B5:H5 B7:H7 B8:H8 B10:H10 B11:H11 B13:H13 B14:H13
View 3 Replies
View Related
Jul 25, 2014
I have created a table to record the daily hours worked by staff, however ,since the data I'm dealing with is quite large (over 300,000 rows), I need a function/formula to make populating the table quicker and less daunting.
View 2 Replies
View Related
Mar 18, 2014
I have a list of times in a column. I'm looking for a macro that will go down the column and record all the times that lay between 5 and 7am, 1 and 3pm (13:00-15:00) and 8 and 10pm (20:00-22:00)
How to write it?
View 1 Replies
View Related
Feb 6, 2008
I have a master spreadsheet that list several columns about employees(name, date, event name, etc.). Then I have an indivdual sheet for each employee. I am trying to import the column information for each employee onto their individual sheet based on their name. It is possible to import the individuals data from the master sheet to the individual sheet based on the employee name?
View 9 Replies
View Related
May 18, 2009
I have a query regarding making a Excel Sheet for Daily sales.
here I go,
Well i want to make an Excel Sheet where in I just need to enter the Date, Invoice Number , Product , No of Product and rest it should calculate the VAT (Rounding Off) amount N den the Grand Total..
M givin you an example in the Below Sheet.
View 9 Replies
View Related
Apr 23, 2012
I have a spreadsheet with multiple columns, column N contains different countries (some are blank). I want to copy all data in that row (except columnt H) to a new sheet if N = USA.
Macros are disabled so a formula(s) is needed.
View 5 Replies
View Related
Jun 24, 2014
I am trying to create a macro that would copy three cell values - Date, Amount, Rate (from Sheet1) and paste them into Sheet2 as values.
In addition, this macro will be run for each business day's file, which only has the 3 values only as of that date; I want Sheet2 to be updated on a daily basis with historical data from past days (when the macro was run) and pasted as values.
For example, if i run the macro today, it should copy Date, Amount & Rate from Sheet1 ('Data' tab in sample.xls) and paste them as values in the next row after yesterday's data in Sheet2 ('Historic data' tab in sample.xls).
Sample attached; what is the best way to do this?
View 3 Replies
View Related
Mar 20, 2014
I have the following scenario:
Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.
So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.
I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.
View 4 Replies
View Related
Jul 15, 2014
I am trying to build a sales sheet that will keep all the records during the month but every day it refreshes to a blank sheet, then allowing users to flick back to a previous day and all the data from then is still saved?
I think this is possible in excel as I am sure I have seen a working sheet before but I don't know where to start or even what the process would be called in excel?
View 1 Replies
View Related
Jan 13, 2014
I am making a sheet to track the daily cycles of a machine. I need to get the information from the "meter reading" column to populate in the " cycles recorded" cell. I'm very new to this and need a formula to use to track the changes in column D on the attached sheet that will populate changes in cell D4.
Cycles counts.xlsx
View 4 Replies
View Related
Apr 7, 2014
My current project involves sorting a spreadsheet, selecting specific qualifying data from the sheet, storing it into a variable, the pasting the individual values from the list into individual cells on a different sheet within the same workbook. Here is the code I have so far...
Code:
Sub Test()
'' freeze screen updating to remain on main worksheet
Application.ScreenUpdating = False
'' move to Avaliable worksheet
[Code] ..
When this code runs the first item in the list pastes into Sheet10 A1, but no other values from the list are placed into Sheet10. Previous to this I had been using a variation of this code to push the list into a combobox list within a userform. In that case instead of
Code:
ThisWorkbook.Worksheets("Sheet10").Range("A1") = x
I had
Code:
Combobox1.list = x
And this worked perfectly; creating a list within the combobox (in fact I can use this modification to interrogate my code to determine if the list is being properly generated, and it is).
View 2 Replies
View Related
May 22, 2007
I have a workbook which consists of difference worksheets that downloaded from the system, e.g Jac01, JKen01 and so on. the worksheets' name might varies and from month to month basis. Therefore, just wondering is that a fast way to save the individual sheet as a separate workbooks. The rational of saving the worksheets into individual workbook is mainly for the individual division manager to study their respective product sales.
View 2 Replies
View Related
Jul 19, 2012
I have a multiple sheet document, on the first sheet I have my daily data:
Name
ID#
Ward
TTO
Transport
Transport 2
Date
Time In
Time Out
Time Stayed
ex1
xxxxx
12
xx
Taxi
01/01/12
xx:xx
xx:xx
xx:xx
[Code] ........
This is updated daily and it shows patients that have been through the department, who, when and how long. This is then converted on sheet 2 and shows as:
Date
Number of Patients
01/01/12
2
02/01/12
1
03/01/12
1
My third sheet has a very similar layout to sheet 2 but is weekly, previously i have used the following to find a weekly total:
=SUM('sheet2'!B563:B567)
I now have well over 4000 rows and altering this sum to accommodate for each week is time consuming as sheet 2+ are updated to cover the following 6 months.
Is there a better formula or a way to use the current formula and drag it down to auto fill? Currently trying to auto fill does the following:
=SUM('DAILY ANALYSIS'!B563:B567)
=SUM('DAILY ANALYSIS'!B564:B568)
=SUM('DAILY ANALYSIS'!B565:B569)
I want something like:
=SUM('DAILY ANALYSIS'!B563:B567)
=SUM('DAILY ANALYSIS'!B570:B574)
=SUM('DAILY ANALYSIS'!B577:B581)
View 2 Replies
View Related
Jun 10, 2008
i have facing a big problem nowadays.problem is that, i have to regularly update manually(copy & paste) "oil filling", " stock" & "meter reading", coming from every day by the supervisors of our company for verious sites spreading accross the our state, nearly 1305 site. i have attached the master file(which should be updated) with the reports coming from the supervisors(Rosan & Jhon) in another sheets. the master file is same form as i given. is their any way of automatic update by any macro.
View 2 Replies
View Related
May 27, 2008
Have a workbook with approximately 25 worksheets in it.
I want to copy/paste/value each worksheet and save it as it's own workbook.
My code doesn't work, it hangs up on ws.copy every single time, on Excel 2007 and prior versions.
Sub SaveWS()
Dim wb As Workbook
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Copy
Set wb = ActiveWorkbook
wb.SaveAs "t:dir1expenses" & ws.Name & ".xls"
wb.Close False
Next ws
End Sub
View 9 Replies
View Related
Dec 3, 2007
I have a workbook with a large number of worksheets in it. I intend putting the workbook onto a shared network drive so that a number of users can go into their individual named sheet - check some data and confirm that they have checked it.
My initial idea is to attach a command button to each individual sheet so that each user can click on the button to confirm that they have checked their data. What I would like to do is hide a worksheet within the workbook and when each user has checked their data - their button click will insert a tick in the relevant cell of the hidden worksheet e.g.
When User 1 clicks command button on User 1 sheet it will insert a tick in cell B2 of the hidden sheet
When User 2 clicks command button on User 2 sheet it will insert a tick in cell B3 of the hidden sheet
View 9 Replies
View Related
Jan 9, 2010
Is there anyway I can make it so that each individual sheet in my workbook has a different password?
View 9 Replies
View Related
Sep 30, 2013
So, I'm trying to create a database, of sorts. I run several reports every morning that leave me with the information I need, but I would like to be able to store them in a master sheet, thus creating a database. That master sheet will be used by my co-workers and, at times, my customers or boss. Add in a few hundred new lines every day and it becomes a task in itself just to keep the spreadsheet up to date, manually. I'm only needing to do this now because the system I use now will be shut down starting next Monday. I (someone who has never had any formal excel training) have been tasked with creating a new way to track orders.
I can create a unique identifier tag for each line, but I don't know how to program or create a macro/equation to be able to filter and replace values in the tag's row, based solely on the tag. The combination of columns A&B in my test report will give me the unique tags. From there, I need columns E-L to be able to be updated from the new reports each morning.
If Sheet 2 contains a list of items that I have updated during the day and wish to place into the master sheet (Sheet1). I don't know the language needed to write the macro and any necessary equations.
My goal is to have my reports dump information into a master sheet, but only replacing/adding onto existing rows and columns that correspond with each other and creating new lines when no pair is found.
I've attached what my report should look like after I've filtered out the unneeded information. It will be this, every day, except with a few hundred more lines.
Imagine Sheet 1 is the master sheet and sheet 2 is the newly uploaded information.
TEST1.xlsx
View 5 Replies
View Related
Feb 15, 2013
One of my job responsibility the following up our store stock, usually we consume constant daily value form our stock, as example we used daily 200 mt of corn, 150 mt do soya bean meal and so on, I would like to creat excel sheet to deduct this value from the previous column daily automatically.
View 1 Replies
View Related
