Is it possible with excel, to automatically insert an employees number if their name is inserted.
Example:
Column A If Smith, John is entered in Column B automatically have his employee number show 5668?
Joe, Mary entered in Column A Column B to show 12345
This would save me a huge amount of time going back and fourth.
I have a running worksheet to account for expenses and deposits. At the
bottom of the sheet are the total values. I have to keep "inserting" rows to
move the values further down the sheet.
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
We have a schedule that creates an Excel file which separates each order by blank rows. I need each order to be separated by a page break, so each order prints on a separate sheet.
Each order entry is 1 or 2 rows
Using Excel 2010/2013 although the file produced is an xls file.
Columns A to J are populated
Rows 1 & 2 are the header
I have tried a macro that involves me doing a countif and counting entries in the row, and if they add to 10 it inserts a page break, but its bit long winded and only seemed to work once.
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:
Employee List
4
28
35
46
503
376
2000
Employee No's
1-19 - 1
20-50 - 1
50-199 - 3
200-499 - 1
500+ - 2
I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.
The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.
I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.
as there are 600 names and some can appear up to 4 - 5 times!
I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.
...................................1/1 ....1/2 ....1/3 ....1/4 ....1/5 ....1/6 ....1/7 ....1/8 ....1/9 ....1/10 ....1/11 ....1/12 ....1/13
Captain America ................................A ..........................T .....................................T
Incredible Hulk.......................................................................................... A
Spider Man ........................................................................................................................ ........A
Iron Man ....................................................T
( I added dots because it wouldn't let me space them out?)
We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.
Any formula to use. Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!
I am trying to create a rota in excel but I am struggling to get it to add up the total number of hours per week per employee.
It get a little complicated as they work split shift so they may do 4 hours in the afternoon and another shift in the evening going into the next day, example they may work 11.00 am - 3.00 pm and 8.00 pm untill 2 am.
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
View 19 Replies View RelatedBasically I first want to be able define around 100 variables such as:
Dog = 1
Cat = 2
Mouse = 3
And so on. The reason being my final data needs to be the numbers for it's purpose but because there are so many I can't possibly remember them all to just type out 23 | 44 | 76 | 2 etc. What I would like to then happen is that whenever I type out the words in a cell it's automatically replaced with the number ID.
So typing in
Dog
Mouse
Mouse
Cat
Dog
would automatically result in
1
3
3
2
1
In those cells when I hit enter or at the end in one go. Is this at all possible in Excel 2010?
I am creating a spreadsheet for my job and knew a code or any way to implement this into my spreadsheet.
View 1 Replies View RelatedI am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.
Employee Name
Date:
1/1
1/2
1/3
1/4
1/5
1/6
1/7
1/8
1/9
1/10
[code]....
We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.
Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!
i have a problem in that i need to generate a random 6 digit number preceeded by an X if the data in column B is either blank or 0. This needs to be filled in if the adjacent cell in column C has data in. i am a moderate macro writer and am finding this part quite challenging.
i am ussing excel 2007.
I have a Log In/Log Out report that I need pulling data from.
Ultimately what I want to do is get the amount of time my employees were logged out.
I am using Excel 2003. Attached is an example of the data I'm working with.
Sheet 1 contains the data, and Sheet 2 will be the summary.
So, looking at Bob's times: If column A contains Bob (A2 in this case), and the same row in column G (G2) contains 79, subtract the contents of the same row in column F (F2) from the next row down in column D (D3) (which would be D3 - F2). That result should populate on Sheet 2 next to Bob's name in h:mm:ss format.
If there is nothing in the next row down in column D, then do nothing.
I would like to leave the data as is, if possible. There are extension numbers next to the names in the data. If possible to ignore those when rolling up to the summary that would be useful. Also, on the summary sheet, if the employee is not on the data sheet (if they had the day off) they can have 0 or NA or something to indicate there is no data for them.
Our office has a vacation days accrual policy based on number of years worked. We have a vacation days number, based on year of employment, the employee earns monthly. I need help with a worksheet, formulas, to document each employee, the year of employment they're in, # of days they have available based on the current month (which would need to add up automatically as the year progresses), then any days they request off, and finally a remaining balance of days left.
View 6 Replies View Relatedappending excel sheet number in a formula. Here is the example.
In an excel book, I have sheets 1 to 50. In each sheet i have a standard format to enter the details. And in one sheet I want to keep some summary with required cells. I made a table by applying formula, for example in cell A1 i kept formula ='9'!$D$9. In this '9' refers sheet9. In the similar way i want to capture the data from all sheets.
A1 cell ='9'!$D$9
A2 cell ='10'!$D$9
A3 cell ='11'!$D$9....etc
Is there any formula to append the Sheet number automatically (like dragging the shell) intead changing sheet number manually. because it is wasting so much time.
I have what I thought was going to be a simple function to create and, after a week of getting more error messages than I've had in a year, I'm ready to throw in the towel. Situation: There are two worksheets in the same workbook [Excel 2003]. One is for "Posting" details about technicians and their work orders. The other is a "Report" that summarizes the number of workorders open, closed, etc.
In the "Posting" sheet, I have created dynamic name ranges for two columns: Technician (4-digit text field) and InDate (date field that, if blank, means that the technician has not closed this work order. My ranges are set up like this:
Technician=OFFSET(Posting!$B$2,0,0,COUNT(Posting!$B:$B),1)
InDate=OFFSET(Posting!$D$2,0,0,COUNT(Posting!$D:$D),1)
So all I need to do is to total all the blank InDates for each Technician by employee number. I've tried COUNT to SUMPRODUCT. A co-worker got it to work with using...=SUMPRODUCT((ISBLANK(Posting!$D$2:$D$65307))*(Posting!$B$2:$B$65307=4288))
His solution was to list almost every available cell in each column. I just can't help but think that the dynamic name range should be able to work but I can't get it right. I think I'll choke if I get one more "N/A" or "VALUE! error.
I am trying to automatically generate an organisation chart in excel from a dynamic list of employees. in column 'A' will be the "manager's name", column 'B" will be the "employee name", column 'C' will be the "employee number" & column 'D' will be the "employee days" number (an potentially a few more employee attributes)
I would like a printable org chart that is created on another tab which is generated from this list, ideally with the "employee name" and their other attributes shown in the chart.
how this can be done in excel, maybe VBA?
I used Excel 2013 template to create employee absence schedule but I want to add if they have enough accrued hrs/days.
Currently, I need formula so their holiday balance is accruing every week.
On a weekly basis employee is accruing 3.85 or 4 hrs (depends how long he worked for the company).
Less than 5 years rate is 3.85 hrs and more than 5 years is 4 hrs.
I attached example sheet for accruing vacation.
E column is employee current balance from last week.
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
I can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.
Here is the code i have but it wont even compile........
problem with Excel and I have no idea how to solve it. Hmm, how do I explain this? Someone is giving me data pulled from a server in an Excel format. I have a column that has a series of entries.
For example:
Blank
Entry 1
Entry 2
Entry 3
Entry 4
Entry 5
However, sometimes there's an entry or two missing. So it'll look like this:
Blank
Entry 1
Entry 2
Entry 4
Entry 5
Is there anyway to tell Excel to say "Hey there's an entry missing here," and have it insert a row with the missing entry value? I do not work in Excel ever but people around here do, in case someone has some advice. I already asked the guy if he could just server me the data formatted correctly, and -- of course -- he can't/won't.
I created a form in which I can enter data that is totaled at the end. Is there a possibility that a new row is inserted at the end of the form automatically every time I enter data in the last field?
View 9 Replies View RelatedI have two separate tables, one above the other, and need a way for it to automatically shift the second table down or a row between the two tables any time another row is added to the top table. Is there any way to do that?
View 2 Replies View RelatedThe spreadsheet is used to keep track of shares that are purchased / sold
It will make all tax calculations as well as provide a single statement showing all transactions rather than having to sort through dozens of individual statements.
I seem to have most of it working but could use some help with the following.
I would like to be able to have a spot (maybe on a separate worksheet) where you could enter the four items from which everything else is derived, share price, date, number of shares and whether it was a purchase or a sale, and have it automatically:
1. add a new row (complete with formulas and formatting info) to display the new information
2. sort the new data to fit into the right spot in the spreadsheet by date.
I'm trying to insert pictures automatically on Excel 2000 but i still with problems to find the better solution, please see the file with a better explanation.
View 6 Replies View RelatedI am trying to keep my checkbook in excel and want to make a spreadsheet that inserts a row every time I hit enter. That way my most reason transaction and balance is always at the top rather than the bottom of the spreadsheet... The inserted row should have the same formulas as the row below.
View 7 Replies View RelatedI have an expenses sheet where I would like to add something I saw on a template...
press "tab" in the last populated cell of a table to insert a blank row. (below or above, it doesn't matter) So I can add the next expense.
Any way to have the worksheet name on a tab inserted automatically in a cell on the spreadsheet? I have a file used to record orders and I have to keep inserting new worksheets for each order. I want the name on the tab of each worksheet to show in cell A1 so when I copy in a new worksheet A1 will show Sheet 1, for example. However, when I rename the worksheet I want "Sheet 1" in A1 to change to the new name.
View 2 Replies View RelatedI have number of column.Say 10 columns. in Column say J5 I have word daily varience. I want a macro to check word daily varience and go 2 steps to the left and insert the column.
View 9 Replies View Related