I have the following datas as an example;
A1: %90
A2: %100
A3: %75
According to those datas I want to get the following results;
B1: On going
B2: Done
B3: On going
So basically I want to tag columns which are equal to 100% as "Done", and the rest is as " On going".
I'm trying to count if there are situations where column a is equal to a specific number and column b is equal to another number.
both columns are numbers. Does anybody have a good idea how to do this, maybe countif, but i am not sure how to do more than one item with that.
I can assign a number of copies to print by adjusting ActiveWindow.SelectedSheets.PrintOut Copies:= , but what I want it to have the number of copies auto adjust to the value of a cell that contains a formula. I was hoping it would something as simple as Copies:=cell but no luck...
View 2 Replies View RelatedI need to count the number of equal cells in col D beginning at the top of the column. The counted cells must begin with a text prefix of "Category:" without the quotes.
Some but not all of the cells in col D begin with a prefix of "Category:" without the quotes, followed by a word or words following the word "Category:" See examples below. All of the terms prefixed with "Category:" in col D are in alphabetical order. I need to count the number of identical cells in col D with the "Category:" prefix.
Examples of the contents of cells in col D with the "Category:" prefix are as follows:
Category: Adversity
Category: Answers
Category: Assurance
Category: Blessings
Category: Build
Category: Change
Category: Children
Category: Choices
Cells above and below cells with a prefix of "Category:" in col D are not adjacent.Cells above and below cells with a prefix of "Category:" in col D are separated by 3 to an undermined number of rows.
I need to count the number of equal cells in col D and insert the count in col A at the last equal term. For example, col A above would have 93, 1, 1, 5, 10, 8, 3, and 12 inserted into col A.
I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.
For example:
- All of my data is in column A
-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.
I can't seem to make user-defined format that puts a text in front of a number and/or a text.
Let's say I have A1: 13, A2: texttext A3: text7 and I want to format a lot of cells to "Ilike 13" / "Ilike texttext" / "Ilike text7"... ie add the same text in the front of the cell, no matter what the content is.
I did manage it seperately, with "texttext" @ for text and "texttext" # for numbers, but what's the general one?
I've create an userform with 2 textbox and a command button.
The user is allow to paste an article into textbox1, when they clicked the button, it should be able to find a specific text string in the article, then right after that text I want to insert addition note and a new article with notes will be generated in textbox2.
Currently i stuck on how to insert the note after the specific text string?
I have a cell (B4)that may contain several different text values, i.e. Bill, Tom, John, Mike, Larry, & Dan. The value in cell D4 needs to relate to what text is in B4, i.e Bill = 6; Tom = 12; John = 8; Mike = 20; Larry = 15; & Dan = 10. I was trying an IF statement, but having trouble.
View 2 Replies View RelatedI have a text field which contains multiple numbers ( reference #s and phone #s). I need to extract the reference # which will be either a 7 or 8 digit number. That number will not fall in any particular place in the string.
View 9 Replies View RelatedI've got a single column worksheet with a varying numbers of characters in each row.
At the end of each row's cell value I must add a 5 char string. Preceding that string I must have enough spaces to make the total length of each row 106 characters.
I've already:Defined and populated the string. Let's call it "strMyString".Established how many rows are in the sheet and stored it in "lngUsedRange"Written the following which cycles through each row establishing how many spaces need to be added:
Dim lngSpacesNeeded As Long
For i = 1 To lngUsedRange
lngSpacesNeeded = 101 - Len(Range("A" & i))
Next i
Now I just need to know what else to put before "Next i" to locate the end of the existing text in the cell and add the number of spaces in "lngSpacesNeeded" then add "strMyString".
I'm trying to do this in VBA.
I open a text file for input, and I want to jump to a certain line # in that text file. The difficult part is that each line has variable length, so I can't use the Seek function.
The data look like this:
1,2,3,4
555,666,777,888
99,00,11,22
... etc.
I am looking for a formula to return the column number of the array when a specific text is found, in this case the text is "Yes"
The Array will only ever be 5 cells beside each other in a row
for example, M4:Q4, will be as follows - No No No Yes No
I want a formula to look at these 5 cells and return the number 4 as that is where the "Yes" value is
I have a spreadsheet where i would like to 'count' the number of cells with a particular colour AND SPECIFIC text (not 'any text').
I attach a xls with the initials of the person in column A, their colour as seen in cells in next four columns with the particular text in each cell. Therefore, for 'ABC1', in the given range, I would like to count how many cells have been allocated with the particular colour (brown, do not know colour index) and the particular text (1 or 2 or 4 or 5) in the range A1:CK39. Haven't supplied the actual sheet for confidentiality reasons.
I am trying to split names across columns. The problem is that some names
spilt into 3 columns (first, middle, last), and others split across 5 or 6
(extra names, etc.)
Is there a way to specify split, using SPACE as a delimiter, but only split
on the first TWO spaces, then leave the rest alone?
I have created an online survey, and people choose ten words (skills) out of 24 possible. Please see screenshot. I would like a formula that does this in layman's terms: "If I see the word "Cooperation" in the source cell, then I'll put "03b" into the target cell; but if I see "Managing" in the source cell instead, I'll put "21a" in the target cell, etc."
I've tried a few IF / Then statements, with no success. Screenshot shows the source cell upper right, and the ten target cells below left with two rows of sample results. I'm guessing the same formula can be in each of the 10 target cells.
I want to use a formula, in another cell, to convert "Friday, 30 May 2014, 3:47:16 PM" to a value. I am using Excel 2003.
View 2 Replies View RelatedFor this example, A1 is the given at 19.875 I am trying to get A2 to net a result of 19.5. A2 is to net that result by searching through cells B1:B14 and finding the nearest equal or lower number. If A1 is less than 13.5 a result of "error" should be generated in A2.
B1=13.5
B2=15.5
B3=17.5
B4=19.5
B6=21.5
[Code]...
Is there a way to format cells to where - when you type in a certain person's first name, it will insert something entirely different?
This has seemed to have happened on a computer at work.
In Excel, when I type in "Martha" then tab, or otherwise leave that cell, Martha changes to "Cool"
If I type in anyone elses name - it stays that name. But if I type in "Martha" .. it changes to "Cool" with the bullet and the word Cool.
I'm trying to write an IF formula that will return a number if the word in the adjacent cell begins with a specific letter. Here's what I want to show:
City
01
Express
02
Overnight
03
So "C" would return 01, "E" would return 02 and "O" would return 03.
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ...
the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies View RelatedHow to recreate the formulas used in my old database. One important factor is that:
In the old database, once you type in a word, it associates it to a number, and also adds that number in a further row. how do i do this?.
EXAMPLE:
- i have the word "sitter" that = 10.25$. If i enter "sitter" in column F, it enters 10.25$ in column M.
- Likewise, i can also have the word "private" that = 13.50$. If i enter "private" in column F, it enters 13.50$ in column M.
I currently, manually create my production work orders using excel (in-house ERP system not good enough).
Rather than manually type in the names of products to be produced, I would like to have the number 1 = a production item and 2 = another and so on.....So basically when I type 1 in a cell it will automatically display a production item.
I imagine this to be a process where i would type all the names of my production items in individual cells, then create a formula to make 1 = a specific cell, 2, 3, 4, etc.....
If square A1 says deposit account and c1 has a figure in it, i want g1 to repeat the figure,but if a2,a3 etc says something other than deposit account i want g2,g3 to remain blank.What is the formula for square g1etc?
View 9 Replies View RelatedI am looking to do the following:
Say I enter the following values into a spreadsheet:
5
7
4
24
32
Is there a way to enter the value "36" and have Excel find the two values of 32 and 4 which add up to 36 for me? I will obviously be dealing with much larger spreadsheets and would like to somehow save time by not having to hunt down possible combinations of the total value I'm searching for.
I'm looking at test results and would like to know how many times in a row correct answers were given before getting one incorrect. The way I have this currently set up, is if a student has the answer correct it returns a "TRUE" value, and a "FALSE" value for an incorrect answer. A typical test may have 50 questions, so I would have either "TRUE" or "FALSE" values in rows A1 through A50.
So I've been trying to figure out a formula that counts the number of times "TRUE" rows repeat 'consecutively' in a given column, in this scenario column A. The formula would need to check the current row and if "TRUE" check the row immediately after, if that row is also "TRUE" then check the row immediately after that, until it runs into a "FALSE" string, then display that total count of consecutive "TRUES" in another cell. I understand there may be duplicate results but I'm only really interested in the longest value, so duplicates can be ignored.
If it makes the formula easier, each possible result can be assigned to it's own cell. Let's assume the test has 50 questions. So if a student got all 50 questions correct the result count would be 50 in cell B50, or if they got 25 correct before the first incorrect answer, cell B25 would say 25, etc. I'm guessing I could then easily query this column for the largest value to come up with what I need at a glance.
I have two date columns, a start date and end date. Another columns tells me the number of days between these two columns. It may return 45 days which actually straddle 3 different months.
My aim is to identify between this date range how many days in Apr 14. Then in the next column howmany days in May 14 etc
So on the spreadsheet Id have 12 columns for each month of the year. in Apr 14 for each row between the given date range I want it to tell me how many days are actually in April. Id then copy that formula into the May 14 column.
I am using cell V7 to input the formula but I want cell V7 to tell me if the number in U7 is greater than or equal to the individual #'s in cells U7-U40 and I would like it to put a 1,2,3 in the cells of V7-V40 for the three highest numbers in order of largest to smallest if is this possible?
View 3 Replies View RelatedIn my Excel spreadsheet I enter todays date in a single cell (A2), then I list various dates that jobs come into shop in other cells (A8:A108). I have cells ( F8:F108) where I have been manually entering an asterik (*) for those jobs equal to or greater than five days old in cell (A2). Is there a formula that can do the math for me? I've tried Excel help but to no avail.
View 3 Replies View RelatedI want to bring in text files that result in a single column and then want to be able to align equal text in the column next to each other. I used to be able to do some moderate programming in FoxPro and used a transaction that was called set relationship to field+field, etc.
If there is no equal text in either of the other columns then I have tried to set it to a column by itself. Of course this comes data comes in all in one column with no spaces. I have searched the help files and the net and just can't find a way to do this. I'm thinking this might be attuned to Access more than Excel but I am not proficient in this. Right now I am doing a cut and paste to make this work but some of the imported text have upwards of 800 lines.