I have a productivity report that is posted on an intranet. I only want the members of my deptartment to see the productivity results. To accomplish this, I need help creating a VBA so the names in column B will only populate if an employee enters his/her 6 digit employee ID correctly in cell B1. If the ID entered is not listed in column B of the "Rep ID Master" sheet, then an error message should pop up stating something similiar to, "Incorrect ID. Please Enter Your 6 Digit Employee ID". Also, Since I don't want the entire company to see our dept's productivity numbers, I want the VBA to prevent the associate from saving changes to the sheet, but still allow me to do so--preferably via a password.
How to do a ranking for productivity based on goals in six categories for my employees. I want to be able to rank them on these key categories and rank them based on the best average. For instance:
Employee #001 Ranked 1st in Sales per Hour, 3rd in Avg Transaction Amount, 2nd in Multiple Sales, 5th in Accessory Percentage Employee #002 Ranked 2nd in Sales per Hour, 1st in Avg Transaction Amount, 1st in Multiple Sales, 4th in Accessory Percentage
I want to factor against the goals to determine each individual rank for each category and then rank based on the combination of them all.
I haven't gotten to all the additional conditional formatting but recall how to do this with ease, but the sheet in its basic format is present in the attachment below.
we've been using this spreadsheet as a timesheet since May of 2002. Recently in the past couple of days when we try to update an employee's time and date of work done by clicking on an update button on the first Worksheet, an error (Runtime Error '1004' Method 'Range' of object '_Global' failed) pops up.
Clicking the 'Debug' button opens a window up with this information:
I developed a 14-day work schedule and I assigned each different job position a number. The different job positions are numbered 1-6 and are as follows: #1=5:30am-1:30pm, #2=6:00am-2:00pm, #3=9:30am-1:30pm, #4=12:00pm-8:00pm, #5=1:00pm-8:00pm & #6=6:00pm-8:00pm. Numbers 1,2,4,5 clock-out for a 30 minute lunch break, while the other numbers do not.
My goal is to insert the numbers 1-6 into the spreadsheet throughout the 14 days for each employee, and have Excel calculate the total number of hours for each employee in the far right column. I would also like "V" & "H" to equal 7.5 hours. This would save a lot of time instead of going through and adding up the hours with a calculator
I have a payroll spreadsheet that is going to be placed on our network server. My boss wants me to have this spreadsheet to be protected so that one person can get in, add hours and commission, and save it up on the server. Then another person can go in to that spreadsheet, open it up to get the info and use it to cut the checks. What my problem is that the person who is making the changes does not need to see the pay rate or the total dollar amount columns, but the person who gets the data out does. Neither one of these people really know how to hide and unhide columns in Excel, so I need it to have when one password opens it, the pay rate and total pay columns are hidden, but the other password will show those columns.
I would like to be able to password protect each sheet in a workbook and still be able to use the macros/form control (button). Below is the code that I'm using and the code that I've attempted to add to it to protect it.
Current Code:
[Code] ....
Code added for protection but not working:
[Code] ....
I've added this code to both the beginning and the end of my code; however, I'm getting a "400" error when I try to use the form control (button) when the sheet is protected. Is there something that I'm doing incorrectly?
Is it possible to have multiple passwords for a column based on text entered. I was hoping to use it as a form of digital signature. Eg if I wanted to enter my initals KP I would have to enter password assigned to KP, if a staff member entered their initials "AB" they would have to enter password assigned to that.
I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.
I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.
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?
I am trying to create a spreadsheet for an online gift registry. They require that the spreadsheet have the photo's url's in a column. I already have the spreadsheet filled with my data. In the spreadsheet, Column D is filled with unique numbers, some with parenthesis, (ex. 52011, 52011(2), 34132, etc.)
I also have a folder full of images that are similarly formatted as such "...imagesrand_name_52011.jpg". (They will be moved eventually to a webserver.) Each number in the column may or may not have a corresponding image. And the images may or may not have a corresponding number in the spreadsheet. Is there a way to generate a url automatically in a column that corresponds to the image with the matching number? And if it doesn't, just leave it blank?
i am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid)
example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account. If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1.
If D:D is "payment pending" do same as above but in row 2 and so on.
I have to look through an entire table and find certain criteria (eg. "STEVE'S PIZZA SHOP"). It might be in columns G or H, or it could also be in Columns C or something of that nature. What I'd like to do is search through the table, find each instance of said criteria and cut each row the criteria apperas in to a different sheet. I'd like to do this as a macro, so I can set it up for other criteria as well. In addition, if I can include in the macro a way to create a header row (which I'm pretty confident I can myself), as well as change the title of the sheet the information is moved to. I have looked up Do-While loops, For-Next loops, If-Then loops. I am at a total loss.
This is what I am looking to do and I am having an issue with it.
worksheet "beeble"
Column B has all the employee numbers listed from B3 to B100 Column C needs to have the emp name put in to them based on the emp number.
worksheet "weeble"
This sheet has the list of employees with their Emp#
A2 down to A99 is the number B2 on down is the name that belongs to the emp #
At issue is sheet "beeble" changes day to day depending who is scheduled to work in a certian area, otherwise this would be quite easy.. It is very easy for us to put the emp# in instead of the emp-name, so that is why wwe would need to be able to pull this information from the other sheet.
This may end up being very easy, but it is beyond me, and I cannot find what I need from a book, as I spent last night on here searching and reading through a few of my books..
I have 2 workbooks open, one called "Temp" and the other called "CRM 2" My active workbook is "Temp". I am trying to insert a value into the active cell in this workbook, based upon the value of a checkbox in the other spreadsheet. Here's my
If Windows("CRM 2.xls").SelectedSheets.CheckBox6.Value = True Then Selection.Value = "By Phone" I've tried variations on this such as:
If Windows("CRM 2.xls").ActiveSheet.CheckBox6.Value = True Then Selection.Value = "By Phone"
Horses have 5 running styles; early speed to late closers; denoted as 1 to 5 in column Z. I'd like to count those noted as 1 thru 3 for each race.
In column FC (the last column with data) I have a race ID # for each race on the spreadsheet, somewhere between 2500 to 5000 races in each one. They're a monthly record. The race ID # is a concatenation of columns B (Track ID), C ( Date) & D (race #). Each is unique to its race. E.G. Aqueduct, jan 1st, 2004, 2nd race is id'd as AQU379872.
Would it be possible to create a macro that would (A) count the number of horse 1s, horse 2s & horse 3s from a specific race ID & then post those numbers in columns FD thru FF & (B) then continue to the next race ID in column FC & count those horses from that race & so on thru the entire spreadsheet?
Would it be simpler if each race ID were changed to a number--race 1 down to race 2500?
I have a list of employees that i need to simplify into groups as listed below
Employee No's 1-19 20-50 50-199 200-499 500+
In my list of data (attached) that i receive i currently manually count the employee numbers and then put them into the relevant bands dependant on the employee sizes. Example:
I have a spreadsheet that lists each employees hours for the last 4 pay periods...each is in it's own row I am trying to find out their average.
I am using the subtotal function to average their hours and that works fine. BUT...my boss doesn't want to see 4 rows for each person. He just wants to see one row for each person and when I collapse the rows, it only shows the employee's ID, not the name (because that's what I told the subtotal function to do...add subtotal after every change in employee ID).
name appears instead of the employee ID? That's useless to him because he hasn't memorized all the Employee's IDs.
I was given a task of calculating bonus for number of projects per employee. The maximum number of projects per employee is 30 and they have completed different number of projects. Data is as follows:
Column A - Name Column B - Date Column C - Project
Employee name repeats one row per project and project repeats as they are working with it.
I need to list individual employee names in column D and the number of projects each employee has done in column E. A project can be saved many times thus creating many rows for that same employee. Do you think it is possible to accomplish this. One formula for column D and one formula for column E. If needed I can attach an example file or take a screen shot of it.
create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
I have a ListObject table called Table1, and the column name is Table1[Company]. The records have been sorted A-Z for the Company.
The records look something like this
Date Employee Company ------------------------------------------- 1/1/11 Jane A Company 1/1/11 Jane B Company 3/1/11 Bob B Company 2/1/11 Bob B Company 4/1/11 Bob B Company 5/1/11 Bob C Company
What I need is a sub() to look in the column of Table1[Company] and determine:
The # of records that match the criteria given the Company name, and where they start and end. Ultimately, I'd like to copy and paste this portion of the table to another spreadsheet.
So for instance, if the Company = "B Company", then I could find out there are 4 records and there locations to copy and paste them.
I am setting up a template to use to track candidates throughout their lifecycle. I am also creating a 'summary report' on another worksheet that counts total number of candidates, counts the number of candidates that have been submitted to the client, count of the number of candidates that have been interviewed by the client, etc.
How do I count the number of candidates in the spreadsheet?
In Cells A6:B5, I have "Submitted" In Cells A7-?? I have dates for those candidates that have been submitted. Then a new section titled, "In Process" A list of candidates are contained within that section. Then a new section titled, "For Review" etc.
There are multiple sections, so the date in Column A1 may not always be in a constant row, as it depends on how many candidates are within a certain 'phase'.
I have a column I5, titled "Status" This column has a built in drop-down list that contains pre-defined status, for example, "Submitted", "Candidate Declined", "2nd Message Sent", etc. I need to be able to create a summary report based on a count of candidates that fit certain 'Statuses'. For example, count the number of candidates that were Interviewed. This could be a combination of Statuses: Phone screen with client, 1st F2F, 2nd F2F, etc.
(At home, I am using Excel 2003. At work, I am using Excel 2007. So if it's easier to create in Excel 2007, that's fine.)
I have created a spreadsheet in Excel where it gives me a report of how many hours my employees do per week and it seperate them in different categories.
What I would like to do is find a way to match the cell's description with the amount of total hours that were spend on certain project.
So here is the scenario.
In my department there 6 employees that are assigned to work on certain projects in daily basis. These projects are called CRs and to identify them I've added a number after them. So we will have CR0001, CR0002, ect. These are unique projects.More that one employee that could woork at the same project as well on other sections but I am only interested on the projects.
To help you understand what I am trying to do I will give you an example.
John is working on project CR0005, CR0006, CR0001. He has been working on them three projects for the last week.
Here is the summary of the hours:
- 5 hours on CR0005 - 10 hours on CR0006 - 5 hours on CR0001
This information is inputed in Excel spreadsheet Week 1 .....
how to use formula in the monthly contribution field to calculate 7% of pay (salary)
Lenght of Total pay Monthly contribution cummulative Service retirement value 3.6 $528.94
Please one more thing. How do i use FV Function, monthly contribution, and the lenght of service field to to determine the current value of the employees retirement palan.
i am creating a weekly time sheet for my company.the problem that i have is when the persons time reaches 40 hours, the time needs to be calculated in the overtime field. this is really tough for me when the person reaches 40 hours in the middle of the work day. I cant figure it out. i have attached the spreadsheet if you would like to look.
I'm trying to create a weekly employee schedule using Excel 2007 and have several issues but will start with one at a time till we get them all resolved.
I put in my start hour '6' in cell 'B7' and get this: 1/5/1900 12:00:00 AM.
I would like for to display 6AM.
I figure if I get his cell fomatted that I can get the other times correct then go on to the next issue.
