Return Text Based On Another Cells Value
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
ADVERTISEMENT
Apr 30, 2014
I have 10 cells in a column. I have a drop down list in each that is the same in each. If all 10 cells have the same item selected from the drop down list, I want a separate cell to list Yes or No. I've tried a few variations with no luck.
View 4 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
May 12, 2012
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
View 1 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
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 23, 2014
I have a worksheet that I want to populate with specific data from another worksheet.
If all of the following are met:
1) Column A equals "763" and
2) Column K is formatted Red (255, 0, 0) from conditional formatting
3) Column P is blank
Then in my blank worksheet I need to display the data from Columns A, B, D, K, O
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
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
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
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
Oct 3, 2006
I am setting up a spreadsheet for a Stock Broker. I need to have a cell produce a value dependent on the value of another cell. Specifically, I need the calculation cell to change depending on whether another cell has the letter A, S, Q, or M. I am calculating the value of investment income based on the Payment terms (A;annual, S;semiannual, Q;quarterly, M;monthly) So if for example Cell A7=A, then Cell C7 =”specified formula for Annual income”
View 5 Replies
View Related
Dec 15, 2007
The data attached shows the length of individual fixations (A4:A8) and whether the fixation was ON (=1) or OFF (=0) a specific target (C4:C8). I want to know how much time was spent ON target for each 250ms time bin (i.e. between 0-250ms, then 250-500ms etc).
My problem is that each fixation is a different length and may cross over the time bin boundaries. IF(AND(THEN ) works for the first few time bins but I get stuck once the first fixation is accounted for. I have filled the rest in manually so you can get an idea of what I want it to do.
View 3 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
Mar 26, 2014
So I've been trying to set up a table.
The table is made of 4 columns, with several times the same values. Each column has a specific range of value. No possible combination can exist twice. Meaning I won't have two rows that are identical. It looks like :
A1@X
A1@Y
A1&X
A2&X
B3#K
B3#O
B3£K
B4£K
C5$P
C5$M
C5*P
C6*P
The user tells me the value he wants in the 3 first columns, and I want to tell him what's in the 4th one.
View 3 Replies
View Related
Oct 14, 2008
I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12).
What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400.
Is there such a formula or do I have to enter manually the amounts into an If function?
View 11 Replies
View Related
May 12, 2014
I want to search within cells A4:F25 for "B71 - Brown" and show the number that is in the cell below this text. If there is multiple cells with "B71 - Brown" I would like the cell to show the total.
So for example,If the formula was looking for "B71 - Brown" in the cell it would show 8136. (A13+A21+D25+E17). If i changed it to "Lavender" it would show 2380. (C5)
Also this data is linked from several other sheets and the values i search for will change and will need to update.
View 1 Replies
View Related
Sep 14, 2007
IF there is a whole number in either column a, b or c, I need to have column e reflect that with the insertion of a / for each column that has a whole number. i.e.
Column - A - B - C - D - E
3 4 / /
5 6 9 / / /
I am attaching a copy of the spreadsheet showing the examples, I can't seem to get the function to work.
View 8 Replies
View Related
Jul 10, 2014
I have three cells - A1, B1, C1 and each contains a value of yes or no.
If more than two of the cells say "yes", I want the fourth cell (D1) to display "yes."
If less than two columns say "yes", I want the fourth cell (D1) to display "no" .
View 3 Replies
View Related
Feb 9, 2012
I am trying to search text cells to return a word count within a particular row of cells and I am currently using the following formula:
=COUNTIF($D4669:$EI4669,$O$3), where cell o3 contains the word to search and $D4669:$EI4669 the data.
However, this formula misses data that contains characters such as "," etc.
View 9 Replies
View Related
Feb 22, 2008
I have a if function that i am creating in excel 2000, and it has 8 variables. I am trying to put it in a macro as it is not working as a nested if function. Here is the current if function...
=IF(B2=C2,"OK",IF(B2=D2,"OK",IF(B2=E2,"OK",IF(B2=F2,"OK",IF(C2=0,"OK",IF(D2=0,"OK",IF(E2=0, "OK", IF(F2=0, "OK", "INCORRECT"))))))
the excel file is attached for further clarity
View 3 Replies
View Related
Jan 29, 2010
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying:
Sub usstates()
Dim R As Range, s As String
Set R = Sheets(1).Range("J1:J50000")
For i = 5 To R.Rows.Count
X = UCase(R.Cells(10, 1))
If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then
R.Cells(i, 2) = "SC"
End If
Next i
End Sub.................
View 9 Replies
View Related