IF Function (few Words Shown Based On Figures In Specific Cells)
I want to have a few words shown based on figures in specific cells. I not quite sure how to explain, but i will try my best:
A1 = 5
A2 = 0
A3 = 7
The function i am using looks something like this:-
=IF(A1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
However, by using this function, if the cell is empty it still shows the first wording when i need it to show lets say between 1 & 6 shows the first wording and more than 6 shows the second wording! If you know what i mean... I know what i want to do but cant really explain it very well.
Maybe it should look something like this but it does not work the way i am doing it.
=IF(E156>1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
First IF To Be Ignored If Specific Column Contains Any Of The Words Shown
=IF((P3<$Q$1),E3-S3,IF((G3="B"),"Not Changed",IF((G3="MT"),"Not Changed","Not Changed"))) Above is the formula I'm trying to get to work but it's not-a-happenin. It seems like the first IF overides all of the others. Basically I want the first IF to be ignored if column G contains any of the words shown. So basically what I think is happening is that P3 is less than Q1 so it's ignoring the rest of the IFs. Originally my formula worked fine as: =IF(P4<$Q$1,E4-S4,"Not Changed") But I realized that i needed a certain category of product ignored. Products are labeled in column G.
View Replies!
View Related
Function To Return Common Words To 2 Cells
I'm trying to write UDF which getting RegEx pattern and a certain cell as arguments and returns only matching string. For examples for string "The quick brown fox jumps over the lazy dog", and RegEx pattern "w{4}" the function will return two words "OVER" and "LAZY". What should I change in my code? Function GetPattern(myPattern As String, myString As String) Dim regEx As RegExp Dim Matches As Object Set regEx = CreateObject("VBScript.RegExp") With regEx .Pattern = myPattern .IgnoreCase = True End With GetPattern = regEx.Replace(myString, "$1") End Function
View Replies!
View Related
Totals Based On Monthly Figures From Last Year
I need to make a sheet that give totals based on monthly figures from last year. Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75. One month Last year the store made 31.82% on it’s money. The bonus for anything over is $75 per 10% increments. Ie 0.00% $400 0.10% $475 0.20% $550 0.30% $625 0.40% $700 So this month was over last months, 37.18% so the bonus was $3,975. What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%. This hurt my head trying to get it right and im new to this more complicated formulas.
View Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell
Basically a cell might have a number with 3 decimal places For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell. Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items. I included an example of what i mean in this excel file which should clear up any confusion.
View Replies!
View Related
Searching For Specific Words
I have 1000 diff names in a spread sheet and wish to search another spread sheet with these names on and alot more. I want the search to look in the larger spread sheet for these 1000 names and copy all the informtaion in the cells to a new sheet, if the name is not found i would like something like "no info found" to be seen. Im a novice to doing this sort of thing in excel, but i have found this script which searches for the word "mail box" and copies all the info in the cell when it finds it.
View Replies!
View Related
Delete Rows That Contain Specific Words
how to create one macro: I have a spreadsheet with 8 columns with various text headings. Each column has different data (some show dates, some show only figures, some show text). Specifically, column E (entitled "Sub Item Reference") has various text entries (e.g. "Newsletter" or "Booklet"). I am hoping to find a simple macro that will search column E and delete any rows where that column contains the text "Cover" or "Label". I'm sure it's the simplest thing going but I can't seem to work it out.
View Replies!
View Related
Extract Words Function
I am using the extract words function outline here [url] I copied and pasted the function into a new sub and deleted the sub module1 and the end sub to make sure it is it own function I can call. I have called it in another macro I wrote and when I run the macro it bugs out at the first if statement. The code it has a problem with the Get_Word code below. It throws a "Run-time error: '1004': unable to get the Find property of the WorksheetFunction class."...
View Replies!
View Related
If Function To Return No. Based On 2 Cells?
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 Replies!
View Related
Find Words Through Lookup Function
I have an issue with VLOOKUP. By runnig VLOOup we can get the data that there is present in a specified range. But how about to get the data DISPLAYED which is not there in the specified range. I tried combining IF and VLOOKUP functions. I am on a simple project now and I would be happy if I could get the answer immeidately
View Replies!
View Related
Lookup Function Based On Multiple Cells
in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results? i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array. the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned .... kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left) here is the formula: {=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))} "data to retrieve" is an array of one column containing the data you want to retrieve "cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted) "table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value
View Replies!
View Related
Split Function: Multiple Spaces Between Two Words
I am trying to parse a formatted text file. I am using the SPLIT function as arr() = split(cline," ") where cline is an input line. if the line input(cline) has multiple spaces between two words, how would it split that. Eg : abcd defg fefdcs fasdfasdsa would the output of above be arr(0) = abcd arr(1) = defg arr(2) = fefdcs arr(3) = fasdfasdsa
View Replies!
View Related
Add Cells In One Column Based On Specific Cells In Another Column
I would like to write a macro to add cells in columns C,D,E,F,G & H based on the text (employee) entered in column B. For example I need it to add all the cells for "John S" found within column "C" and then total it at the bottom with a code JSSS (John S straight shop time) or JSOS (John S overtime shop) as per my example. The attatchment is a spreadsheet I use to total billable hours for our employees.
View Replies!
View Related
Amendments In Function Convert Digits To Words (US Dollar)
Before I got this Function from your Form to convert digit to words. Function is as following..right now i m getting as following:- If i type 123..i m getting one Hundred twenty Three with this script as below. But i want US Dollar one Hundred Twenty Three.. Can any one add the word "US Dollar" in this script. Script.. Function English(ByVal N As Currency) As String Const Thousand = 1000@ Const Million = Thousand * Thousand Const Billion = Thousand * Million Const Trillion = Thousand * Billion
View Replies!
View Related
Specific Cells Populate With Specific Numbers When A Value Within A Range Is Entered
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View Replies!
View Related
Sum Up Cells Containing Certain Words
im currently wonderin if the SUMIF function can have a WHERE syntax? because i want to add up cells which contain these: A B 1 VC-ON US 2 2 MC-ON US 5 3 MC-OFF US (STD) 8 4 VC-OFF US (PREM) 9 in another separate cell, i want to retrieve the sum of numbers with the words ON US in column A. so the final result will be 7.
View Replies!
View Related
Seperating Words To Different Cells
I have hundreds of file names, and to cut a long explanation short, they are exported as a CSV file to excel. In order indentify the owner of the file I need to rename the file with a commar where you see the dash or the underscore in order to have the name appear in the next cell, as can be seen with Syma and Kevin below, so I can then sort the columns etc etc. I know there is a formula that I can use to achieve this, ie have syma.pdf and kevinc.pdf in the next cell. I think it works on identifying how many characters along the name and then send i to the next cell. as you can see below, there is no set amount of characters to put into a formula. Could I say, for example, if after dash /underscore put next word in next cell? t5 mon a2 w6-syma-0003.pdft5 mon cst w2_dellwynneh.pdft5 mon a23 w3_MASUMS.pdft5 mon cmn201a w1- syma.pdft5 thurs wiabe w2_kevinc.pdft5 thurs wiabe2 w2_kevinc.pdf
View Replies!
View Related
Extract Certain Words From Cells
i have a column where the sponsor has a name of a race and i wish to remove the sponsors name and replace it with something of the sentence for example Barton And Guestier Top Novices' Hurdle Class A Grade 2 i wish to just locate the Novices' Hurdle part and change that particual cell to nov hurdle without the sponsors name is it easy to extract this info
View Replies!
View Related
Clearing Cells Containing Specified Words
I am trying to clear the contents of cells in a very large spread sheet containing certain words. I am running the following macro and it will only do the first part, so it will clear cells containing "deceased" but stops at "esa". There may be any number of other words or text in the cell and I want it to delete cells with "esa" by itself so for ex. not delete cell containing "vanesa". I am very amateur so I am sure this is something simple but I cannot find the answer or figure it out for myself. Sub DeleteAll() Dim lCount As Long Dim rFoundCell As Range Dim rLookRange As Range Set rFoundCell = Range("A1") Set rLookRange = ActiveSheet.UsedRange For lCount = 1 To WorksheetFunction. CountIf(rLookRange, "*deceased") Set rFoundCell = rLookRange.Find(What:="deceased", After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) rFoundCell.ClearContents.......................
View Replies!
View Related
Auto-calculate 2 Column That Contains Numbers And Words Words
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples: Table 1 - Before totaling up: Quantity Product 5 2 x Button A White 3 4 x Button B Pink 4 5 x Ribbon A Black 2 3 x Thread A White 6 2 x Cloth A Blue Table 2 - After totaling up: Quantity Product 10 Button A White 12 Button B Pink 20 Ribbon A Black 6 Thread A White 12 Cloth A Blue I need to have the sum of the "Quantity" multiply "Product". Or in short A x B. And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
View Replies!
View Related
Split Cells With 2 Words Into 2 Cells
My data looks like this: Mindy Moore Mace Kook Cow Moon These names are in Column A. I want to split the first and last name into Column A and Column B of my spreadsheet. I Usually copy row 1 to a txt file then import with "space" as my delimiter. I basically want an automated method of doing this.
View Replies!
View Related
Split Words In One Cell To Many Cells
I have some data that is imported, it's about 25,000-30,000 characters and 2,300- 3,500 words, it all comes in as one cell, A1, if I do a text to column on it I will loose everything past column IV. Is there anyway a macro can take the data and put each word in a cell by its self starting in A2? The words a have a space between them.
View Replies!
View Related
Restrict Cells To List Of Words
DATA VALIDATION IN MY CODE Below is my code which looks at Table1 and converts the cell to the appropriate colour when the cell equals the Case What i would like to do is also have the Case be Data Validation anything else error Private Sub Worksheet_Change(ByVal Target As Range) Dim rRow As Range Dim icolor As Integer Dim ifont As Integer With Application .CellDragAndDrop = False .CutCopyMode = False End With
View Replies!
View Related
Autosplit Two Words From One Cell Into Two Cells
I have database of customers and one collum is labeled "Name and Family name". I want excel to split this records in two collums, ergo one labeled Name, the other one Family name. Note: there are also some names like Furio Alipo Novanta, so in that case I want excel to keep Furio in first cell, and move other stuff next to it.
View Replies!
View Related
Formula To Count Words In Cells
I need a a formula that will count the number of times a word or phrase appears in a column of cells and assign a number For example.... Big Red Ford Truck = 1 Ford Truck = 3 Red Ford Truck = 2 After i get the formula, I can sort by "greater than 2"
View Replies!
View Related
Searching Text In Cells For Words
I have a column that is filled with text of varying lengths and I'd like to search through each cell in that column looking for a specific word or words. Unfortunately the length of the text varys greatly between each cell and there is very little uniformity so I need to be able to search through the entire entry in each cell and then highlight that row if a specific word or words are found. I'd also like to be able to add a number "1" in another column on the same row if the search finds a word or words. Any help would be greatly appreciated. If the cell only contains the exact word or string I'm looking for then it's easy but I can't figure out how to search through text in a cell that contains more than I'm looking for. example: Lets say I'm loooking for "caught fire" column Q contains: 1 "The computer caught fire after several hours" 2 "A house on the hill caught fire" If "caught fire" exists in the cell being checked, then highlight the row and put a 1 in a specific column, lets say J.
View Replies!
View Related
Search Cells For Parts Of Words
I am looking to search a column of data (text) and have copy relevant cells to another spreadsheet. Example: Enter "Cat" in a userform, and the program would copy all of the following rows to a new spreadsheet: Cat cat your cat the cat 123 cat cat123 catters CAT Think you guys may get the picture...if cat appears in any way in the cell, I would like that cells contents on a new spreadsheet.
View Replies!
View Related
Extracting Words In Separate Cells
I need a formula which would from one cell extract the words in separate cells. I have probelms because the words in the sentence in the starting cell are not separate by spaces but dots. For example: How.do.I.extract.words.from.this.sentence?
View Replies!
View Related
Find Words - Deleting The Words - And Deleting Some Info After The Words.
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it. I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
View Replies!
View Related
Move Text Between Certain Words To Adjacent Cells
how to cut a string from text and copy it to another cell: Example of a TEXT:.......................... This is Text in one cell - I need to cut the BOLD part. It always ( if appears ) is after statement <b>Kod Producenta</b>: and before <br/> It's not always in the same place. but always between statment above. "<b>Kod Producenta</b>: STRING_THAT_NEED_TO_BE_COPIED_TO_DIFFERENT_CELL <br/>" how can i do it with VBA macro? with cpp would be so much easier but still i need to do this in VBA...
View Replies!
View Related
Count Words In Range Mixed In With Other Words
I have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".
View Replies!
View Related
Combining List Of Words Into New Words, Conjugation
I have 4-5 separate lists of words/letters and I would like to combine them in a certain order but creating all possible combinations resulting a new word. It's like a conjugation tool for verbs. For example if List 1 = be/el/ki/meg; List 2= m List3=e/é; List4= gy/" "; List 5=" "/ek/sz/nnek So the output has to be (if the order is List1+List2+List3+List4+List5)= bemegy/bemegyek/bemegysz/bemesz/bemész/elmegy/elmennek/ ..... and all possible combinations. Preferentially I would like to automate it in a way that it straight outputs into MS Access. Also can it be automated that it reads the list from a file (database)? (Also can it recognize the word by letters? Because the basic word that I would load the program= List2+List3+List4)
View Replies!
View Related
Auto Find Words And Delete From Selected Cells
it's possible to select a group of cells, or a column, and then have specific words (and the colon), like Email: Tel: Fax: and URL: found and removed? I've got a list of contacts one after the other in a column like this John Accountant 456, Hillside Drive, Hamley Tel: 09189374884 Fax: 0284764654 Email: john@email.om URL: ww.johntheaccountant.com The number of lines per contact isn't always the same. Can it be done without Macros?
View Replies!
View Related
Macro To Add 2 Blanks Cells Below Cell With Words
REPLACE ENGINE OIL & FILTER, VEHICLE INSPECTION, FILL FLUIDS RESET THE OIL REPLACEMENT REMINDER LIGHT. (blank row ) (blank row ) E I have an excel file with many columns containing information at the end of each column like this. It lists car verbage, then 2 blank cells are required , followed by an E. I was wondering if a macro could be used to where it looks at a column, finds where the verbage ends, and automatically inserts 2 blank cells below it followed by an E. In other words it needs to make sure only 2 blank cells are after the verbage and an e should follow in the cell below. If the column has 3 blank cells below the verbage it should fix it and add the E at the appropriate space.
View Replies!
View Related
Find Common Words In Cells And Create New List
I have a spreadsheet of part #'s, descriptions, manufacturer names, and manufacturer part #'s. (It's a list of the inventory in my warehouse). Each row contains information for just the item in that row. Row 2 references another part in my warehouse, row 3 yet another, and so on. Many of the parts have more than one potential manufacturer and part #, (meaning that any of those manufacturer's part #'s are basically the same tool; just different brands. At one time we may get a shipment of one, at other times we may get a shipment of another). For example, a screwdriver may be listed like this: Part # 1234 screwdriver, mfg Snap-On, part # 456, mfg Stanley, part # 789, mfg Mac Tool, part # 439. Then further down the list, there may be another part listed like this: Part # 9980 wrench, mfg Stanley, part #741, mfg Snap-On, part # 852, mfg Proto, part # 369. If you can imagine that data across the cells of a spreadsheet row, notice how the mfg name 'Snap-On' was the first mfg name on the screwdriver, but it was listed as the 2nd mfg name on the wrench. So, here's my question: I want to be able to group all of the items made by any one manufacturer together in a new list. If all of the manufacturer names were in the same column, I could simply sort the list by that column, but since I've got thousands of rows with the mfg name I'm looking for in different columns on different rows, I thought maybe a macro could search each row for the word I'm looking for, then if found, take the whole row and copy it to a new worksheet. So the end result would be, If I wanted to see all items of which Snap-On is an acceptable supplier, I could get a list of all potential Snap-On items grouped together. I'm sorry this is so long. I may have over-worded this and it may not be too clear. I could email an example of the spreadsheet if anyone needed more info to figure out what I'm looking for and was willing to take a look at it.
View Replies!
View Related
Flag Partial Match Where Cells Contain Key Words
I have a list of customer names. These include one-off customers and companies. Unfortunately, they are not always keyed correctly and in the same format. What I need to be able to do is flag any companies. These will be those that contain key words, such as Ltd, Ltd., Limited, PLC, & Co, & Sons and so on. My customer list may look like this: 1. Bill Jones & Son 2. Mr B Smith 3. Posh Homes Ltd 4. Posh Homes Limited 5. Mr A Singh 6. Bill Jones & Sons I then need to flag no.'s 1, 3, 4 and 6 as Companies while 2 and 5 are flagged as non-company.
View Replies!
View Related
|
TRACKER
