Return Formula Value/Text Based On Many Cell Conditions
This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses)
Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses
Is there a formula/function that i can use (like the IF Formula) to complete the following information;
EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO"
Column H lists all departments
Column A lists deaprtments
A3 representing the 1st Department needing training
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Formula To Return Text Based On Top X
I need to write a formula that will Display “Big Fish” if a customer is in the top 100 sales or ‘Not that great’ if the customer is not in the top 100 sales for the customer whose CUSTOMERID is equal to my ID. An updated Spreadsheet can be found here [url] OR [url]
View Replies!
View Related
Formula That Will Test Text Conditions In A Single Cell
I need a function that will use a column of text values and test these values to see if one or more of the values exist in a single cell. If it does I need the function to return true or false. Ie. cell A1 contains the text "Jim Smith" the B column contains the test names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim". Because Jim is in the cell A1 I would need the function in C1 to return the value "true". If A1 contained the text "bob smith" then function in C1 would return the value "false".
View Replies!
View Related
Return Data Based On Two Conditions
I have very limited vb experience and I believe it’s the only way I can do what I need to get done. I am working on creating an invoice template. I want the price to populate based on two conditions. The two conditions are invoice line quantity and month. Let me provide data ranges. The user will enter a quantity for each invoice line moving down B17, B18, B19, B20 etc up to 24 lines. (through B40). The user will enter a current date in F13. Away from the main body of the invoice: Range T5 thru T16 will be pre-populated with each month of year 2007. So T5 = 1/1/2007, T6 = 2/1/2007, T7 = 3/1/2007 etc. Range U5 – U16 will be pre-populated with the line price for each month if the line quantity is less than 10 Range V5 – V16 will be pre-populated with the line price for each month if the line quantity is 10 or more The price should autopopulate in G17 - G40 where a quantity is placed in the corresponding column range B17 - B40. The number of lines varies per invoice so I'm setting the template up with sufficient lines. As an example, the price should populate for each invoice line beginning in G17 to correspond to the month entered in F13 and quantity entered in B17 based on the corresponding month in T5-T16 and the price in either U5-U16 or V5-V16 given the quantity. So if the Month in F13 was February and the quantity in B17 was 25, the price that populates G17 would come from V6. If the Month in F13 is March and quantity in B17 is 5, the price that populates G17 would be from U7. I just don’t have enough experience to write this myself.
View Replies!
View Related
Return Result Based On Text In Cell
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities. In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario: Warranty Replacement = 20 Insurance Claim = 20 Billing Issues = 15 Retention Opportunities = 20
View Replies!
View Related
Choose & Return Cell Based On Text In Another
I am attempting to choose a cell containing a text string based on information placed in a raw data area. My output from this formula will be a line of text picked from yet another reference cell, based on each of the four choices. Removed non pertinent information and edited for clarity.
View Replies!
View Related
Return Range Address Across Different Columns Based On Conditions
I am trying to return a cell address range based on values in different columns. Attached is an example. In Column A1 I have "Range" and below that a set of numbers from 1 to 31. In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter. What I want to do is as follows:- I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet. I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.
View Replies!
View Related
Formula: Return Result Based On Other Cell Values
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this: if B5 is >3.01% then b6 =" Market Test Required" if B5 is +3% then b6 =110 if B5 is +2% then b6 =106 if B5 is +1% then b6 =103 if B5 is 0% then b6 =100 if B5 is -1% then b6 =96 if B5 is -2% then b6 =93 if B5 is -3% then b6 =90 if B5 is >-3.01% then b6 ="Market Test Required" So on and so on.... I would really appreciate your help on this issue. I have used excel for some time now but not with complex formulas or any vb.
View Replies!
View Related
Query Worksheet & Return Rows Based On Conditions/Criteria
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows. 1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row. 2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
View Replies!
View Related
Formula To Return The Value In A Column Based On Cell Entry In Row
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case). I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
View Replies!
View Related
Returning A Text Value Based On 2+ Conditions
I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes. A B C E F G M S Get value from G M S 5G M T M T 6M M U M U 7H M V M V 8K
View Replies!
View Related
FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View Replies!
View Related
Mega Formula To Calculate Results Based On Several Conditions
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking. For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05) The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
View Replies!
View Related
Return Text Based On Text Existing In Range
I am trying to Lookup a country to see if it is classified as a "Developed Country". My formula would be in cell AA4. I want to see if the country in cell B4 is on a list of developed country's on another worksheet. IF the country is found on that list cell AA4 displays Developed. If the country is not on the list, cell AA4 displays Emerging. I have tried an IF statement using the Match function and it does not work. =IF(MATCH(B4,'Developed Country List'!$A$2:$A$37,0), "Developed", "Emerging")
View Replies!
View Related
Cell Value Based On Multiple Conditions
I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10. I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.
View Replies!
View Related
Reset Cell Value Based On Two Conditions
I am trying to build a spreadsheet that tallies hours worked and hours available for the next day. The key to this spreadsheet is the number 70. A worker can accumulate no more then 70 hours in an 8 day period, however after any day with no hours worked the rule resets to 70 hours available again. I have attached a sample spreadsheet so you can see how this should work.
View Replies!
View Related
Return A Text Value Based On Criteria
I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too. Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.
View Replies!
View Related
Return Text Based On Criteria
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View Replies!
View Related
Return Lowest Based On Text
I am looking for a formula that will return the lowest value in a range, expressed as a letter. The relative values of these letters are: F=0 P=1 M=2 D=3 For example, If in cells A1:A4 we entered the values F, P, M & D in A5 the result would be F (as it is the lowest value in the A1:A4 range). I have included an example worksheet with fictional data and the intended result in the Final Score column.
View Replies!
View Related
Return Text Based On Lookup Value
I have a database of customers that are all sorted by a customer number. I have a form that has a number of fields that display customer info. When I use a blank of this form and put the customer number in it's correct field, all the other info fields do a vlookup on the customer number in the database file and return the info, such as phone numbers, name, address, etc. Here's my question; How can I check for a blank (using ISBLANK, I assume) to check to see if the customer number exists in our database and, if it's NOT blank, operate on it with the following example which is used to return their insurance expiration date. =IF((VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))<TODAY(),"EXPIRED",VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE)) I realize that this may be a sledgehammer approach and that Access is probably a better tool, but my company has not shelled out the $$ for Access...yet.
View Replies!
View Related
Return Text Based On Another Cells Value
I have 5 options that I am designating as "Allocation Methods" for a number of line items. They are numbered 1-5, but i have custom formatted them so that they have text in the formatting. They are as follows; Allocation Method 1 - Allocation per Hard Cost 2 - Allocation PSF of Proj. or Hotel 3 - Allocation per Loan 4 - Allocation per Parking Spaces 5 - Allocation for Park I will allocate one of the above allocation methods to every line item in an allocation column by hardcoding 1 - 5 in a cell for each line item. What i cannot figure out (or figure out if it is even possible) is how, for instance, can I type in a 1 and it have it automatically formatted per number 1 above & then if I change my mind to method 2 and type 2 it will automatically format per number 2 above. I know I can do this by creating a reference column right beside by allocation column, but I would prefer not to use that method.
View Replies!
View Related
Return Value Based On Last Number In Alphanumeric Text
I want to be able to return a text string of either Trainee 1 or Trainee 2, (or default to other text) based on codes that are 4 digits (DD01, KKB1, KKB2, KKC2, GG03, etc, where starting with K and ending with 1 or 2 is the criteria for TR 1 or TR 2 . There are two many KK** items to list them all in an If statement and I can't seem to use a wildcard. =IF (right(c1,1) = "1", "Trainee 1", B1) gets me halfway there I think but of course doesn`t pick up Trainee 2 and also returns Trainee 1 when it sees DD01.
View Replies!
View Related
Return Text Based On Number Range
I have a range of some 2,000 plus cells that are various numbers in them only. What I want to be able to apply is a macro that will look at the number in a cell and if the result is between a set range, produce a text, eg. If the number is between 0 - 999 then insert text [url] this is where you go'. If the number is between 1000 - 1999 then insert text [url] this is where you go 2' If the number is between 2000 - 2999 then insert text [url] this is where you go 2' etc. I have up to 11 ranges of numbers to insert (up to 10000 - 19999). So the text is the result of the formula.
View Replies!
View Related
Return Text Results Based On 2 Criterion
I am attempting to create a formula that will, return text results based on 2 criterion. This is what my sheet looks like Name Job Full Time / Partime Bob Admin Full Time Barry Accounts Part Time Chris Claims Full Time Emma Claims Full Time The formula I have for this is {=INDEX(A1:A4,MATCH(1,(((B1:B4)="Claims")*((C1:C4)="Full Time")),0))} Which is fine and returns the name Chris, however as you can see from above, Emma also meets the criteria given however, I cannot get Emma to appear also. Is it possible to display more than one entry in a cell/s based on this formula, or am I missing something. I would use a pivot table to do this, however I need the sheet to be completely automated.
View Replies!
View Related
Return Text Based On Calculation Result
I need an excel function for this... Say, Cell A2= 8 Cell A3= 1 If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?
View Replies!
View Related
Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell
I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem: Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it. i.e. (in reference to attachment - sheet DATA) IF E6 = Holiday, then D6 = Red IF E6 = Half Day Holiday, then D6 = Red IF E6 = Sick, then D6 = Blue IF E6 = Sick Half Day, then D6 = Blue IF E6 = Bank Holiday, then D6 = Green IF E6 = Compassionate Leave, then D6 = Pink IF E6 = Unpaid Leave then, D6 = Yellow IF E6 = Unpaid Leave Half Day then, D6 = Yellow However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
View Replies!
View Related
Cell Comments Line Breaks Based On Conditions
I manually enter data into inserted comments in a worksheet. Is it possible to run a macro that formats comments to align based on spaces? Ie Entering "City","State" and "Amount" on row 1 in a comment and entering "Denver", "Colorado" and "5" in the second row would produce those words to align at a predetermined spacing (ie after every space the next word would start 10 characters over) ----------------- City State Amount Denver Colorado 5
View Replies!
View Related
Change Cell Background Color Based On Multiple Conditions
My Excel skills are basic at best, with no knowledge of VBA other than finding the VB Editor. What I'm trying to do: Attached is the file I'm working with, to give you a better idea. When a name is selected in column B, the cells in the non corresponding columns E to N should have a black background color. For example, when you select Mike in column B, the cells in that row in columns I to N, named Dave and Dean, should become black. If you select the name Dave, columns E to H (Mike) and M to N (Dean) should become black. In addition, the background color of the non-black cells should change depending on the status. OK : green FAIL: red N/A: orange Exceptions: blue The attached file shows a few possible results, manually entered.
View Replies!
View Related
Array Formula To Return Text
I have an Array formula that returns a value from several rows below: =MAX(IF($P$8:$AQ$8="Today",$P13:$AQ13)) Row 8 contains only text and only one occurrence of the word "Today". Row 13 has only numbers. The corresponding value directly below the column containing the word "Today" is returned successfully. I now need a similar formula to return a text entry located in the same column but on the previous row (row 12) directly above the value just returned and this row contains only text entries. Using the above formula and referencing $P12:$AQ12 returns 0 as expected.
View Replies!
View Related
Make One Cell Count Twice In AVG If Certain Text Conditions Apply- 2008
I am using Excel to tabulate votes for a contest. Judges have given a number to each entry, and but certain judges' opinions need to count twice as much as other judges' opinions based on their qualifications. I've attached the file to help illustrate what I'm trying to do. Morris's votes need to count twice for all Photography or Web Design entries, and Clark's votes need to count twice for all Graphic Design or Web Design entries. I know I can do this manually by simply copying the number into a blank cell in another column (like the blank column between Morris and Clark's names), but is there any way to make Excel do this for me?
View Replies!
View Related
Formula/Function To Return Multiple Values Based On Criteria
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
View Replies!
View Related
Find Cell Value In Column & Return Text In Adjacent Cell
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30. I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file. I want the user to be able to choose from the list or type the drug name in.
View Replies!
View Related
Return Value Based On Value In Cell
I'm trying to use IF function with multiple statements in Excel 2003. However, whenever I try I get the "The formula you typed contains an error message". I've tried even copying and pasting the example provided in the very Excel help file: =IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) (without changing any value or symbol) in a blank worksheet but even so I get the same error message. I've also tried by copying and pasting examples I've found in different web pages, but the result is the same. However, there's no problem with single statement.
View Replies!
View Related
Return A Value For Two Conditions
I need a formula that will return a value if two conditions are meet. This is probably pretty easy but i am drawing a blank. I have two columns B & C and in those colums there are numbers from 1-5. And in column A there is a number. I need the formula to look in column B&C and if the a condition is meet it will return the number that is in column a A B C 1 2 2 2 1 3 3 3 3 4 2 3 For Example: I need the formula to look in column B&C where the value is 1 for column B and 3 for column C and return the value in column A which would be (2)
View Replies!
View Related
If A Cell Has Certain Text Then Return Value?
How do i write an If function where if a cell has a certain text in it, it would return a value to onather cell. For instance: If cell B1 has "red" in it the cell B4 must be "6" If cell B1 has 'blue" in it, then cell B4 must be "7" and so on with certain text returning a certain value?
View Replies!
View Related
Return A Value Based On Cell Format
I don't even know if this is possible but can you use some formula within Excel to return a value to a cell based on the cell formatting of another cell? Here is specifically what I am trying to do: Cell A2 has a value of 10. If the font color of that cell is black (or "automatic") I want a formula in cell A1 that will return a value of "+10". If the font color of cell A2 is red, I want the formula in cell A1 to return a value of "-10".
View Replies!
View Related
Return Answer Based On Cell Value
im looking to return an answer in cell c28. in b28 it will say "best goalkeeper" in c28 i need a formula to look in range d2:d6 then return the highest answer and correspond that to whats in b2: b6. so we could say (b2)shilton(d2) 33 (b3)corrigan (d3) 55 (b4)parkes (d4) 66 (b5)schmeichel (d5) 100 (b6)cudicini (d6) 14 in c28 it will return the answer schmeichel because he is the highest and in d28 it would return the points he has.
View Replies!
View Related
Return A If All 3 Conditions Met
I have 3 cells, A1 = " a name" A2 = " a time" A3 = " a day ie Thu" I am trying to get a formula to look in B1:B100 for the cells that match A1, then in those cells look in C1:C100 for cells thant match A2 then in the cells that meet both those look in D1:D100 for the ones that match A3. Then if there are any cells that meet all 3 matches return the value "a" if not return a blank.
View Replies!
View Related
Return A Value If Cell Contains Rather Than = A Text String
I want a formula or function that will return a count value if a cell contains a string rather than returning a value if the cell equals a value. For example, =COUNTIF(A$1:A$19703,F1) returns a value to cells in column G if the value in column F equals any exactly matching value in A. I need a formula that works like the filter option "contains" so that if any cells in col A merely contain a string rather than exactly match or equal a string from column F I can get a count value returned.
View Replies!
View Related
Return Text Depending On Another Cell
I have a very long excel sheet. What I want to do is, depending on the input in one column (text), return a another text in another column, for the whole sheet. I tried to with Select Case Statement and if-then, but couldn't figure it out since I don't have much VBA experience.
View Replies!
View Related
|
TRACKER
