I am currently maintaining a database that keeps up to date records of employees in my company and their vacations including their nationality dept. etc. for the vacation reports i have a "last day of work," "return to work date," "Actual Return to work date," and at the end a "remarks" column,
Moreover I need to report how many employees per department/Discipline are on leave ex. ( mechanical, electrical, and so on.) That I did using countifs having whoever is remarked as "na" vs. actual return date, Discipline vs. each discipline. All works fine but what i want to ask is there anyway that i can list the names of employees that are on vacation under each discipline?? Ie. if 3 are in the electrical engineering department, can i list their names? or if Today()>Actual Return to Work day (ie they are late and have not arrived yet) is there a way that it can list the names of multiple employees? rather than having to work against each name etc.
here's the formula that I'm using: =INDEX($C$35:$AF$35,MATCH(MAX(C41:AF41),C41:AF41,0 )) where $C$35:$AF$35 contains names of people & C41:AF41 contains #'s. This works great if there isn't a tie. Is there a formula to search the range, find the max # and if there are two answers display both names. ex. the max# is 3 and both joe and sam have 3. it would then show joe / sam or something like that. Is this possible? If not, then if there is a tie for the max #, to have the cell just display tie.
I have a table whereby I Vlookup a different spreadsheet on an order number (in this instance Z011352/001). There are multiple sketch numbers associated to this Z number.
I would like to be able to input the Z number and then obviously the Vlookup pulls up the first sketch number. This works fine.
If I then input the same Z number again it pulls up the same sketch number, but I want it to look in the current table (the one i'm entering the Z number onto), see if the sketch number is already present and if it is pull up the next sketch number in the list on the separeate spreadhseet. (I hope this is clear enough)
I have a message box in my spreadsheet. I want it so if the user puts in multiple answers that certain message box appears, if they put in a different set of multiple answers it shows up. For example, the code below is what I am using now. It says that if the user types 5225 in C16 and 3000 in C17 then a specific msg box appears. I want it to say if the user types in 5225 or 5226 or 5227 the same msg box appears but if they type in 5665 a different msg box appears. How would I change this code? ...
In the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.
Which changes my cell to show the text 33% if the text cold is entered into cell E2. Now what I would like to know, is if I can add multiple catch words to give alternate pre defined percentages. Such as warm and hot to give the respective answers as 66% and 99%
to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?
I have a table in excel that i need to use to mark a 20 question quiz. I have the correct answers in one column and the students answers in the next column. I want to mark a correct answer with 1 and an incorrect answer with 0 marks. I know how to use IF, however the students answers can vary eg, correct answer could be B and D but the student writes B + D. Is there any way of marking this correct even tho it is not exactly written out correct?
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
See example attached. I am building a form where a user need to provide different criteria to receive a specific hourly rate. Conditions are:-
1. Age 2. A / B / C / D 3. Weekend (W) or Mid Week (M)
There are 4 age groups, 4 categories (A,B etc) and 2 Daily rates, therefore there are 32 different possible rates. I have considered nested if statements but I think this would be a very long formula - is this the only answer?
I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.
I've attached the XL file for your review of the project.
I need to establish a method of interleating multiple dropdowns or Form Control boxes. The purpose is to select one item and have a selection of multiple items associated with the selected item. Example:
Computer training dropdown box 1 offers selections of word, excel, powerpoint, ... If selection is Word, then dropdown box#2 offers advanced in room #15, Intermediate in room #16, Beginning in room #17. If Box 1 selection is excel box 2 selections would be different.
I have tried to go around the long way to achieve this but came up with pages of pointless code .... I know there is a better way I just dont know enough about VB to do it myself ... And I know this is EASY for many :-)
-------- Cell ranges h11 to as11 are a totals row. If the total is 0, colorindex is set to vbpatternnone, if >= 1, then colorindex is set to vbpatterngray. Easy right ? I just dont kn ow how to do FROM/IF/DO range loops... --------
Details: The code in worksheet_SelectionChange will contain the following:
1: From range h11 to as11, variable1 = application.interior.colorindex of the cell.
2: Check if the cell is >=1 or <=0 ....
3: If >=1 then set application.interior.colorindex = vbpatterngray. Go to #5. ' (This inserts a pattern over the original color of the cell)
4: If <=0 then set application.interior.colorindex = vbpatternNONE ALSO set application.interior.colorindex = variable1 ' (This clears the cell pattern and returns it to original color)
5. Repeat steps to clear cell pattern and restore color / or insert pattern for all cells from range H11:AS11
Cell C7 is a drop down list with 3 possibles (Consumer,Commercial, Public). Depending on which is chosen, I want Cell C10 to provide a different list of possibles (Consumer Products, Commercial Products, Public Products).
I have tried to create a custom IF statement with the list choice & sometimes it refuses & sometimes it gives me the list, but with the formula string as the drop down options.
Is this forumula possible or is it beyond excel's capability?
I am trying to achieve the following in one formula.
IF an amount is greater than 0 and less than 500,000 put in 490 IF an amount is greater than 500,000 and less than 1,000,000 put in 1000 IF an amount is greater than 1,000,000 and less than 4,000,000 put in 2205 IF an amount is greater than 4,000,000 and less than 9,000,000 put in 5000 IF an amount is greater than 9,000,000 and less than 10,000,000 put in 6500 IF an amount is greater than 10,000,000 and less than 15,000,000 put in 7500 IF an amount is greater than 15,000,000 put in 9500
I am using Excel 2007 and the following code is generating an error 1004 (Method 'Range' of object '_Worksheet' failed) on the .Range select line. I am trying to select a bunch of noncontiguous ranges and then format them all at one time.
With Sheet1 .Range("B9:G9,B10:D11,E10:E11,F10:G11,A13:G13,A14:D20,E14:E20,F14:G20,A22:H22,A23:D24,E23:F24, G23:H24,A26:H26,A27:D28,E27:F28,G27:H28,B30:G30,B31:C32,D31:E32,F31:G32,B34:G34,B35:B36,E35:E36,C35:D36,F35: G36,B38,B39:C40,D39:D40,E39:F40,B42:G42,B43:D50,E43:E50,F43:G50,A52:G52,A53:C54,D53:D54,E53:G54, G61:G62,H65:H66,A56:H56,A57:H60,A61:F62,A64:H64,A65:G66").Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With End With
I've written longer lines of code, so I don't think it's a line length issue. Is there a limit to the number of ranges or cells that Excel can select at one time?
I have a workbook filled with many tabs / sheets. Each sheet contains the same structure of information and isn't an important aspect of this question.
I've looked at this topic http://www.mrexcel.com/board2/viewtopic.php?t=16683 and I'm unable to follow it well.
Is there a way to search all the tabs for a word and if it matches to copy the entire row into the search tab / sheet? Specifically for each and every searched match.
It would save my users trying to search each tab individually and possibly miss the data they are actually looking for. Hundreds of people use this reference workbook which stores where all our documentation is stored across numerous servers. Each row is one document and its corresponding storage information.
How would I write out a formula if I currently have this formula/ =COUNTIF(D6:Q6,"a")*8 on cell S6 but I also want to include/ =COUNTIF(D6:Q6,"a-1")*1 and =COUNTIF(D6:Q6,"a-2")*2 and so on till a-7 and i want all this to read on the same cell.
This is my current formula that im using in cell U6
=IF(L6>=500,"Sept 8 2009","Sept 1 2009")
it works fine with just 2 answer cuz if its not one answer is another
=IF(AND($B$2>=0,$B$2>=500),"Sept 8 2009","Sept 1 2009") found this one online but it got frustrating...i change the values to mine
so what im looking for is a if formula or similar to the following
if L6 is between 0-500 i want U6 to say Sept 1 2009 if L6 is between 501-750 i want U6 to say Sept 8 2009 if L6 is between 751-999 i want U6 to say Sept 15 2009 if L6 is between 1000-1200 i want U6 to say Sept 22 2009
i try both if formulas (stated above) both but both return one or more results...i just want one
L6 U6 ***(decimal included) 0.00 than i would get the result Sept 1 2009 253.52 than i would get the result Sept 1 2009 647.36 than i would get the result Sept 8 2009 750.01 than i would get the result Sept 15 2009 1100.00 than i would get the result Sept 22 2009
if above 1200 i would get the result sorry your amount owed is to high
formulas are preferred *but will work with macros and other options
*if using macros or vba or what have you please explain how it will work
I have created a workbook that I store data from my audits, this data is in the form of Y if compliant, N if noncompliant and N/A if not applicable. Where the fun part begins is that each question has a different risk involved. I have used a simple 1 to 5 risk scores and given scores for compliance and non compliance to each score, for example a risk 1 if compliant is 100 points, if non compliant is -100 points, all the N/As are worth 0.
I currently calculate the totals in a different sheet in the work book, but I do this kind of manually, I have calculations to work out the totals and percentages and all that, but I cannot figure out how to get the Ys and Ns to appear in this sheet as 100 or -100. All I do at the moment is bring the Y, N or N/A over with a simple =corresponding cell in sheet 1 then manually change this to the number I require.
I would like to tabulate correct answers on a test given. The data is in excel as student answer (column header), with possible response of 0 or 1. Another column of Answer Key with response of 0 or 1. There are 80 total questions (rows).
I would like to be able to create another column of "Grade" with 0 (incorrect) or 1 (correct) so that I can tabulate % correct responses. I will eventually be analyzing correct responses by question, combining results with other students at same level, etc.
I will likely do further analysis in SAS, but want to get the data set together in Excel.