I have a huge workbook with lots of sheets. I'm an Instructor and I use the workbook for all my students records. The first sheet has all the students names. The second sheet has a statment that only females have to sign. On the first sheet column A is "First Name", column B is "Middle Initial", column C is "Last Name" and column D is "gender".
I would like to be able to have all the Female names automatically inserted on the second sheet into the "name" cell. The concatenate formula will probably be necessary as the names will have to be put together (Last name, MI First Name). I have a maximum of 12 students so I have created 12 individual statments so that if there are 12 females their names will be inserted into each name cell for each statement. I can send or post the entire workbook if necessary.
I have a list of employees split out into gender and performance rating. I want to graph this to compare male to female salaries and performance. Data attached.
I need to create a function for cell E9 that will look at cell D4(gender) AND cell C5 (age in days) and choose the correct value J3:M3 (boys) or O3:R3 (girls) to subtract form cell C9.
I tried =IF(D4=2(AND(C5<E3,sum(C9,-5),FALSE)) but did not get anywhere.
I am working on a spreadsheet that needs to access data from two different spreadsheets. I train an army in Africa and this will be used for their physical fitness test. I have set it up where the user inputs the soldiers name, birthday, gender, push up raw score, sit up raw score, and run time raw score.
When I first wrote the program all I had were male soldiers, now I have female soldiers as well. My problem is I cannot figure out how to tell it to go to either the male or female data page. The code I had used for accessing the male page earlier was: =IF(OR(E3="",E3<17,F3=""),"",VLOOKUP(F3,'Male Points'!A4:K77,IF(E3>61,11, ROUNDUP((E3-16)/5+1,0)),TRUE))
Now I need the program to figure out which data sheet (Male Points or Female Points) to use. And will I have to adjust my code above as well?
i am trying to assign names to my ranges using VBA. i am doing it through the following but seems like it does not work.
Sub MakeName() ActiveWorkbook.Names.Add Name:="ACCOUNT", RefersTo:="GL_Details!$J$2:INDEX(GL_Details!$J:$J,LastRow_GL)" End Sub >Workbook Defined NamesNameRefers To ACCOUNT=GL_Details!$J$2:INDEX(GL_Details!$J:$J,LastRow_GL) ACCOUNTING_PERIOD=GL_Details!$C$2:INDEX(GL_Details!$C:$C,LastRow_GL) BUDGET_YEAR=Expenditure_Details!$BF$2:INDEX(Expenditure_Details!$BF:$BF,LastRow_Exp)
How do I get a drop down (gender list) to affect the data in another cell? ie when male is selected this automatically affects another cell - data 13.5 and when female is selected as gender the cell data is 12.5.
I need some code that will find the column with the header of "Gender" and change the values from "M" and "F" to "Male" and "Female" but I keep running into issues.
I want to use a Select Case statement so I can set all other found values as blanks.
I have a series of data and want to create a chart looking like this
Google Image Result for [URL] ...
Where it shows min/max on the error bars, quartile 1/3 on the box and median as a scatter plot. My data is a simple table
Group Amount F Amount M Median F Median M Median F+M
[Code] ....
Here F = Female, M=Male and the chart should show all calculation for both gender together (I have another thread up where the gender are separated with the Title (How to create a stock column chart with error bars?? (Both gender separated))
Using two columns of data: Column A with a list of first names sorted by first name, Column B with a different integer value for every cell in Column A. How do I write a formula that sums the values contained in Column B for each unique first name in Column A? The formula needs to identify where first name changes in Column A as there are different numbers of entries in Column A for each first name. (Note: I do not wish to use sub-totals, as I want to copy/paste values back into the results column and use sort to generate a new lookup table)
Have attached an example of the two columns I would like to test. Ideally I would like to see the results in a third adjacent column with the sum displayed only where a new first name appears in first column (the cells would be blank in the third column when the first name is the same as the row above).
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
See the attached spreadsheet. I have people's names in cells A2:A5. These names have a rank value in cells B2:B5. What I would like to do is create a formula that sorts/orders the names in cells A2:A5 according to the rank in cells B2:B5. The results I would like to achieve are shown in A8:A11. I am aware of the "Sort" function in the "Data" drop down menu, but need to leave cells A1:B5 as they are.
Reference the attached excel audit example. Column F highlighted in Yellow is finding the difference between "Authorized Post Allow" D9 "Post Allowance" B9 . The Column location of the two Columns changes on each audit. This means I have to manually fix the formula in Column F every time. Is there a formula that would find the difference between these two columns based on their names "Post Allowance" and "Authorized Post Allow" preventing me from manully fixing the formula.
I need the names of students from Summative tab in the correct box based on how many question they got correct. (Q 27-32, (0-1, below, 2-3, Average, 4-5, Above Average, 6, Excellent Performance. ) I have attached a file.
How can I sum values with names based on a number prefix? For example, in the attached sheet, how can i sum all values that have the prefix 4.10.02.xxxx?
I am a small business owner with little experience in Excel and I have been trying to set up a worksheet that will organize my products and finances. I organize sets of various products and sell them in bulk to customers. I was trying to recreate this in Excel so I can figure out the total price for each of these sets and easily edit them.
What I've been trying to do is to take a list of the items and assign a price to each of them. This is on a sheet titled "Prices." Here's an example:
prices.jpg
On a separate sheet titled "Sets," I created lists of items included in each particular set. To easily add or change the products I used drop down menus.
sets.jpg
I would like for the sum of the price of all the items listed under each set to add up automatically in the "Total" row. For example, in cell B7 it should display the number 10. Here is a list of the following formulas I've tried:
The names are not static and can be moved, meaning that John can be 1, 2, 3, or 4 (all depending on how the name is chosen). I want to find the average on where John ranks. In this instance, he would be 2 [(1+3+2)/3].
My question is - How can I create a formula that will look for where John appears in the column (B1) and call the previous cell (A1)? This formula needs to be repeated on each column, added and averaged.
I have created 70 "templates" in my workbook. I would like to change the name of each template to the name contained on a list in the "Data" sheet. The list starts at a4, and may have 70 or more names. I want to cycle through each name, place it in cell c8 of the template, rename the template with the same name, then move on to the next name, rename the next template, place name in cell c8, and so forth. So far I have this, which creates the "template" and renames it according to the list, but it doesn't insert the name into cell C8 of each new sheet.
Sub NewSheets() Dim I As Integer Dim ws As Worksheet Dim sh As Worksheet Set ws = Sheets("Template")
Worksheet 2 I want it list the Names only from highest to the lowest from worksheet 1. If two Names have the same total then I want it in Alphabetical order towards who will be listed first.
Example: Worksheet 1 B10 Sam AO10 10 B11 John AO11 14 B12 george AO12 16 B13 Steve AO13 9 B14 Carlos AO14 9
I have a long list of names and right now I have to scroll down the list like crazy to get to some of the letters (xyz for example).
I really wanted to setup a clickable row or column that has one letter in each row or column, but I'm not sure I can do that as the sheet is already formatted. But I could just use one cell where I could click in it and type a letter which would jump me to the portion of the list that starts with that letter.
or I guess maybe something that pops up a requestor for me to enter the letter.
I found some stuff that was close on here that had some of the functionality I needed, but I couldn't piece it together.
I'm trying to go through David's books, but I haven't had a lot of time and I'm not getting some of the basics.
sheet1 tab name is : MAIN and other sheet tab name like following 2. xyz-Sales 3. xyz-Rev 4. xyz-SSN 5. xyz-ddn 6. abc-Sales 7. abc-Rev 8. abc-ddn 9. abc-ssn 10. ddd-sales 11. ddd-Rev 12. ddd-ssn 13. ddd-ddn
In Main sheet There are 3 buttons
1 . XYZ 2. abc 3. ddd
when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide
if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide
i don't want to use
Sheet2.Visible = xlSheetVeryHidden
i want to use finde xyz sheet tab name and shows and other are hide.
I have an excel sheet which displays the data in the following format: (See image attachment). This is just an example of the way the master list is formatted. The master list has over 18,000 lines. Basically, there's a project name, resource name, and number of hours worked for the project within that work week. I've been trying to figure out a way to create a formula or macro to search all these names and add up the hours of the duplicates.
I need excel to search this list of names and total up all hours for each matching name and place each total value on another sheet. For example, Ken Craver has 8 occurances within the week of 1/21/2007 (from project 1 and 25) and his total hours = 31. So the value 31 should be calculated because all the hours associated with Ken Craver add up to 31. Then excel should scan another sheet which has a list of resource names and once it has found the matching name on the other sheet, it will place the value 31 in the column next to the name.
I'm trying to create a macro which will run through every salesperson in a list, and copy and paste their product and client data (from separate sheets) to a template sheet. The template sheet will then by copied to the Salesgroup workbook, and renamed to the salesperson's name. Then the macro should go back to the original workbook and do it all again for the next salesperson, putting them into the same workbook.
The problem I'm having is that the references to the Template worksheet seem to be being messed up by copying the template sheet to the new workbook, and it then ends up creating all the worksheets, but it stops taking the data from the data sheets, and ends up copying the previous worksheet and pasting that into the new copied template, which means that I get the template multiple times across the same sheet.
Sub SalesSheets() Dim Salesbook As Workbook Dim NewBook As Workbook Dim SalesDataSheet As Worksheet Dim ClientDataSheet As Worksheet Dim SalespersonListSheet As Worksheet Dim Template As Worksheet Dim rRange As Range Dim rCell As Range Dim strText As String Dim SalesGroup As String Application.DisplayAlerts = False Application. ScreenUpdating = False Set Salesbook = Application.Workbooks("2008 Salesperson TEST.xls") 'change filename here Set Template = Salesbook.Worksheets("Salesperson Template") Set SalesDataSheet = Salesbook.Worksheets("SP product YoY") Set ClientDataSheet = Salesbook.Worksheets("Client YoY") Set SalespersonListSheet = Salesbook.Worksheets("Salesperson List")..........................
I want to make several charts with dynamic ranges. To do that, I wrote the offset functions I need in cells. I've attached a sample spreadsheet. I want to have a macro so that when I run it, it will take the contents of the active cell, insert a name called those contents, and make it refer to the cell 4 to the left of it.
In the spreadsheet, I'd like to be able to click on E2 (sentdate1), create a name called sentdate1, and make its value A2, or =OFFSET('Sentiment'!$A$3,0,0,COUNT('Sentiment'!$A:$A),1). Here's what I have so far.
Sub Macro8() ActiveCell.Offset(0, -4).Range("A1").Select ActiveCell.Offset(0, 4).Range("A1").Select ActiveWorkbook.Names.Add Name:="sentdate1", RefersToR1C1:= _ "=OFFSET(Sentiment!R3C1,0,0,COUNT(Sentiment!C1),1)" End Sub
Auto Merged Post Until 24 Hrs Passes;Here's the sample worksheet.
I am trying to produce numerous workbooks based on a filtered name. I will attach a sample spreadsheet that has the data.
On the sheet we have engineer names. I basically want to filter them (not difficult with a macro) but then to copy the results to a new spreadsheet and save the workbook as the engineers name (ie J. Bloggs has 5 jobs so they are filtered and the results are dumped into a new workbook and then saved as J. Bloggs.xls). This will happen for all engineers.
I have though about doing it as a macro and I think that would give me the end result but we have around 20 engineers and these can sometimes go up and down. Is there anyway to do this automatically?
I have a column containing names, blank spaces, numbers, a bunch of miscellaneous things. However all I want to do is extract only names, into another sheet, and not the numbers, blank spaces, etc. Youi'll see what I mean when you open the attachment. Offset isn't an option because the names don't appear in the column in an organized fashion.
I could really use some excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.
So for example, I have the following list of files:
I'm able to do a formula to get the term digits (meaning the last two numbers), but i'd like to have another column that can put names based on the term digit column. For example, Tom might work 00-04, Sally works 05-09, Greg works 10-15, Lucy works 16-21.. etc
I came across the below IF formula that is exactly what i need, except it only works for two associates and not the multiple that i need.. but it looks to be a good starting point nonetheless.
------------------------------ =IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.
You can also use (if A2 is where the Loan # is):
=IF(Right(A2,2)*1<50,"Sheryl","Lisa")
The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's. -----------------------------
I have a column (A) with names, Column (B) with platoon numbers. On another sheet I have a table for each platoon and I want it to be populated with the names of each platoon indevidually. The names are alphabetically and the platoon numbers are mixed.
Column A Column B Name Platoon J Goodman 1 L Barns 7 H Law 1 B Aims 10
and so on....
I want all the names from platoon 1 to appear in the table for platoon 1. I have about 3000 members and 40 platoons.
