Last Date By Person
Jan 4, 2013
I have a sheet that tracks when people are paid. Each time a person is paid, another entry goes into the sheet with his/her name, pay amount, and date they were paid. So, a given person could have up to 50 lines devoted to them, each with a different date associated with it (the list is sorted by name).
What I need to do is figure out the most recent pay date for each person in the sheet. I would prefer to avoid vba, but experimenting with native excel functions has gotten me nowhere...
View 2 Replies
ADVERTISEMENT
Dec 15, 2006
I am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.
I have two objectives that I am trying to reach:
First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date
Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.
If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.
I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.
Start date is column E
Finish date is column F
Owner is column L
View 10 Replies
View Related
Apr 21, 2006
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View 7 Replies
View Related
Apr 4, 2014
I have a worksheet with some names in a column with a persons first name then the second name and I would like to place the names in a different column I can have this to work ok with the following formula
=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))
This works ok but I have people with three names like Peter May Jones
When I have changed the name I would like to have (Jones, Peter May) but I get (May Peter Jones)
Also the colon I have after the first name gets put at the end of the name not after the last name.
View 12 Replies
View Related
Jul 25, 2014
I'm looking for a formula to calculate the age of some one. I used a formula but it gave me a #value! Error
20/05/1936 (E4)
=YEAR(E4)-YEAR(TODAY())
View 4 Replies
View Related
May 31, 2007
I have some data as below and I want to return the name of the person with the highest value.
Col A Col B Col C Col D
John Peter Paul Luke
3 4 7 8
View 9 Replies
View Related
Dec 19, 2007
I have a list of order numbers and sales persons associated with those order numbers..
I was trying to come up with an idea of how to enter a Sales Persons Name in a field and have a random order number based on their name populate so that I can audit that particular order.
Can this be done in excel with a vlookup formula or something to that affect?
And if so, how?
Order # Sales Person
256796 Judy
248986 Brian
897655 Fred
245466 Judy
364599 Tomas
Since Judy has more than 1, I would like to just radomly pick one of those... Now, just a little side information, my actual sheet has approximately 3000 orders with 52 sales reps...
View 9 Replies
View Related
Feb 2, 2009
I am trying to write a series of macros to automate the creation and population of a summary tab for transmission of patient results to our Laboratory computer system and have hit a bit of a brick wall. One of our analysers creates an Excel workbook for each analytical run with each patient sample analysed on the run assigned to a separate worksheet. These worksheets contain the patient ID (lab number) in cell C1 with results for the different analytes in cells F7 to F56. We need to be able to present this data with each patient having a row to themselves (i.e. Patient ID for patient 1 being in cell A2, test result 1 in cell B2, test result 2 in cell C2 etc, then patient 2 taking row 3 etc). Row one is populated with the field Sample name in cell A1 and the cross-referenced test IDs running across the rest of the row. As analytical runs can contain varying numbers of samples we need the Summary tab to be able to cope with a indeterminate number of worksheets.
View 2 Replies
View Related
Dec 27, 2007
I am creating a personal expense tracking sheet for the upcoming year. I have each day of the year going down as my rows. My column headers are various expenses I expect to have, such as a phone bill and internet bill. I want a way to pick which expenses (columns) are visible. I expect this to aid in the inputting of expenses and the reviewing of expense types. I know of course, I could just click all the columns I don't want and hide them, but I'm looking for a quicker way if possible.
So, if I want to record the phone bill, or just look at the phone bill expenses alone, I could use a drop down which listed all my column headers and check which one(s) I wanted to view. I thought of like the way a pivot table option looks to pick what you want to view /shrug.
Maybe a drop down is not the way to accomplish this, and maybe my spreadsheet should be laid out differently.
View 6 Replies
View Related
Sep 15, 2013
I need to create a line graph that has 2 lines for each person, one with the amount, one with the points. The X-Axis is by Weeks going up. How can I do this without making a separate series for each? I've attached an example.
example.xlsx
View 1 Replies
View Related
Feb 10, 2014
I am trying to write a formula that isn't a gigantic IF statement to calculate whether a person has upgraded their type of house or not within a time period.
The below are the names of the types of housing. I have two of these lists, the first is from the beginning of the time period and the second is from the end of the time period:
Pucca - Normal
Kuchha - Strong
Pucca - IAY
Kuchha - Medium
Kuchha - Weak
The best option on this list is Pucca - Normal and the worst is Kuchha - Weak.
A person can start at any option on this list and end on any option. I only want to measure whether their type of house has been upgraded (i.e. from Kuchha - Weak to Kuchha - Medium). I do not need to calculate whether they have stayed the same or been downgraded.
If the person has upgraded I want to display the value of 1. If they have stayed the same or downgraded I do not want to display anything.
An added complication is that if they have upgraded to Pucca - IAY I want to also do not want to show anything.
View 3 Replies
View Related
Apr 6, 2012
I have uploaded my excel spread sheet.
[URL]
Viewing only displays half the written request, but I downloaded it, and it was ok
View 8 Replies
View Related
Jul 9, 2012
I'm struggling with the VBA DATEDIFF function. I have a person's DOB and am attempting to determine their age by comparing to a different date. It seems like their age only changes when I change the year, rather than choose a date within the year past their birth date... Here is my code:
Sub Test_click()
DOB1 = #7/19/1930# 'DOB
date2 = #7/18/2012#
Date3 = #7/21/2012#
age1 = DateDiff("yyyy", DOB1, date2) 'age should be 81 (but is 82)
age2 = DateDiff("yyyy", DOB1, date2) 'age is 82 (correct)
End Sub
View 5 Replies
View Related
Oct 15, 2012
I have a spreadsheet that I want to use to plan projects across days and I want to assign a task to any one of four people. there maybe more than one task in any project that the same person may be assign to. Want I want to do is count how many assignments they have on that day. I've tried using things like vlookup, row(), hlookup and a various combinations of these with countif, or maybe dcount, I'm just not sure, but I think I'm finding it too tricky.
I've uploaded the sheet here..[URL] .....
So in other words how many tasks does person y have if the x is on their row. The person is a data validated drop down list so if the person changes from y to b then this will effect the total at the bottom.
View 2 Replies
View Related
Jan 7, 2014
I have some data, similar to below which is sales data for each sales peson in my firm. The info I have is the name, the quoted value and the sold value. I estimate by the end of the month I will have over 400-500 entries so dont want to do this manually.
I have been able to use the sumif function to tell me the totals that each sales person has sold and quoted.
Now I want to work out the
min and max that each sales person has quoted and sold
and
The number of sales each sales person has had
Are there formulas to do this?
Ive looked at the min/max functions but I believe they will only give me the min and max for all of the quoted and sold whereas I want this by sales person.
I have also looked at the count function but this will only provide a count of the entire number of sales and quoted, again I want this by sales person.
Sales Person
Quoted
Sold
[Code].....
View 2 Replies
View Related
Apr 15, 2014
Is there a way to search responses from a specific Person? I think it was before the Mr Excel format change. Domenic was the responder.
View 2 Replies
View Related
Sep 21, 2007
I have worksheet 1 which calculates a member's pension which is based on his date of joining a scheme, date of leaving, salary and yearly contributions paid into the scheme. Normally I would input these details manually but now I have a large number of members to produce figs for. These members' details will be on worksheet 2 with a separate row for each member's details. I want excel to process each member one by one and enter his details in worksheet 2 on the calculation worksheet 1 and put the results in worksheet 3. Is this possible? And how???
View 8 Replies
View Related
Sep 5, 2013
The first column is "Person Name" and the second column is date in format "6/27/2013". I want a formula to calculate the total number of calls a person made in the month of June.
View 2 Replies
View Related
Aug 7, 2014
I have a list of data which i need to send to individuals. I can sort the data by person's name.
I need to send each individual person their own data. Instead of copying and pasting each person's data into a new tab for everyone, is there a quick way in excel that for each change in person's name (Column A), that it creates a new tab with all the data?
View 5 Replies
View Related
Jun 7, 2013
I have a file is used by many person at the same time. Sometime 2 person can open it as compatible mode , when 2nd person save will write over the 1st person data, that means all have been done by 1st person will be gone, serious problem.
Normally open a file will tell me who have using it , but it show wrongly person although the person has not open the file. worse that 1 day only show 1 person name.
View 4 Replies
View Related
May 16, 2009
I have to print the admit card of each students with their details like Name of student, Fathers name, roll no., subjects, examination center years, etc. in a given admit card format. I had scan the blank admit card and used as a background image in excel but the image of the admit card does not print in excel except the cell values that I had entered.
I had made the database of all students details. I want to use the students database to print students data on the scanned admit card image. I also want a option to select the name of the student before printing (to verify the details). Please help me how to do it in excel. I am using excel 2000.
View 8 Replies
View Related
Dec 5, 2007
I am trying to create a formula to pull the person's name and the avg number of cartons sorted for the month from this spreadsheet.
I've attached a sample of the spreadsheet I created, you will see at the bottom I would like to see the person that had the highest avg for the month and the person with the lowest avg for the month.
This is just a simple spreadsheet to track productivity of my sortation employees.
View 9 Replies
View Related
Feb 1, 2009
I have attached a worksheet that shows the last and first name of an employee along with their hire date and term date, if termed. What I am looking for is a function that will tell me if a person was active as of 12/31/08 which will read true on the side of their name under active; I also need a function that will tell me if a person worked over 6 months in 2008.
The first person John Doe was hired in 2005 and has a true on the side of his name which meant that he was active as of 12/31/08 but as you can see Jane Doe has true on the side of her hire date and false on the side of her term date and I don't want it to read the first true but the last true of someones name, but Jane Doe qualifies for working over 6 months even though she started in 2007.
View 7 Replies
View Related
Dec 22, 2013
I am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.
I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?
sheets.time chart.xlsx
2014 tab- grey column is installer and white is shop employees
profit loss tab- all calculations done here
payroll tab- all cost data here
View 1 Replies
View Related
Jan 15, 2014
How to create a search formula that will look up a person's name in a given worksheet in a specified cell?
View 8 Replies
View Related
Jan 28, 2014
I'm trying to create a macro that will take name values from a list (Last name, First name) and then find these particular names in a long list of names in another sheet. Once all the occurrences of the name are found, the macro would need to add up all the hours that the person has worked during a given month. For example, I have Jane Doe and John Smith on my shortlist, the macro would have to take the name of the first person, Jane Doe, find all the occurrences of that name in the second sheet that contains names, dates and hours worked, then add up all the hours worked by that person and return the total hours worked value for that parson. Then take the name of the second person, John Smith, and do the same, and so on. Is it possible to do it with macros?
View 2 Replies
View Related
Jun 25, 2006
I am making an excel file that should convert the distance jumped by a person to a specific rank. For example if the distance was greater or equal to 34, he would get a 10. and 34> distance >= 32, he would get a 9 and so on...
I need a formula that will should nest more than 7 functions, in this case it wont work right? So what I did was name 2 separate fuctions for example
View 11 Replies
View Related
Dec 20, 2012
I would like to know how I can get excel to randomly select one line of data for each of the people listed below (so one for bob, one for jenny, one for Trish etc.). I have been trying a mixture of vlookup and rand but to no avail, below is just a sample list but I would like to try it on a large set of data. I have been able to get a random transaction but not one for each person.
Code:
a1
b
c
d
e
f
g
h
i
j
2
3
4
REF_ID
NAME
DATE
BILL TOTAL
[Code] .....
View 1 Replies
View Related
Apr 29, 2014
Drop down menu that can pull data for 1 person at a time from Table?? (Even possible)
View 1 Replies
View Related
Jun 18, 2014
I have created a spreadsheet which uses data validation to allow a user to select a company, start Date, end date, and projected months in order to output different figures. I have attached a spreadsheet to this post in case that was hard to understand.
Since there is data validation, indexing, and matching going on, as different selections are made, various tables change as a result. What I mean as you open the example sheet and start messing around with the data validation boxes.
The problem I am encountering now, is creating a chart that will also keep changing as the user changes their selections in the data validation boxes. I am unsure if this is even possible, but I figured it was worth a shot to ask. On the example sheet, I have included a graph similar to the one I want, but it only contains data in a certain range of cells, rather than dynamically changing to accommodate whatever data is outputted.
Here is what I am looking for in the graph:
1. Whatever dates are selected by the user in the data validation boxes, along with the projected dates will be on the x-axis.
2. The PMPM figures associated with the selected dates, and the projected dates on the y-axis.
3. Ability to make different selections from the data validation boxes and still have a dynamic chart that keeps updating itself.
View 2 Replies
View Related
