Return Text Results Based On 2 Criterion
Oct 6, 2008
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 8 Replies
ADVERTISEMENT
Apr 19, 2012
I have a report that shows when our company has shipments covered by inventory (although you are not seeing the conditional formatting in the table). Columns K:Q represent shipment dates and shipment amounts. Columns D:I represent inventory with Column J being the total inventory. The conditional formatting works to highlight the cells in K:O of when shipments are covered by inventory. I need to be able to return the date (Columns K:Q) for an item (Column B) when a shipment is not covered by inventory.
For example Row 154: all shipments are covered by the amount of inventory. A shipment of 3 on 04/24 is more than covered the 105 pieces of inventory. But for Row 170, the shipment for 480 pieces on 04/23/12 does not have enough inventory to cover.
The result I need to return for row 170 is that 480 pieces are due on 04/23/12 that are not covered.
What formula would I used to return that value?
Excel 2010ABCDEFGHIJKLMNOPQ1LineITEMNEXT CUSTOMER PART WIP INVENTORYSTAGINGFINISHEDINSPECTDCYTRAILERTOTALPast4/18/20124/19/20124/20/20124/23/20124/24/20124/25/2012154Tabber105290325AA05290325AA-DC000010501053155Tabber1070025AC-EXP070025AC-
[Code]....
View 2 Replies
View Related
Jul 5, 2013
1) I have an invoice form, and need to create a formula that will allow me to calculate a discount IF an item number begins with "C" or "CE".
Example: Item # is in cell F12. (may or may not begin with "C" or "CE")
Item price is in cell J12.
Extended price (qty x price) is in cell K12.
In L12, I need to calculate a discount (from % in fixed cell L9) on the figure in K12 - based on whether or not the Item # in F12 begins with a C or CE.
If it does not begin with C or CE, then L12 needs to equal 100% of K12.
(The 2 parameters I referred to in the title were:
C, followed by a number
CE followed by a number )
2) The biggest part of the dilemma is that the person using this spreadsheet is totally unfamiliar with Excel formulas, spreadsheets in general, and has to send this finished product to a client each week. So I need this to be as simple as possible - which seems to me to be a formula (that can be copied to insert rows, etc. if necessary).
View 2 Replies
View Related
Nov 21, 2006
I am looking at developing a table that has three possible options for different categories, being Yes, No and N/A. A yes score a certain percentage depending on what column it is, and the use of N/A changes the percentage of other scores. (i.e. A yes in column 5.1 = a score of 5, a yes in 5.2 = 5, a yes in 5.3 = 10 and a yes in 5.4 = 15. However if 5.4 is N/A then this amount is diveded amongst the remaining 3, i.e. 5.1 = 10, etc.)
View 3 Replies
View Related
Dec 1, 2006
Please see the attached sheets.
Here's the issue:
On sheet "master" I have a list of goals in column A.
Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.
I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.
I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.
Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.
how this done for AL, I can go ahead and create the other sheets.
View 9 Replies
View Related
Nov 4, 2008
I want vlookup to return results based on the *** and age of the person from tables in a separate sheet. For eg. if the person is "male" or "female" and if the person is "<=15", "16", or "17". I have been practicising with a formula that looks like this:
View 6 Replies
View Related
Mar 6, 2009
Multilookup based on Multiple criterion without using Arrays
Dear Forum,
I am well aware of the oft-mentioned Array Solution which gives us more than one record based on mulitple criterion using the INDEX function..
the formula layout being as mentioned below:
[ ={INDEX(A2:A100,SMALL(IF((B2:B100=<Condtn 1>)*(C2:C100=<Condtn 2>),ROW(D2:D100)-ROW($D$2)+1),ROW($D$2:$D2))} ]
I like this formula a lot and have used it more often, however it has made my file exceedingly slow..
Considering that it takes every small change in the file to compute around 5 mins or more than this the whole purpose seems to be defeated, I have read a lot of posts on Array Vs Non-Array of late by DonkeyOte.
View 14 Replies
View Related
Apr 26, 2014
I need to filter and clean the criterion based on another column cells.
If the R column has the value 1, then it is filtered and cleaned only the cells of column J
My spreadsheet needs to stay current with the strange layout.
[Code] ....
Attached File : LayOutStranger.xlsx‎
View 3 Replies
View Related
Mar 10, 2014
How to create checked and unchecked checkboxes based on three criterion(in sheet 1)?for clear explanation..it will be like this...firstly,the "SELECT TAKEN only"checkbox will checked/unchecked row d20:r20.secondly,the "SELECT RETURN only" checkbox will checked/unchecked row d21:r21 and so on.lastly,"SELECT ALL" will checked/unchecked all the rows.
Checkbox1.xlsm
View 2 Replies
View Related
May 8, 2006
Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.
I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.
I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.
NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.
Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0
Scenario:
Looking for Numeric Criterion 1 (one).
Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1
In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.
View 14 Replies
View Related
Oct 7, 2013
I've attached a spreadsheet. I want the order of the sentence to change based on the numbers and whether they are positive or negative. Below are more details and the spreadsheet has numbers.
"Total expense for September was 8 higher (lower) than prior year, driven by a higher (lower) claims expense and admin expense"
This might be hard to explain: For the second part (starting from "driven by", I would like the variable that explains more of the positive or negative variance (in the example, claims) to come first.
Here is a different example
Total expense for September was 6 lower than prior year, driven by a lower admin expense (claims is not included because it is higher)
View 8 Replies
View Related
Aug 14, 2013
So basically I have a spreadsheet that tracks if a patient has turned in there required paperwork within the last 6 months and then changes the cell to white and lets me know how many days they have left until they are due to turn this paperwork in again. Then if it has expired I have a condition format change the cell to Red but I would like to add in the text EXPIRED to the cell. lastly I have Cells that have no data in them gray and I would like to add in red text saying No Paperwork.
I'm hoping this will be my last build of this spreadsheet so I can go ahead and start applying it to the real workbook [URL]
View 12 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Oct 15, 2007
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 4 Replies
View Related
Jun 2, 2009
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 5 Replies
View Related
Jan 26, 2010
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 2 Replies
View Related
Nov 8, 2006
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 7 Replies
View Related
May 20, 2007
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 9 Replies
View Related
Jan 22, 2008
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 6 Replies
View Related
Mar 5, 2008
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 4 Replies
View Related
Apr 30, 2009
I have a cell with a %. I want to return text in another cell based on that data i.e. between 90 - 100 = excellent, 75 - 90 = good, or something like that.
View 3 Replies
View Related
Aug 27, 2007
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 9 Replies
View Related
Oct 26, 2007
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 5 Replies
View Related
Dec 18, 2007
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 2 Replies
View Related
Jan 25, 2008
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 7 Replies
View Related
May 2, 2008
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 3 Replies
View Related
Apr 9, 2014
how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I'm using Win 8.1, Office 2013.
View 4 Replies
View Related
Feb 27, 2014
I have two columns in a spreadsheet that I want to use an IF function with. In the first column, I want a pick list containing 5 text options. Depending on which option is chosen, I want a number to auto-populate in the second column.
The numbers are important, because I want to repeat this pair of cells with slightly different text values in the second pair, and then to have a column that multiplies the two resulting numbers and conditional formats the result.
View 14 Replies
View Related
Oct 16, 2006
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 8 Replies
View Related
Apr 8, 2013
Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?
E3a7qta.png
View 1 Replies
View Related
