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 RepliesI 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?
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 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.
I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.
Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.
It is non-numeric text, and i've pasted --> values to remove formula issues.
I generated my urls to online photos, I referenced cells where some are only numerical (ex. 479) while others contain a numerical/text mix (ex. 3014-RACK). Here is my url code in excel...
View 10 Replies View RelatedI am trying to auto-populate text in cells in area A, based on data I enter into other cells in area B. I want the area A cells to be for display only, as all editing will be done in area B. The problem is: how do I do this such that the text I write does not get cut off if is longer than the column width? See the attached document for a clear example and description of what I am trying to do...
Excel Q.xlsx
I want to know how to display part of a cells text value, inside another cell.
Suppose in cell A1 i have "20-Jun-14"
How would I get cell B2 to display just "Jun"?
I try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
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.
I want to search for a word in column A and when I find it I want to copy it to column B.
Column A is a description that can be 6 or 7 words long. Column B is a single word.
Example:
Col A
Engine Kit, V-8, 306, forged.
I need to copy the word Kit to column B.
I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.
For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol
So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.
I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")
I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.
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" .
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.
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
I have a spreadsheet where i do need to count cells values that are formatted as text but i do need to ignore the real text in some cells, in this formula i do need to add values depending of the adjacent cell.
I have attached a example : New Microsoft Excel Worksheet.xlsx
I have some text I wish to add some content to the front and back of it. i.e. the text in the cell is "214.212.134.62" and I want to add "http://" to the front of it and ":9999" to the back to it, so it looks like "http://214.212.134.62:9999" in a different cell.
View 3 Replies View RelatedI have a column in which the cells contain values such as "012321 BGH YRK". Is there any way you know of that I can remove just the text from every cell and keep the numeric values? A mass operation because there are literally hundreds of these cells. I am using Excel 2001.
View 9 Replies View RelatedStructure:
I have a worksheet with the cells in one of the columns having html tags inside of each cell.
The tags vary by location within the cell and there are anywhere from 0-10 tags per cell.
Column A is the text string. Inside these cells are the tags. The tags are of form
<A href="/mortgagecenter/article_alias">
The
<A href="/mortgagecenter/
and
">
parts are true for every instance of this string....
....article_alias is a variable per each string. I have columns B and C which are the aricle_alias names and article_ID's, respectively.
What I am trying to do:
From the [quote]<A href="/mortgagecenter/article_alias">[quote]
I would need to replace the
article_alias
portion with
ArticleID&"-"&article_alias
I have a workbook with tons of programming I need to achieve. Here is my work book so it can be viewed.
Right now on "Protocol" Sheet I want the cells with the "Protocol 1" field to look at Admin_Panel Sheet and find the row with the same text. Then take the text in cell A of the same row and copy it to the cell below the "Protocol 1" listed on the protocol sheet.
I have a cell with a long list of phrases all separated by commas.
So it would appear, chicken, beef, steak, soup, fish
I want to create a rule in a cell to identify if one of those phrases comes up - so if chicken comes up then a 1 would appear.
To make it interesting is it possible to grab two words from the cell, so grab the words chicken and beef from a mass of words in one cell and bring it across.
I have these cells which contain huge amount of text and preferences and I am looking to identify these entities on if they contain these words.
Text configuration - how can I tell a cell to pick up a phrase from one cell, but if that phrase is equal to one particular phrase, then produce a blank.
View 3 Replies View Relatedhow to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies View Relatedi have a spreadsheet where I need to count up how many times a particular phrase within a text string appears. The text string will be duplicated many times throughout the spreadsheet.
For example :
Miss X was at work on Saturday
Mr XX was at work on Saturday but not Tuesday
Miss Y was at work on Tuesday
So I would like to count up how many times "work on Saturday" appears in my spreadsheet, and then as a seperate query, how many times "work on Tuesday" appears.
I've been searching the forums and web trying to figure out how to extract a text value from a 3D array result, to no avail or well beyond my understanding.
I've attached a file which shows an abbreviated version of the search (from the original 49 col x 400 row, which shows the name to date array which can return a sum.
What I'm looking to do is identify within this array result if there has been a text entry, and return the text as opposed to a sum of the remaining values. This is essentially a double check to make certain that there aren't hours scheduled when other events such as vac/loa/etc are also booked.
Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
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")
I have four cells that contain text. All have connected check boxes with TRUE FALSE.
I need to be able to select anyone one of these cells with a check box, and have it's text appear in one separate cell eg: A1.
I have no issue connecting check boxes etc. I have no issue reproducing the text from any of these cells into multiple cells with a check box. But they have to be selectable and reproducing in one cell only (eg"A1").
How would I pull only a specific word from a text cell? If you see my example, in one column I have various job titles. If A2 contains "Manager", then in B2 return "Manager. If A1 contains "Coordinator", return "Coordinator. Etc.
Excel Forum.jpg
How to make a text background on editable cells and print without text background?
View 4 Replies View Related