IF Function Based Upon Text
Jul 21, 2009
The following is my existing formula (I admit it is probably very far from what it should be) but I'm looking a way to base the conditional part of a function based on the text within a cell. =IF(E4=OR("WordA","WordB"),(I4),(-1*I4))
There are four words that I use to classify the column "E" for sake of simplicity I will name them as "WordA" "WordB" "WordC" and "WordD". Those words serve as the identifier that I am trying to test. I.E. if "WordA" or "WordB" are present in column E, I want the end result (listed in column I) to be a positive number. If "WordC" or "WordD" are present in column E, I want the end result listed in column I to be a negative number. Currently I have a formula in column I, which is: =((H3-G3)/G3).
Both Column H and G have numerical values. Essentially the whole goal of my process is that if WordA or WordB show up in E, then I want my formula in I to remain "as-is". If however, WordC or WordD are present, I want the end result or formula in column I to be negative.
I am not tied down or restricted to using a particular formula, but just don't know how to set it up either way. Additionally, I plan to copy the formula down in terms of numbers... i.e. I5,I6,I7, etc. so when submitting any advice or if supplying a formula to paste.
View 3 Replies
ADVERTISEMENT
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
Apr 22, 2006
I am using the following code.
Function VLOOKUPRow()
Dim SV As Object
Worksheets("Midterm").Select
SV = "UserFormAppend.TextBoxInvID.Text"
VRow = Columns(1). Find(What:="SV", After:= Cells(1, 1), _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True).Row
MsgBox (VRow)
End Function
I am trying to give the VRow variable the value or the row that The text from my text box is on. How do you make the what look for a variable. I can get it to work if i put a constent in the what. how to make the what look for a variable. I'm new at this forum thing so im not quite sure how to check for answers.
View 2 Replies
View Related
Oct 14, 2008
1. Search an excel sheet "column" for a particular type of text and insert values based on that text (if found) in another column.
e.g I have column A1 to A10 with different types of text. I would like to search for the keyword "Risk is high" OR "Risk=High" for each cell in the column and insert a "1" if found beside it's corresponding "B" column. If not found, I would like to insert a "0".
So, if the text "Risk is high" OR "Risk=High" was only found in A6, I would like B6 to be "1". Rest of the values in the B column would be "0's", since the text was not found in any of the other cells.
View 10 Replies
View Related
Dec 8, 2009
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View 6 Replies
View Related
Mar 28, 2014
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
See attachment : Example for forum.xlsx
View 3 Replies
View Related
Jun 29, 2014
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
View 7 Replies
View Related
Mar 22, 2014
Programming Excel VBA Macro to do OCR (text recognition) from a prt scr screen capture image and input the text into cells. Currently my Excel file has a push-button, and upon clicking on it the macro pastes into Excel the current clipboard image I have created by pressing prt scr while in another program. The macro then crops the image to the region with the applicable text. I have to then manually type the text I see in image format into the appropriate cells.
the VBA coding to automate this? I'd like it to use the clipboard image and run it through OneNote OCR, after which the applicable text values are automatically entered into the cells. Ideally the code will first crop to the region with the desired text before it does OCR. If this is not feasible, it will need to incorporate a method (keyword search?) to hone in on the desired text after the entire prt scr image has been OCRed.
View 8 Replies
View Related
Dec 12, 2012
If the user selects French (radio button selection), I want to swap out the existing English text for French and vice versa. One large text box with several paragraphs. Can Excel do this?
View 2 Replies
View Related
Dec 16, 2009
I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.
For example
Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"
I am using MS excel 2007.
View 9 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
Feb 23, 2009
I'm trying to use an IF function to evaluate a cell containing a text string. The string is two words with a space; "Turned ON" or "Turned OFF". The formula I'm using is "IF(D15=("Turned ON"),1,0)". It always returns 0 as the result. If I eliminate the space it works fine, but I would have thousands of entries to correct. Any thoughts?
View 8 Replies
View Related
Dec 5, 2013
I have an userform with 10 txtBoxes that I want to have the color changed based off another textbox Values (they will side by side, so when the user enter a value, it will know either it's excelent, good, average or bad based on the colors green, blue, yellow and red).
Is there a way to do so? Because all I have managed to do is to change one txtBox color per time, based on the value entered on the same text box (i.e. if I change the text box which should be changed, nothing changes).
View 9 Replies
View Related
Jul 19, 2014
I need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.
I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.
Example
What I need
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
A-123 bca loreal skin care anti acne anto acne soap
Main file
Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales
y-123 abc lux 120 110 105
A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx
Category files
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
View 1 Replies
View Related
Oct 16, 2006
I have been working on this worksheet part of which is attached herewith. I would like excel to automatically enter Updated/Inserted in column B against Individuals' names as per the instructions given in column A. For example: As per instruction in A9, B13:B16 should show Updated. I have tried to use the nested if function, but it does not work as I want it to. Also as I am not used to macros or VBA codes, could this problem be solved with formulas?
View 2 Replies
View Related
Nov 21, 2008
I can't get the text box function to work on this spreadsheet.
View 2 Replies
View Related
Apr 1, 2009
I have a spredsheet with multiple Alpha Numeric codes in one cell. I would like to seperate the codes but instead of placing them in the adjacent columns, like the text to columns function does, I want them to go to the preceding rows.
View 3 Replies
View Related
Jun 23, 2009
In column A i will be entering Jobs in the format : 'Customer1 Job4', 'Customer 5 Job 4'. In column B i would liek to identify the jobs automatically, so a formula that goes something like : IF(colum A contains "Job 2", "Job 2",If (Column A contains "Job 3","Job 3","")). There are only about 6 jobs but there could be many customers.
View 4 Replies
View Related
Jan 28, 2010
I have a list of items with a cell, however these are on seperate lines (using Alt+Ent) function. Example of entered text within the cell below:
_UN
_OD
_PN
_H
The beginning of each line will always start with an underscore ( _ ). The items within the list will either be 2 or 3 characters long (which includes the underscore).
The required output I'd like is (spaces used to indicate seperate cells):
_UN _OD _PN _H
I'm trying to use the 'Text to Column' function to solve my problem, however I haven't yet managed to get it to work. I've tried using the 'Fixed Width' function within this, however when I use this, it inserts an 'Enter' within the cell, which I don't want.
Does anyone else have a solution? Any help would be appreciated. Preferably I'd like this to be automatic using a formula, instead of me having to click the 'Text to Column' button each time.
(I'm using Excel 2007 if this makes any difference too)
View 12 Replies
View Related
Jan 23, 2014
This formula seems to work fine for the first two logical tests, but when it come to adding the third, I get an error.
=IF(E5="Hourly",C5*D5,IF(E5="Daily",(C5*D5)*8,IF(E5="Weekly",(C5*D5)*32,''"))
View 4 Replies
View Related
Dec 5, 2005
In column A there is a date and time function in the format as follows :
dd/mm/yy hh:mm:ss most of the time.
When this is downloaded it is easy to manuplite. But some times it show as
above but as a text string or sometimes the dd/mm is switched to mm/dd.
Does anyone know the work rounds these formulas?
I use the dd/mm and Hr function to complie reports from column A but usually
spend hours using mid() left() function due to the date change.
I have tried formating the cell but this does not work?
View 10 Replies
View Related
Jun 7, 2006
I think there's a function that compares two text values and determines if
they are "similar." For instance ABC would be considered a match with ZABC.
Is anyone familiar with this function?
View 10 Replies
View Related
Oct 22, 2008
In a single cell, I want to say "Report Complete as of" and then the current date. Is there a way to use & the TODAY function that will return TODAY as a date in conjunction with the text? I keep getting "Report Complete as of 39743" instead of "Report Complete as of 10/22/08", for example, and I can't seem to convert the numbers to a date.
View 2 Replies
View Related
Jan 2, 2009
I need to find a way to take the TODAY() function and and split the individual digits out and recombine them to a text cell, i have tried using the MID function but it returns the serial rather than the actual number, e.g
today() 02/01/2009 needs to be shown as 020108 (this needs to be text rather than a number) as it is included within an INDIRECT formula.
View 4 Replies
View Related
Dec 21, 2009
I am trying to use the "MAX" function to find the max temp on a particular day in a week from 7 separate sheets. The problem I have is one particular company autopopulates their temperature block with the temp and degree symbol: 46°F instead of just 46. The max function is thrown off it appears by the symbol and letter. Is there a way to get the max function to only look at the numbers? I'm also open to the option of autoconverting each temp to a number on the master sheet and letting the "MAX" function search through those if that's possible.
View 10 Replies
View Related
May 8, 2008
I have a cell with a text string >3, and would like it to return the number (not text) 3. I could use Right but since the number will not only be single digit I don't think this will work.
View 9 Replies
View Related
Sep 17, 2008
I've been looking for a while on here now and have found some great formulas, but can't seem to get this 100% figured out. Here is what I want to do. My master sheet contains a cell (A5) that is used as a keyword search. So if I wanted to find info using my name I would enter gsheppar in A5. I have another sheet labeled "List" where there are different categories i.e. A1 is labeled Online Retailers, A2:A51 contains a list of the retailers. In B2:B51 I would enter my name (gsheppar) if I had an association with the retailer.
On the master sheet when I enter my name I would like it to show all retailers in B5:B55 that has my name next to it in B2:B51 from the "List" sheet. The problem I am running into for example B2 on the list sheet has my name plus 3 other people's names formatted: gsheppar,name 2, name 3. The question I have is how to have the formula in B2:B51 on the master sheet look specifically for the name that is in the keyword search when there are multiple names in a single cell on the "list" sheet.
View 9 Replies
View Related
Dec 13, 2006
I have a multipage control on a userform. On Page 8 of that multipage, I have label1, part of that label is modified by other parts of the program. This is done by the program writing to a sheet of Data and then the program pulls that info into this label. My question is,what is the correct format to populate the label?
see below: This is what I have in my Userform_Activate and Userform_Initialize subs:
CurrentVersion = Sheets("Data"). Range("N2").Value
Label1.Caption = "This will allow you to change the Caption of the UserForms through out the operational program. Some UserForms cannot be changed. The ending of" + Chr(32) + Chr(34) + "Automatic 2005 V" + Chr(32) + CurrentVersion + Chr(34) + Chr(32) + " will be added to what you enter below as default."
CurrentVersion is pulled off of the datasheet which is a number like 5.9.8.2. however it errors "Run time error 13 - type mismatch" I can only suspect the label1.Caption needs something like: Multipage8.label1.caption =
View 2 Replies
View Related
May 20, 2014
I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:
=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")
However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.
It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))
I think it is trying to continue doing something with the text function
View 3 Replies
View Related
May 3, 2007
I have a problem when using the function of text(). I set "=TEXT(SUM(A1:A5), 0) + 5" in Cell A6. Assume the answer of SUM(A1:A5) is 10, the result in A6 will be 15.
My question is how to prohibit a string value [Text(SUM(A1:A5),0)] add a number value [5] to form the result 15.....
View 11 Replies
View Related
